Postgres field-level encryption: key management and query trade-offs

TL;DR — Field-level encryption for Postgres is usually an application-layer design: the app encrypts selected values before INSERT/UPDATE, stores ciphertext in normal columns, and gets data keys from KMS/HSM-backed key management. The key decision is not "can we encrypt it?" but "what queries must still work?" — if you need equality search, sort, prefix match, or analytics on the plaintext, each one adds cost, leakage, or both. Reading time: ~7 min

What it is and where it sits

Field-level encryption means encrypting specific column values such as SSNs, bank account numbers, API secrets, medical notes, or recovery answers, instead of encrypting the whole database volume and calling it done. In Postgres terms, you store ciphertext in the row for only the sensitive fields, while non-sensitive fields stay queryable in the clear.

This sits above disk encryption and usually above Postgres itself:

• Disk/volume encryption protects stolen disks and snapshots.
• Postgres transport security (ssl=on) protects data in transit.
• Field-level encryption protects against broader classes of exposure: leaked logical backups, overly broad read access, accidental data exports, some insider access, and downstream replicas or analytics copies that should not see plaintext.

In most production systems, the application performs the crypto, not the database. Postgres can do crypto with pgcrypto, but then the database process sees plaintext and often the keys too. That helps with convenience, not separation of duties.

What it replaces:

• It does not replace full-disk encryption or TLS.
• It often replaces "store it in plaintext and trust RBAC".
• It sometimes replaces ad hoc app secrets handling where developers manually base64-encode values and call it encryption.

Typical request/data flow:

[Client] ---- 1. POST /customers ----> [API App]
[API App] ---- 2. request data key ----> [KMS/HSM]
[KMS/HSM] ---- 3. wrapped key or decrypt ----> [API App]
[API App] ---- 4. encrypt sensitive fields ----> [Postgres]
[Postgres] ---- 5. store ciphertext + metadata ----> [Replica/Backup]

What talks to it:

• Your API or worker encrypts before writing and decrypts after reading.
• A KMS/HSM or secret-management service protects the master key or key-encryption key (KEK).
• Postgres stores ciphertext, key IDs, and optional search tokens.
• Background jobs handle backfill and key rotation.

A common schema shape is:

• email_ciphertext bytea
• email_key_id text
• email_nonce bytea
• email_search_token bytea for exact-match lookup only

If you are thinking "why not just use pgcrypto.pgp_sym_encrypt() in SQL?", the answer is usually key exposure and operational coupling. The app would still need to provide the passphrase or key to the DB session, and anyone with enough DB access can often get plaintext by running the same function.

How it actually works

The standard pattern is envelope encryption.

• A master key lives in KMS/HSM and is never exported in plaintext.
• The app gets either a fresh data encryption key (DEK) or a wrapped DEK per record/tenant/table.
• The app encrypts the field with an AEAD mode such as AES-256-GCM or XChaCha20-Poly1305.
• The app stores ciphertext plus enough metadata to decrypt later: algorithm/version, nonce, wrapped DEK or DEK reference, and key ID.

End-to-end example: encrypting a customer tax ID

Assume a customers table where tax_id is sensitive, and support staff need exact-match lookup by tax ID but should not browse all tax IDs.

Schema idea:

• tax_id_ciphertext bytea not null
• tax_id_nonce bytea not null
• tax_id_key_id text not null
• tax_id_search_token bytea not null
• tax_id_alg text not null default 'aes-256-gcm:v1'

Step by step:

1. Client sends tax_id=123-45-6789 to the API.
2. API canonicalizes it before doing anything else. For searchability, this is mandatory. Example: strip dashes and spaces, reject invalid length, store canonical form 123456789.
3. API asks KMS to decrypt or generate the DEK. In many designs, the DEK is cached in memory for a short TTL like 5-15 minutes to avoid a KMS round-trip per row.
4. API generates a random 96-bit nonce for AES-GCM.
5. API encrypts the canonical tax ID. It should include authenticated associated data (AAD) such as table=customers,column=tax_id,tenant_id=42 so ciphertext copied into another context will fail authentication.
6. API computes a deterministic search token separately, usually HMAC-SHA-256(search_key, canonical_tax_id). This is not reversible encryption; it is an equality index aid.
7. API inserts ciphertext, nonce, key ID, algorithm version, and search token into Postgres.
8. For lookup, API canonicalizes the user-supplied tax ID the same way, computes the same HMAC token, queries by tax_id_search_token, fetches the matching row, decrypts tax_id_ciphertext, and returns plaintext only to authorized code paths.

