中文
devexamples
← Recipes

阅读中文版本

Encrypt Sensitive Fields at the Application Layer

Encrypt sensitive columns before writing and decrypt after reading, key by tenant where needed, and keep indexes on deterministic derived values instead of plaintext.

~50 minsecurityencryptiondatabases

Task

Protect specific sensitive fields with application-layer encryption while still supporting lookups.

When to use this

Whole-database encryption does not protect against an application bug or a read replica with weaker access, so sensitive fields remain exposed.

Prerequisites

  • A key management or secret source
  • A column type able to store ciphertext and nonce

Steps

1. Encrypt the field with an authenticated cipher and a random nonce before insert.

Expected output: Stored values are ciphertext, tamper-evident and unique per row.

2. Decrypt after read and never store the plaintext or key in logs.

Expected output: Sensitive data is confined to memory and to ciphertext at rest.

3. For searchable fields, store a keyed hash or blind index column alongside.

Expected output: Equality lookups work without decrypting everything.

4. Include the field name and, where relevant, the tenant in the authenticated data.

psql -c "SELECT length(ciphertext), length(nonce) FROM customers WHERE id=$1;"

Expected output: Ciphertext cannot be moved between fields or tenants.

Troubleshooting

Problem

Cannot range-query encrypted fields

Solution

Application-layer encryption supports equality via blind indexes but not range queries; redesign the query or store a coarse non-sensitive range.

Problem

Key rotation breaks old rows

Solution

Version the key with each ciphertext and decrypt with the stored version, re-encrypting lazily.

Limitations

  • Encrypting fields blocks database-side search, sort, and constraints on those fields unless blind indexes are added.
  • It does not protect data in memory or in logs if the application mishandles the plaintext.

Context

Protect specific sensitive fields with application-layer encryption while still supporting lookups. Whole-database encryption does not protect against an application bug or a read replica with weaker access, so sensitive fields remain exposed.

Steps

  1. Encrypt the field with an authenticated cipher and a random nonce before insert. Expected: Stored values are ciphertext, tamper-evident and unique per row.
  2. Decrypt after read and never store the plaintext or key in logs. Expected: Sensitive data is confined to memory and to ciphertext at rest.
  3. For searchable fields, store a keyed hash or blind index column alongside. Expected: Equality lookups work without decrypting everything.
  4. Include the field name and, where relevant, the tenant in the authenticated data. Expected: Ciphertext cannot be moved between fields or tenants.
const nonce = randomBytes(12);
const ct = encrypt(key, plaintext, nonce, aad);
await db.insert({ ciphertext: ct, nonce, keyVersion: 1 });
// blind index for equality lookups
const blind = hmac(indexKey, plaintext.toLowerCase());

Verify

  • Stored values are ciphertext, tamper-evident and unique per row.
  • Sensitive data is confined to memory and to ciphertext at rest.
  • Equality lookups work without decrypting everything.
  • Ciphertext cannot be moved between fields or tenants.

Limits

  • Encrypting fields blocks database-side search, sort, and constraints on those fields unless blind indexes are added.
  • It does not protect data in memory or in logs if the application mishandles the plaintext.