Column-Level Data Encryption: Concepts, Key Management, and Practice

1. Why encrypt at the column level, not just at the database level

Data encryption at the database level, often called transparent data encryption, encrypts the entire data file on disk. This measure reliably protects against physical theft of a hard drive or a backup, which remains unreadable without the matching key. It does not, however, protect against the far more common case: a compromised database account reading data normally via SQL query, because the database engine transparently decrypts data for an active connection.

Data encryption at the column level addresses exactly this: individual, especially sensitive columns are additionally stored encrypted, so that even an account with full SELECT access to the table sees only ciphertext unless it also has the matching decryption key. This additional protection layer is particularly relevant for fields such as social security numbers, credit card data, or health data, where regulatory requirements explicitly demand field-level encryption "at rest."

The decisive difference: data encryption at the column level separates access to the data from access to the key. A database administrator with full access to all tables still sees only ciphertext with correctly implemented column encryption, as long as the key is managed outside the database. This separation is a central element of defense-in-depth that pure volume encryption structurally cannot offer.

2. pgcrypto in PostgreSQL: symmetric encryption in practice

PostgreSQL offers native functions for column-level data encryption via the pgcrypto extension. The functions pgp_sym_encrypt() and pgp_sym_decrypt() implement symmetric encryption following the OpenPGP standard directly in SQL, without application code having to embed cryptographic libraries itself. The passphrase parameter acts as the key, but should never appear as a literal in the SQL statement, and must instead come from a secure external source.

An important practical point about pgcrypto: encrypted columns must be declared as bytea instead of text or varchar, because the result is binary ciphertext. Application code reading these columns must explicitly request decryption, which in practice means every query accessing encrypted fields must be adapted, instead of working transparently as with unencrypted columns.

-- Enable the pgcrypto extension once per database
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Column-level data encryption: store as bytea, never as plain text
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  ssn_encrypted BYTEA NOT NULL  -- encrypted column
);

-- Insert with encryption, passphrase from a secure external source
-- (never hardcode the passphrase in application code or SQL)
INSERT INTO customers (first_name, ssn_encrypted)
VALUES (
  'Jane Doe',
  pgp_sym_encrypt('123-45-6789', current_setting('app.encryption_key'))
);

-- Read with explicit decryption, requires the same key
SELECT
  first_name,
  pgp_sym_decrypt(ssn_encrypted, current_setting('app.encryption_key')) AS ssn
FROM customers
WHERE id = 1;

3. Deterministic vs. randomized encryption

A central design decision in column-level data encryption concerns the choice between deterministic and randomized encryption. Randomized encryption produces a different ciphertext each time the same plaintext value is encrypted, because a random initialization vector is mixed in. This is the cryptographically safer choice, because an attacker cannot tell, even with access to many ciphertexts, which rows contain the same plaintext.

The downside of randomized data encryption: you cannot search for a value directly with WHERE ssn_encrypted = ..., because the same plaintext yields a different ciphertext on every encryption. Deterministic encryption solves this problem by always producing the same ciphertext for the same plaintext, which allows direct equality search, but introduces a security risk: an attacker can recognize patterns, such as two customers sharing the same credit card number, without knowing the plaintext itself.

-- Randomized encryption: different ciphertext each time, more secure
-- pgp_sym_encrypt includes a random session key by default
SELECT pgp_sym_encrypt('4111111111111111', 'key') != pgp_sym_encrypt('4111111111111111', 'key');
-- Returns TRUE: same plaintext, different ciphertext each call

-- Deterministic alternative: HMAC-based blind index for equality search
-- Store both the randomized ciphertext AND a deterministic search token
CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  card_number_encrypted BYTEA NOT NULL,   -- randomized, for storage
  card_number_search_hash TEXT NOT NULL   -- deterministic HMAC, for lookup
);

INSERT INTO payments (card_number_encrypted, card_number_search_hash)
VALUES (
  pgp_sym_encrypt('4111111111111111', current_setting('app.encryption_key')),
  encode(hmac('4111111111111111', current_setting('app.hmac_key'), 'sha256'), 'hex')
);

-- Search uses the deterministic hash, never the randomized ciphertext
SELECT * FROM payments
WHERE card_number_search_hash = encode(hmac('4111111111111111', current_setting('app.hmac_key'), 'sha256'), 'hex');

4. Key management: where keys must never live

The most effective column-level data encryption becomes worthless if the key sits right next to the encrypted data. A common, dangerous mistake: the encryption key gets stored in a configuration table of the same database, or worse, hardcoded directly in application code. In both cases, anyone gaining access to the database or the repository automatically also gets the key, and the whole encryption becomes a pure formality.

Professional data encryption uses a dedicated key management system such as AWS KMS, HashiCorp Vault, or Azure Key Vault, which manages keys outside the database, logs access granularly, and never returns raw keys to application code, offering only encryption and decryption operations as a service. The application calls the KMS at runtime, receives the decrypted value directly back, and ideally the raw key never leaves the KMS.

A two-tier key model with a data encryption key per column or table and an overarching key encryption key in the KMS further reduces risk: if a data encryption key is compromised, only the affected column is at risk, while the key encryption key remains protected in the KMS and can be used to rotate all data encryption keys.

