Database Encryption: Transparent Encryption, Column Encryption, and Key Management
A technical deep-dive into database encryption strategies: TDE vs column-level vs application-level encryption, envelope encryption with KMS, pgcrypto, Always Encrypted in SQL Server, and key rotation without downtime.
Encrypting data at rest is a fundamental security control, yet the implementation details determine whether encryption actually protects your data or provides only compliance theater. This guide covers the three main tiers of database encryption, explains envelope encryption with a Key Management Service, and addresses the hardest operational challenge: rotating keys without downtime.
The Three Tiers of Database Encryption
Tier 1: Transparent Data Encryption (TDE)
Transparent Data Encryption encrypts the database files on disk. The database engine decrypts data automatically when it reads pages into memory, making the encryption completely invisible to queries and applications. If an attacker steals the raw data files or disk volume, they see only ciphertext.
What TDE protects against:
- Stolen disk drives or cloud storage volumes
- Database backup file theft
- Decommissioned hardware attacks
What TDE does not protect against:
- A compromised database server (the engine decrypts data before serving queries)
- A compromised DBA account
- SQL injection that reads data through the query engine
- Memory scraping attacks
TDE is the easiest encryption to implement and has minimal performance impact (typically 3-5% overhead). Every cloud-managed database supports it:
# AWS RDS: Enable encryption at creation time
aws rds create-db-instance \
--db-instance-identifier mydb \
--db-instance-class db.t3.medium \
--engine postgres \
--storage-encrypted \
--kms-key-id arn:aws:kms:us-east-1:123456789012:key/your-key-id \
--master-username admin \
--master-user-password 'use-secrets-manager-instead'
# For SQL Server, enable TDE after creation:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'use-a-strong-passphrase';
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'TDE Certificate';
USE MyDatabase;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
ALTER DATABASE MyDatabase SET ENCRYPTION ON;
Tier 2: Column-Level Encryption
Column-level encryption (CLE) encrypts specific columns within the database. The database engine stores ciphertext and the decryption happens either within the engine (database-managed keys) or in the application (client-managed keys).
Column-level encryption protects against:
- Everything TDE protects against
- Compromised DBAs who can read raw data but not the encryption key
- Overly broad SELECT permissions
SQL Server's Always Encrypted (column-level) is a standout implementation because the database server itself never has access to the encryption key:
-- Create Column Master Key (references a key stored in Azure Key Vault or Windows Certificate Store)
CREATE COLUMN MASTER KEY MyCMK
WITH (
KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
KEY_PATH = 'https://myvault.vault.azure.net/keys/MyKey/abc123'
);
-- Create Column Encryption Key (encrypted with CMK)
CREATE COLUMN ENCRYPTION KEY MyCEK
WITH VALUES (
COLUMN_MASTER_KEY = MyCMK,
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x01700000... -- Generated by SSMS or PowerShell tooling
);
-- Create table with encrypted columns
CREATE TABLE Patients (
PatientID INT PRIMARY KEY,
Name NVARCHAR(100), -- Not encrypted
SSN NVARCHAR(11) ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = MyCEK,
ENCRYPTION_TYPE = DETERMINISTIC, -- Allows equality searches
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_512'
),
DateOfBirth DATE ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = MyCEK,
ENCRYPTION_TYPE = RANDOMIZED, -- More secure, no equality search
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_512'
)
);
With Always Encrypted, the SQL Server only ever handles ciphertext. The .NET driver performs encryption/decryption on the client using the column master key from Azure Key Vault or the Windows certificate store.
Tier 3: Application-Level Encryption
Application-level encryption happens entirely in your application code before data is written to the database. The database stores only encrypted blobs and has no ability to decrypt them.
Advantages:
- Database compromise (including OS-level) does not expose plaintext
- Complete control over key material and algorithms
- Works with any database engine
Disadvantages:
- Cannot query or index encrypted values (without deterministic encryption)
- Application complexity increases significantly
- Harder to audit and get right
Using pgcrypto in PostgreSQL for application-managed encryption at the database layer:
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Symmetric encryption with AES-256 (via OpenPGP)
INSERT INTO health_records (patient_id, diagnosis_encrypted)
VALUES (
123,
pgp_sym_encrypt(
'Type 2 Diabetes',
current_setting('app.encryption_key'),
'compress-algo=1, cipher-algo=aes256'
)
);
-- Decrypt when reading
SELECT
patient_id,
pgp_sym_decrypt(
diagnosis_encrypted,
current_setting('app.encryption_key')
) AS diagnosis
FROM health_records
WHERE patient_id = 123;
-- Asymmetric encryption: encrypt with public key, decrypt with private key
-- Useful when many services write but only one service reads
INSERT INTO sensitive_events (event_id, payload_encrypted)
VALUES (
gen_random_uuid(),
pgp_pub_encrypt(
'{"action": "payment", "amount": 9900}',
dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----...')
)
);
Envelope Encryption with KMS
Encrypting data directly with a KMS key is expensive (network round-trip per operation) and hits KMS rate limits quickly. The solution is envelope encryption: use KMS to protect a data encryption key (DEK), and use the DEK to encrypt the actual data locally.
┌─────────────────────────────────────────────────────┐
│ KMS Key (Key Encryption Key / KEK) │
│ - Stored in KMS, never leaves HSM │
│ - Used only to encrypt/decrypt the DEK │
└────────────────────────┬────────────────────────────┘
│ wraps/unwraps
┌────────────────────────▼────────────────────────────┐
│ Data Encryption Key (DEK) │
│ - Generated locally (AES-256) │
│ - Stored encrypted (ciphertext) in your database │
│ - Cached in application memory after decryption │
└────────────────────────┬────────────────────────────┘
│ encrypts/decrypts
┌────────────────────────▼────────────────────────────┐
│ Actual Data │
│ - Encrypted by DEK before writing to database │
└─────────────────────────────────────────────────────┘
Implementation with AWS KMS:
import boto3
import os
from cryptography.hazmat.primitives.ciphers.aead import AESGCM
kms = boto3.client('kms', region_name='us-east-1')
KEY_ID = 'arn:aws:kms:us-east-1:123456789:key/your-key-id'
def generate_data_key():
"""Generate a new DEK, return (plaintext_key, encrypted_key)"""
response = kms.generate_data_key(
KeyId=KEY_ID,
KeySpec='AES_256'
)
return response['Plaintext'], response['CiphertextBlob']
def encrypt_value(plaintext: str) -> dict:
"""Encrypt a value with envelope encryption"""
plaintext_key, encrypted_key = generate_data_key()
# Use the plaintext DEK to encrypt data locally (no KMS call)
aesgcm = AESGCM(plaintext_key)
nonce = os.urandom(12)
ciphertext = aesgcm.encrypt(nonce, plaintext.encode(), None)
# Clear plaintext key from memory as soon as possible
del plaintext_key
return {
'encrypted_key': encrypted_key, # Store this in DB
'nonce': nonce, # Store this in DB
'ciphertext': ciphertext # Store this in DB
}
def decrypt_value(encrypted_record: dict) -> str:
"""Decrypt a value using the stored encrypted DEK"""
# Decrypt the DEK with KMS (one KMS call per decryption)
response = kms.decrypt(
CiphertextBlob=encrypted_record['encrypted_key'],
KeyId=KEY_ID
)
plaintext_key = response['Plaintext']
aesgcm = AESGCM(plaintext_key)
plaintext = aesgcm.decrypt(
encrypted_record['nonce'],
encrypted_record['ciphertext'],
None
)
del plaintext_key
return plaintext.decode()
For high-throughput applications, cache the decrypted DEK in memory for a short TTL (5-15 minutes) to avoid a KMS call per operation. Use a per-tenant DEK rather than a single global key so you can revoke access for a specific tenant by disabling their DEK.
Key Rotation Without Downtime
Key rotation is the most operationally complex part of any encryption scheme. The goal is to re-encrypt all data with a new key without causing downtime or data loss.
Rotating a KMS Key (AWS)
For envelope encryption, rotating the KMS key (KEK) does not require re-encrypting any data. AWS KMS supports automatic key rotation annually:
aws kms enable-key-rotation --key-id arn:aws:kms:us-east-1:123456789:key/your-key-id
When the KMS key rotates, AWS keeps all previous key versions and uses them to decrypt data encrypted under those versions. New encryptions use the latest key version.
Rotating a Data Encryption Key (DEK)
Rotating the actual DEK requires re-encrypting the data. The safest pattern is a dual-key approach:
- Generate a new DEK.
- Begin writing new data with the new DEK (mark records with key version).
- Background-migrate old records: decrypt with old DEK, re-encrypt with new DEK.
- Once migration completes, delete the old DEK.
-- Track which key version encrypted each record
ALTER TABLE sensitive_records ADD COLUMN key_version INTEGER DEFAULT 1;
-- Background migration job (run in batches to avoid lock contention)
UPDATE sensitive_records
SET
encrypted_data = re_encrypt_with_new_key(encrypted_data, old_key_id, new_key_id),
key_version = 2
WHERE key_version = 1
AND id IN (
SELECT id FROM sensitive_records
WHERE key_version = 1
LIMIT 1000
);
The re_encrypt_with_new_key function would be called from your application layer, decrypting with the old DEK and re-encrypting with the new DEK. Running this in batches with LIMIT 1000 and a small sleep between batches prevents table locking and production performance degradation.
Performance Implications
Encryption is not free. Measured overhead by approach:
| Method | CPU Overhead | Query Impact |
|---|---|---|
| TDE (AES-256-XTS) | 3-5% | None (transparent) |
| pgcrypto pgp_sym_encrypt | 10-20% | Cannot index encrypted values |
| Always Encrypted | 5-15% | Deterministic only allows equality |
| Application-level AES-256-GCM | 1-3% for crypto | Full index loss on encrypted columns |
The most common performance pitfall is encrypting columns that are heavily used in WHERE clauses or JOIN conditions. If you must encrypt a field you also query, use deterministic encryption and accept the ciphertext frequency leakage, or maintain a secure hash alongside the encrypted value for lookup purposes:
-- Store both the encrypted SSN and a keyed hash for lookups
ALTER TABLE users ADD COLUMN ssn_encrypted BYTEA;
ALTER TABLE users ADD COLUMN ssn_lookup_hash VARCHAR(64); -- HMAC-SHA256
-- Index on the hash for fast lookups
CREATE INDEX idx_users_ssn_lookup ON users(ssn_lookup_hash);
The HMAC key must be different from the encryption key and should also be stored in your KMS.