Part 185: Column-Level Encryption & Key Management with SQL

Protecting sensitive PII, credit card tokens, and secrets at rest.

1. Encryption at Rest (TDE) vs Column Encryption

• Transparent Data Encryption (TDE): Encrypts physical database files on disk. Protects against stolen hard drives, but does not protect against compromised database superusers.
• Column-Level Encryption: Encrypts sensitive fields (SSNs, API keys) using cryptographic functions (pgcrypto). Even database administrators cannot read raw data without the encryption key!

2. Using pgcrypto in PostgreSQL

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Create table storing encrypted binary data
CREATE TABLE patient_records (
    id SERIAL PRIMARY KEY,
    patient_name VARCHAR(100),
    ssn_encrypted BYTEA NOT NULL
);

-- Encrypting data with AES-256
INSERT INTO patient_records (patient_name, ssn_encrypted)
VALUES (
    'Jane Doe', 
    pgp_sym_encrypt('123-45-6789', 'MySecretMasterKey2026!')
);

-- Decrypting data at query time
SELECT 
    patient_name,
    pgp_sym_decrypt(ssn_encrypted, 'MySecretMasterKey2026!') AS decrypted_ssn
FROM patient_records;

Key Takeaways

• Never hardcode encryption keys in plain SQL text; pass keys via parameterized session variables or KMS integrations.

In Part 186, we explore Dynamic Data Masking & Anonymization for GDPR & HIPAA!