🔐 Field-Level Encryption: The Column That Keeps a Secret From Your Own DBA

Quick question: if someone gets a pg_dump of your production database — not a breach headline, just a .sql.gz sitting in the wrong S3 bucket for eleven minutes — how many of your users' secrets can they read? If your answer is "well, it's encrypted at rest," I need you to sit down, because that's not the flex you think it is.

Encryption at rest (TDE, disk-level encryption, whatever your cloud provider calls its checkbox) protects you from exactly one scenario: someone stealing the physical disk or the raw storage volume. The moment that data is queryable — which is the entire point of a database — at-rest encryption is transparent. Your database engine decrypts it for you, your ORM never even knows encryption happened, and anyone with valid credentials (a leaked API key, a compromised read replica, an over-permissioned analytics tool, a well-meaning DBA running an ad-hoc query) sees plaintext. That backup sitting in S3? Also plaintext the second someone restores it. Disk encryption is a great answer to "what if someone steals the hardware." It's a non-answer to "what if someone reads the data."

Field-level encryption is the annoying but honest alternative: specific columns — SSNs, card numbers, health data, API secrets — are encrypted by the application, before they ever reach the database, and stay ciphertext no matter who's holding the connection string.

What it actually buys you

The threat model field-level encryption addresses is narrower and more realistic than "hacker breaches the perimeter." It's:

A backup gets exfiltrated or misconfigured (this happens constantly — it's the single most common way "encrypted" data leaks).
A read replica or reporting database has looser access controls than production.
An insider — malicious or just careless — runs SELECT * FROM users for a task that didn't need to touch SSNs.
SQL injection somewhere in a legacy endpoint returns rows instead of nothing.

In every one of those, disk encryption does nothing, because the attacker has a live, authenticated path to the data. Field-level encryption means what they get back is ciphertext they can't do anything with unless they also have the key — and the key, critically, does not live next to the data.

The part that makes it painful: you lose the database

Here's the trade nobody puts on the marketing slide: once a column is ciphertext, your database can no longer help you with it. No WHERE email = ? index lookups, no LIKE '%gmail.com' searches, no ORDER BY, no uniqueness constraints enforced by the DB. AES in a proper mode (GCM, with a random nonce per row) produces different ciphertext for the same plaintext every time — which is exactly what you want for security, and exactly what breaks equality lookups.

This is where I've seen teams — including one project at Cubet where we encrypted a customer identifier column and only then discovered three cron jobs joined on it — quietly reinvent the same three workarounds:

Deterministic encryption for lookup columns. Same plaintext always produces the same ciphertext (no random nonce, or an HMAC of the plaintext used as a blind index instead). You get equality search back, at the cost of leaking frequency — an attacker who sees the ciphertext distribution can tell which rows share a value, even without the key.
A separate blind index. Store HMAC(secret_key, lowercase(email)) in its own indexed column alongside the randomly-encrypted real value. Queries hit the HMAC column; nothing about the plaintext leaks beyond "these two rows match."
Give up on DB-side search entirely for that field and accept application-side filtering, or move the searchable-but-sensitive use case to a separate index (Elasticsearch with its own access controls, etc.).

None of these are free. All of them are cheaper than finding out your "encrypted" column was decryptable by anyone with read access the whole time.

A minimal shape, in code

The important part isn't the AES call — every language's crypto library does that fine. It's making sure the nonce is random per write, stored alongside the ciphertext (it's not secret, it just can't repeat), and that the key comes from a KMS/secrets manager rather than an environment variable sitting in the same repo as the schema.

const crypto = require('crypto');

function encryptField(plaintext, keyBuffer) {
  const nonce = crypto.randomBytes(12); // GCM nonce: unique per encryption, never reused with the same key
  const cipher = crypto.createCipheriv('aes-256-gcm', keyBuffer, nonce);
  const ciphertext = Buffer.concat([cipher.update(plaintext, 'utf8'), cipher.final()]);
  const authTag = cipher.getAuthTag();

  // store nonce + authTag alongside ciphertext — none of these three are secret
  return Buffer.concat([nonce, authTag, ciphertext]).toString('base64');
}

function decryptField(stored, keyBuffer) {
  const raw = Buffer.from(stored, 'base64');
  const nonce = raw.subarray(0, 12);
  const authTag = raw.subarray(12, 28);
  const ciphertext = raw.subarray(28);

  const decipher = crypto.createDecipheriv('aes-256-gcm', keyBuffer, nonce);
  decipher.setAuthTag(authTag); // wrong key or tampered ciphertext throws here, not silently
  return Buffer.concat([decipher.update(ciphertext), decipher.final()]).toString('utf8');
}

The authTag check is the part people skip when they roll their own on top of a non-authenticated cipher mode, and it's the difference between "tampering fails loudly" and "tampering fails invisibly." GCM gives you authentication for free — there's rarely a reason to reach for plain CBC anymore.

For the blind index, an HMAC of a normalized plaintext, stored in its own column and never treated as the primary source of truth:

function blindIndex(plaintext, hmacKey) {
  return crypto
    .createHmac('sha256', hmacKey)
    .update(plaintext.trim().toLowerCase())
    .digest('base64');
}

Two keys, two purposes: the AES key protects confidentiality, the HMAC key protects the index. Rotate them independently, and if the HMAC key ever leaks, an attacker gains "can tell which rows match" — not "can read the data."

Where key management stops being optional

Field-level encryption is only as strong as the wall between the key and the data. If your application server holds both the database connection string and the decryption key, and an attacker compromises the app server, you've built an elaborate ritual that protects against exactly nothing — they get plaintext the same way your app does. The point of a KMS (AWS KMS, GCP KMS, Vault) isn't ceremony, it's that decryption becomes an auditable, revocable API call rather than a local operation. You can see every decrypt in CloudTrail. You can revoke a key without touching the database. That visibility is most of the actual security value — the AES-256 part was never the hard bit.

When to skip it

Not everything needs this. If a field isn't independently sensitive outside its normal access path — a shipping address, a display name — field-level encryption mostly adds query pain for a threat model that doesn't apply. Save it for the columns that are genuinely radioactive if they leak on their own: government IDs, payment data, health records, auth secrets, anything a regulator or a breach-notification law has opinions about. Encrypting everything "to be safe" just means every future engineer has to think about key management before writing a WHERE clause, and eventually someone will decide that's too annoying and quietly stop.

If you've fought with searchable encryption, blind indexes, or a KMS integration that turned into a bigger project than the feature it protected, I'd genuinely like to hear the war story. Find me on GitHub or LinkedIn — and if this saved you from an "encrypted at rest, therefore secure" meeting, share it with whoever's about to sit through one.