5. Application-side vs. database-native encryption

Data encryption can happen either inside the database itself via a function like pgp_sym_encrypt, or entirely in the application layer before data is even sent to the database. Application-side encryption offers the strongest separation: the database never sees plaintext at any point, and even a fully compromised database server delivers an attacker only ciphertext, because encryption and decryption happen exclusively in the application.

The downside of application-side data encryption: database functions such as aggregation, sorting, or full-text search on the encrypted field become impossible, because the database never sees the plaintext. Database-native encryption with pgcrypto at least allows decryption within a query for downstream processing, but shifts trust to the database engine itself, which briefly holds plaintext in memory.

-- Application-side encryption: database only ever sees ciphertext
-- Pseudocode showing the pattern, actual crypto happens in app code
function storeCustomer(customer) {
  const encryptedSsn = encryptAES256(customer.ssn, getKeyFromKms());
  db.query(
    "INSERT INTO customers (first_name, ssn_encrypted) VALUES ($1, $2)",
    [customer.firstName, encryptedSsn]
  );
  // Database process memory, logs and backups never contain plaintext
}

function readCustomerSsn(id) {
  const row = db.query("SELECT ssn_encrypted FROM customers WHERE id = $1", [id]);
  return decryptAES256(row.ssn_encrypted, getKeyFromKms());
  // Decryption happens exclusively in application memory
}

6. Encryption and indexing: the tradeoff

A B-tree index on a column with randomized data encryption is practically useless, because the index only sorts ciphertext, which has no relationship to plaintext ordering. Range queries such as WHERE amount_encrypted BETWEEN ... AND ... therefore fundamentally do not work on encrypted columns, even with an index present.

The practical solution for equality search is the deterministic blind index via HMAC already shown, which stores a separate, indexable hash value alongside the actual, randomized encrypted value. For range queries on encrypted numeric fields, specialized techniques such as order-preserving encryption exist, but they themselves leak certain information about relative magnitude and should therefore only be used when range search is indispensable and the residual risk is accepted.

7. Format-preserving encryption for structured data

Format-preserving encryption is a special form of data encryption where the ciphertext retains the same structure and length as the plaintext. A 16-digit credit card number gets encrypted into a different 16-digit number, instead of arbitrary-length binary ciphertext. This approach is especially valuable for existing systems whose database schema and validation logic expect fixed field lengths and formats and cannot be changed without an expensive migration.

The benefit of format-preserving encryption within data encryption lies in keeping legacy applications, reporting tools, and third-party systems that expect a fixed format (such as Luhn-valid credit card numbers for test purposes) working without schema changes. The tradeoff: format-preserving encryption usually offers somewhat weaker cryptographic guarantees in practice than classic randomized encryption, because the preserved structure necessarily leaks some information about the plaintext.

8. Key rotation without downtime

Key rotation is a mandatory part of any serious data encryption strategy, but in practice it is often postponed, because a naive approach means immediate downtime: decrypt every row with the old key and re-encrypt with the new key while the application stands still. With millions of rows, that is unacceptable in production systems.

The production-ready approach to key rotation for data encryption uses a version column that indicates which key version encrypted a given row. The application keeps both keys available simultaneously, decrypts using the version noted on the row, and always encrypts newly written or updated rows with the current key. A background process gradually migrates older rows to the new key, without the application ever standing still.

-- Key rotation pattern: version column tracks which key encrypted each row
ALTER TABLE customers ADD COLUMN key_version INT NOT NULL DEFAULT 1;

-- Application keeps both keys available during rotation
-- key_version = 1 uses the old key, key_version = 2 uses the new key

-- Background job migrates rows in small batches, no downtime
UPDATE customers
SET
  ssn_encrypted = pgp_sym_encrypt(
    pgp_sym_decrypt(ssn_encrypted, current_setting('app.encryption_key_v1')),
    current_setting('app.encryption_key_v2')
  ),
  key_version = 2
WHERE key_version = 1
  AND id IN (SELECT id FROM customers WHERE key_version = 1 LIMIT 1000);
-- Repeat in batches until all rows use key_version = 2
-- Old key can be retired from the KMS only after the last row migrates

9. Encryption approaches compared

The following table compares the key decision points in column-level data encryption and shows which approach fits which use case.

No single approach covers every requirement. Column-level data encryption requires deliberate tradeoffs between searchability, security, and compatibility with existing systems, which should be made individually for every sensitive field before implementation.

10. Summary

Column-level data encryption closes a gap that pure volume encryption leaves open: protection against a compromised database account with normal read access. pgcrypto delivers the necessary functions in PostgreSQL, but the actual security gain only emerges from consistently separating keys from data via a dedicated key management system.

The choice between deterministic and randomized data encryption decides between searchability and security, a blind index via HMAC combines both for equality search. Format-preserving encryption keeps legacy systems compatible, while a version column enables key rotation without production downtime. Every decision should be made individually per field, depending on which queries against the encrypted value are actually required.

11. FAQ: Column-Level Data Encryption