Database Proxy: Search and Query Impact

CloudTaser's database proxy encrypts PII columns transparently. This page explains which queries are affected and why, in practice, search functionality remains intact for properly designed applications.

---

What the Proxy Does

The proxy sits between your application and the managed database. It encrypts configured columns on INSERT/UPDATE and decrypts them on SELECT:

Application → INSERT (name="Alice", ssn="123-45-6789", email="[email protected]")
    ↓
CloudTaser DB Proxy → encrypts ssn and email with AES-256-GCM + EU vault key
    ↓
Cloud SQL / RDS → stores (name="Alice", ssn=\x01...[131 bytes ciphertext], email=\x01...[137 bytes ciphertext])

On read:

Application → SELECT name, ssn, email FROM users WHERE id = 1
    ↓
CloudTaser DB Proxy → fetches from database, decrypts ssn and email
    ↓
Application receives → (name="Alice", ssn="123-45-6789", email="[email protected]")

The application sees plaintext. The database stores ciphertext. The cloud provider, its DBAs, backups, snapshots, and replicas all contain only ciphertext for encrypted columns.

---

What Works, What Doesn't

Fully Working (no changes needed)

Query Pattern	Example	Why It Works
Lookup by primary key	WHERE id = $1	ID is not encrypted
Lookup by foreign key	WHERE user_id = $1	Foreign keys are not encrypted
Lookup by business identifier	WHERE account_number = $1	Business IDs are not encrypted
Filter by date	WHERE created_at > $1	Timestamps are not encrypted
Filter by status	WHERE status = 'active'	Status fields are not encrypted
ORDER BY non-PII column	ORDER BY created_at DESC	Non-encrypted columns sort normally
JOIN on non-PII columns	JOIN orders ON users.id = orders.user_id	Join keys are not encrypted
Aggregate non-PII columns	COUNT(*) WHERE status = 'active'	Aggregation on non-encrypted columns works
SELECT encrypted columns	SELECT ssn, email FROM users WHERE id = 1	Proxy decrypts on read
INSERT encrypted columns	INSERT INTO users (ssn) VALUES ($1)	Proxy encrypts on write
UPDATE encrypted columns	UPDATE users SET email = $1 WHERE id = $2	Proxy encrypts the value, WHERE uses ID

Not Working (encrypted columns in WHERE/JOIN/ORDER BY)

Query Pattern	Example	Why It Fails	Solution
Equality search on encrypted column	WHERE ssn = $1	Ciphertext is randomized — same plaintext produces different ciphertext	Blind index (V2)
Pattern match on encrypted column	WHERE email LIKE '%@example.com'	Cannot pattern-match ciphertext	Not supported; search by ID instead
ORDER BY encrypted column	ORDER BY ssn	Ciphertext ordering has no relation to plaintext ordering	Sort in application after decryption
JOIN on encrypted column	JOIN t2 ON t1.ssn = t2.ssn	Ciphertext won't match across tables	Deterministic encryption (V2) or join by ID
GROUP BY encrypted column	GROUP BY email	Same value produces different ciphertext	Blind index (V2)

---

Why Search Is Not Affected in Practice

In properly designed regulated systems (banking, government, healthcare), PII columns are never used as search keys. Applications look up records by identifiers and retrieve PII for display:

Banking

-- How a bank actually queries customer data:
SELECT name, ssn, email FROM customers WHERE customer_id = $1;
SELECT account_number, balance FROM accounts WHERE customer_id = $1;
SELECT * FROM transactions WHERE account_id = $1 AND date > $2 ORDER BY date DESC;

-- NOT this:
SELECT * FROM customers WHERE ssn = '123-45-6789';  -- Never happens in production

The SSN, name, and email are stored because KYC/AML regulations require it. They are retrieved by customer_id and displayed when a banker opens a customer profile. They are never used as lookup keys.

Government Identity Services (DigiD)

-- How DigiD-style systems query:
SELECT bsn, name, email FROM citizens WHERE internal_user_id = $1;
SELECT * FROM sessions WHERE session_token = $1;
SELECT * FROM audit_log WHERE user_id = $1 AND timestamp > $2;

-- NOT this:
SELECT * FROM citizens WHERE bsn = '123456789';  -- Internal systems use user_id

The BSN (Burger Service Nummer) is PII. Internal systems use an opaque user_id for all lookups. BSN is stored encrypted and only retrieved for display or government API calls.

Healthcare

-- How healthcare systems query:
SELECT patient_name, bsn, diagnosis FROM patients WHERE patient_id = $1;
SELECT * FROM appointments WHERE patient_id = $1 AND date = $2;

