Encrypt User PII at Rest with AES-256-GCM

Why Field-Level Encryption, Not Just Disk Encryption

AWS RDS, Google Cloud SQL, and Azure SQL all offer "encryption at rest." This encrypts the entire disk volume. It does not protect against SQL injection, compromised credentials, backup exposure, insider threats, or application-level breaches. Field-level encryption addresses all of these.

The Architecture

Our field-level encryption uses three cryptographic primitives:

1. AES-256-GCM for encryption. Authenticated encryption providing confidentiality and integrity.
2. SHA-256 key derivation for per-field keys. A single master key combined with the field name derives a unique key for each field type.
3. HMAC-SHA256 for searchable blind indexes. A deterministic hash enabling database lookups without decryption.

The Searchability Problem

If emails are encrypted, how do you run WHERE email = 'alice@example.com'? The answer is a blind index: a deterministic HMAC-SHA256 hash stored in a separate column.

const { piiSearchHash, piiDecrypt } = require('auth-shield');

// To find a user by email:
const emailHash = piiSearchHash('alice@example.com', masterKey, 'email');
const user = await db.query('SELECT * FROM users WHERE email_hash = $1', [emailHash]);

// Then decrypt the email for display:
const email = piiDecrypt(user.email_encrypted, masterKey, 'email');

Implementation with auth-shield

const { piiEncrypt, piiDecrypt, piiSearchHash, piiRotateKey } = require('auth-shield');

// Encrypt before database write
const encrypted = piiEncrypt('alice@example.com', process.env.PII_MASTER_KEY, 'email');
// Returns: base64(12-byte-nonce || ciphertext || 16-byte-GCM-tag)

// Decrypt after database read
const email = piiDecrypt(encrypted, process.env.PII_MASTER_KEY, 'email');

// Create search hash for lookups
const hash = piiSearchHash('alice@example.com', process.env.PII_MASTER_KEY, 'email');

// Rotate encryption key
const reEncrypted = piiRotateKey(encrypted, oldKey, newKey, 'email');

Database Schema Changes

-- Step 1: Add encrypted columns and hash columns
ALTER TABLE users
  ADD COLUMN email_encrypted TEXT,
  ADD COLUMN email_hash CHAR(64),
  ADD COLUMN phone_encrypted TEXT,
  ADD COLUMN phone_hash CHAR(64),
  ADD COLUMN name_encrypted TEXT;

-- Step 2: Create indexes on hash columns
CREATE INDEX idx_users_email_hash ON users (email_hash);
CREATE INDEX idx_users_phone_hash ON users (phone_hash);

Migration Strategy

Phase 1: Dual-Write. Deploy schema changes. Write to both plaintext and encrypted columns.

Phase 2: Backfill. Encrypt existing plaintext data in batches of 500 rows.

Phase 3: Verify and Switch. Update application to read from encrypted columns only.

Phase 4: Drop Plaintext. Drop the original plaintext columns. Point of no return.

Performance

For a registration that encrypts email, phone, and name (3 fields), the total overhead is approximately 6 microseconds. A PostgreSQL INSERT takes 500-5,000 microseconds. The encryption overhead is invisible.

Compliance Coverage

• HIPAA (164.312(a)(2)(iv)): encryption of ePHI at rest — now mandatory
• GDPR (Article 32): technical measures for data protection
• CCPA/CPRA: reasonable security measures for personal information
• SOC 2 (CC6.1): encryption as a logical access control
• PCI-DSS (Req 3): protect stored cardholder data

Under GDPR, if encrypted data is breached and the key was not compromised, the breach may not require notification because the data is "unintelligible to any person who is not authorized to access it."