Why separate encryption and search token? Because secure randomized encryption should produce different ciphertext every time for the same plaintext. That is good for confidentiality but bad for equality search. If you need exact-match search, you add a deterministic side channel on purpose and accept the leakage: an attacker who gets the token column can tell when two rows have the same normalized value.

What the row actually contains

A realistic stored row might look like this conceptually:

• tax_id_ciphertext: binary blob including auth tag or ciphertext+tag
• tax_id_nonce: 12 bytes for GCM
• tax_id_key_id: arn:...key/1234 or kms-key-2026-01
• tax_id_alg: aes-256-gcm:v1
• tax_id_search_token: 32 bytes HMAC output

Failure modes you will actually see

If the wrong key, nonce, or AAD is used during decrypt, your crypto library will fail authentication. Typical error shapes:

cipher: message authentication failed

AES-GCM decrypt failed: invalid tag

secretbox.Open: authentication failed

Those are not "maybe the string encoding is off" errors. Treat them as data/key mismatch, corrupted ciphertext, wrong AAD, or a bug in versioned serialization.

When to use it (and when not to)

Use field-level encryption when the sensitivity is column-specific and you want to reduce blast radius beyond "whoever can read the DB can read everything."

You probably don't need this if the real problem is overprivileged application access. Encrypting columns does not help when the same app tier that decrypts them is compromised.

Trade-offs

Every benefit here has a bill attached.

• Reduced exposure in backups, replicas, exports, and ad hoc SQL reads.
  • Cost: application complexity, schema metadata, migration work, and more failure modes.
• Separation of duties via KMS/HSM-managed keys.
  • Cost: latency to KMS, service quotas, IAM policy complexity, and incident dependency on another control plane.
• Per-field protection instead of all-or-nothing DB trust.
  • Cost: developers must explicitly choose what gets encrypted and how it is normalized.
• Exact-match search via deterministic token.
  • Cost: equality leakage. Attackers can see duplicates; low-entropy domains are vulnerable to guessing/dictionary attacks.
• Strong AEAD encryption with random nonces.
  • Cost: no native sorting, range queries, prefix search, or meaningful indexing on plaintext.
• Key rotation support.
  • Cost: re-encryption jobs, dual-read logic during migration, and operational risk.

Latency and money are usually not from Postgres; they come from key management. If you call KMS for every row on hot paths, you will feel it. Typical mitigation is DEK caching in process memory with short TTL and strict metrics around cache hit rate.

Lock-in risk is real but manageable. If your envelope format is your own versioned blob plus standard AEAD, moving between KMS providers is mostly a key-wrapping migration. If you tightly couple to vendor-specific DB-side encryption functions, migration gets uglier.

In practice

Example 1: Postgres schema for ciphertext plus exact-match token

⚠️ Adding NOT NULL encrypted columns to a large existing table can lock or rewrite data depending on your migration plan. On a busy table, add nullable columns first, backfill in batches, then add constraints and indexes in a later migration.

ALTER TABLE customers
  ADD COLUMN tax_id_ciphertext bytea,
  ADD COLUMN tax_id_nonce bytea,
  ADD COLUMN tax_id_key_id text,
  ADD COLUMN tax_id_alg text,
  ADD COLUMN tax_id_search_token bytea;

CREATE INDEX CONCURRENTLY idx_customers_tax_id_search_token
  ON customers (tax_id_search_token);