-- NOT this:
SELECT * FROM patients WHERE patient_name = 'Jan de Vries';  -- Use patient_id

---

The One Exception: Login by Email

The most common case where an application searches by a PII column is login by email:

SELECT id, password_hash FROM users WHERE email = $1;

This is solved by blind indexes (planned for V2). A blind index stores an HMAC-SHA256 of the email in a separate column:

-- Schema with blind index:
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email BYTEA,          -- encrypted by proxy
    email_idx BYTEA,      -- HMAC-SHA256 blind index (not reversible)
    password_hash TEXT
);

-- Login query uses the blind index:
SELECT id, password_hash FROM users WHERE email_idx = $1;
-- Proxy computes HMAC-SHA256 of the input email and matches against the index

The blind index enables exact-match lookup without exposing the email to the database. The database cannot reverse the HMAC to recover the email.

Until blind indexes ship

If your application needs login-by-email before V2, keep the email column unencrypted and encrypt other PII columns (SSN, name, address, phone, card number). This still removes the majority of PII from the cloud provider's reach.

---

Integration Test Evidence

The following output is from the TestEncryptedColumnRoundTrip integration test running against real PostgreSQL 18 + OpenBao:

╔══════════════════════════════════════════════════════════════╗
║  INSERT via CloudTaser proxy                               ║
╠══════════════════════════════════════════════════════════════╣
║  Parameters sent by application:                           ║
║    $1 (name):  "Alice"                                     ║
║    $2 (ssn):   "123-45-6789"                               ║
║    $3 (email): "[email protected]"                         ║
╚══════════════════════════════════════════════════════════════╝
  -> INSERT OK (proxy encrypted ssn and email before forwarding to DB)

╔══════════════════════════════════════════════════════════════╗
║  SELECT via CloudTaser proxy (application sees plaintext)  ║
╚══════════════════════════════════════════════════════════════╝
  name:  "Alice"
  ssn:   "123-45-6789"
  email: "[email protected]"
  -> Application receives plaintext (proxy decrypted transparently)

╔══════════════════════════════════════════════════════════════╗
║  SELECT directly from database (what the cloud provider    ║
║  sees — bypassing proxy, as if DBA or CLOUD Act warrant)   ║
╚══════════════════════════════════════════════════════════════╝
  name:  "Alice"  (plaintext — not in encrypt list)
  ssn:   \x012384c25a...  (131 bytes of ciphertext)
  email: \x0156af5c9b...  (137 bytes of ciphertext)
  -> Cloud provider sees ciphertext. Decryption requires EU-hosted vault key.

╔══════════════════════════════════════════════════════════════╗
║  Comparison: Application vs Cloud Provider                 ║
╠══════════════════════════════════════════════════════════════╣
║  name  | App: Alice           | DB: Alice                  ║
║  ssn   | App: 123-45-6789     | DB: (131 bytes ciphertext) ║
║  email | App: [email protected] | DB: (137 bytes ciphertext) ║
╠══════════════════════════════════════════════════════════════╣
║  Non-PII columns: stored as plaintext (searchable)         ║
║  PII columns: stored as ciphertext (EU vault key needed)   ║
╚══════════════════════════════════════════════════════════════╝

╔══════════════════════════════════════════════════════════════╗
║  Search by ID (non-PII) — works normally                   ║
╚══════════════════════════════════════════════════════════════╝
  WHERE id = 1 -> name="Alice", ssn="123-45-6789"
  -> Lookup by primary key works perfectly. PII decrypted on read.

╔══════════════════════════════════════════════════════════════╗
║  Search by name (non-PII, non-encrypted) — works normally  ║
╚══════════════════════════════════════════════════════════════╝
  WHERE name = 'Alice' -> ssn="123-45-6789"
  -> Search by non-encrypted column returns decrypted PII.

---

Summary

If your application...	Impact	Action needed
Looks up records by ID/FK	None	None
Filters by date, status, type	None	None
Retrieves PII for display	None	Proxy decrypts transparently
Writes PII on registration/update	None	Proxy encrypts transparently
Searches by email for login	Broken until V2	Use blind index (V2), or keep email unencrypted
Searches by SSN/name	Broken	Redesign to search by ID (best practice regardless of encryption)

The proxy does not break applications that follow standard database design patterns. PII columns are storage columns, not search columns.

:octicons-arrow-right-24: Database Proxy Configuration | :octicons-arrow-right-24: Blind Indexes (V2)