This creates the storage shape most teams actually need: ciphertext plus metadata and a separate exact-match token. The gotcha is CREATE INDEX CONCURRENTLY: run it outside a transaction block, or Postgres will fail with output shaped like:

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Example 2: Application-layer encryption and lookup in Node.js

import crypto from "node:crypto";
import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

function canonicalizeTaxId(input) {
  const v = input.replace(/[^0-9]/g, "");
  if (v.length !== 9) throw new Error("invalid tax_id");
  return v;
}

function encryptField(plaintext, dek, aad) {
  const nonce = crypto.randomBytes(12);
  const cipher = crypto.createCipheriv("aes-256-gcm", dek, nonce);
  cipher.setAAD(Buffer.from(aad, "utf8"));
  const ciphertext = Buffer.concat([cipher.update(plaintext, "utf8"), cipher.final()]);
  const tag = cipher.getAuthTag();
  return { nonce, ciphertext: Buffer.concat([ciphertext, tag]) };
}

function decryptField(blob, nonce, dek, aad) {
  const tag = blob.subarray(blob.length - 16);
  const ciphertext = blob.subarray(0, blob.length - 16);
  const decipher = crypto.createDecipheriv("aes-256-gcm", dek, nonce);
  decipher.setAAD(Buffer.from(aad, "utf8"));
  decipher.setAuthTag(tag);
  return Buffer.concat([decipher.update(ciphertext), decipher.final()]).toString("utf8");
}

function searchToken(value, searchKey) {
  return crypto.createHmac("sha256", searchKey).update(value, "utf8").digest();
}

async function insertCustomer({ id, tenantId, taxId, dek, keyId, searchKey }) {
  const canonical = canonicalizeTaxId(taxId);
  const aad = `table=customers,column=tax_id,tenant=${tenantId}`;
  const { nonce, ciphertext } = encryptField(canonical, dek, aad);
  const token = searchToken(canonical, searchKey);

  await pool.query(
    `INSERT INTO customers
      (id, tax_id_ciphertext, tax_id_nonce, tax_id_key_id, tax_id_alg, tax_id_search_token)
     VALUES ($1, $2, $3, $4, 'aes-256-gcm:v1', $5)`,
    [id, ciphertext, nonce, keyId, token]
  );
}

async function findByTaxId({ tenantId, taxId, dek, searchKey }) {
  const canonical = canonicalizeTaxId(taxId);
  const token = searchToken(canonical, searchKey);
  const { rows } = await pool.query(
    `SELECT id, tax_id_ciphertext, tax_id_nonce
       FROM customers
      WHERE tax_id_search_token = $1`,
    [token]
  );
  return rows.map(r => ({
    id: r.id,
    taxId: decryptField(r.tax_id_ciphertext, r.tax_id_nonce, dek, `table=customers,column=tax_id,tenant=${tenantId}`)
  }));
}

This is the core pattern: randomized AEAD for confidentiality plus HMAC token for exact-match search. The gotcha is canonicalization and AAD stability: if you change either later without versioning, old rows will decrypt-fail with Unsupported state or unable to authenticate data-style errors.

Example 3: Batch backfill and rotation query shape

SELECT id
FROM customers
WHERE tax_id_ciphertext IS NULL
ORDER BY id
LIMIT 500
FOR UPDATE SKIP LOCKED;

Use this from a worker to backfill plaintext-to-ciphertext migrations or re-encrypt rows during rotation without workers fighting each other. The gotcha is transaction size: commit every batch, or you will hold locks too long and create replication lag.

For rotation, the safe pattern is:

1. Add support for decrypting old and new key versions.
2. Start writing only with the new key ID.
3. Re-encrypt old rows in batches.
4. Remove old-key decrypt support only after metrics show zero old rows.

Further reading

• PostgreSQL Documentation: pgcrypto
• PostgreSQL Documentation: CREATE INDEX CONCURRENTLY
• NIST SP 800-38D: Galois/Counter Mode (GCM)
• OWASP Cryptographic Storage Cheat Sheet
• Google Tink documentation on envelope encryption

This article was written by an AI system and published pending human review. Verify anything you intend to act on.