Eric Campbell 7 min read Security

Encryption at Rest: AES and Database Encryption

Encryption at rest protects data stored on disk, as opposed to encryption in transit which secures data moving across networks. The distinction matters because the threat models differ significantly....

Key Insights

  • AES-256-GCM should be your default choice for encryption at rest—it provides both confidentiality and integrity verification in a single operation, preventing tampering attacks that CBC mode can’t detect.
  • Transparent Database Encryption (TDE) protects against physical theft but not application-level attacks; sensitive fields like SSNs and payment data need application-layer encryption for defense in depth.
  • Key management is where most encryption implementations fail—use envelope encryption with a cloud KMS to separate key hierarchies and enable rotation without re-encrypting all your data.

Introduction to Encryption at Rest

Encryption at rest protects data stored on disk, as opposed to encryption in transit which secures data moving across networks. The distinction matters because the threat models differ significantly. In-transit attacks target network sniffing and man-in-the-middle scenarios. At-rest attacks target stolen hard drives, compromised backups, decommissioned hardware that wasn’t properly wiped, and insider threats with physical or administrative access.

The attack surface is larger than most developers realize. That database backup sitting in S3? It’s data at rest. The SSD in your production server? Data at rest. The laptop your DBA uses for emergency maintenance? Data at rest. When any of these leave your control—through theft, improper disposal, or a misconfigured bucket policy—unencrypted data walks out the door.

Compliance frameworks have caught up to this reality. GDPR requires “appropriate technical measures” for personal data protection, and regulators interpret this to include encryption. HIPAA’s Security Rule explicitly addresses encryption for protected health information. PCI-DSS mandates encryption of cardholder data at rest with documented key management procedures. Even if compliance isn’t driving your requirements today, it likely will tomorrow.

AES Fundamentals

AES (Advanced Encryption Standard) is a symmetric block cipher that operates on 128-bit blocks of data. “Symmetric” means the same key encrypts and decrypts. “Block cipher” means it processes fixed-size chunks rather than streaming bytes individually.

You’ll encounter three key sizes: 128, 192, and 256 bits. AES-256 is the standard choice for sensitive data—not because AES-128 is broken (it isn’t), but because the marginal performance cost is negligible and it satisfies compliance requirements that specify “256-bit encryption.”

The mode of operation determines how AES handles data larger than 128 bits. CBC (Cipher Block Chaining) was the traditional choice, but it has a critical weakness: it provides confidentiality without integrity. An attacker can flip bits in ciphertext and produce predictable changes in plaintext without detection. This enables padding oracle attacks and other nastiness.

GCM (Galois/Counter Mode) solves this by providing authenticated encryption. It produces both ciphertext and an authentication tag. Decryption fails if anyone tampers with the ciphertext. Use GCM unless you have a specific reason not to.

Here’s AES-256-GCM encryption in Python:

import os
import base64
from cryptography.hazmat.primitives.ciphers.aead import AESGCM

def encrypt_aes_gcm(plaintext: str, key: bytes) -> dict:
    """Encrypt plaintext using AES-256-GCM."""
    # Generate a random 96-bit nonce (NIST recommendation for GCM)
    nonce = os.urandom(12)
    
    aesgcm = AESGCM(key)
    ciphertext = aesgcm.encrypt(nonce, plaintext.encode('utf-8'), None)
    
    return {
        'ciphertext': base64.b64encode(ciphertext).decode('utf-8'),
        'nonce': base64.b64encode(nonce).decode('utf-8')
    }

def decrypt_aes_gcm(encrypted_data: dict, key: bytes) -> str:
    """Decrypt AES-256-GCM encrypted data."""
    ciphertext = base64.b64decode(encrypted_data['ciphertext'])
    nonce = base64.b64decode(encrypted_data['nonce'])
    
    aesgcm = AESGCM(key)
    plaintext = aesgcm.decrypt(nonce, ciphertext, None)
    
    return plaintext.decode('utf-8')

# Usage
key = os.urandom(32)  # 256 bits - in production, retrieve from KMS
sensitive_data = "SSN: 123-45-6789"

encrypted = encrypt_aes_gcm(sensitive_data, key)
decrypted = decrypt_aes_gcm(encrypted, key)

The nonce (number used once) must be unique for every encryption operation with the same key. Reusing a nonce with GCM is catastrophic—it enables key recovery. Generate it randomly or use a counter, but never reuse it.

Key Management Essentials

Here’s the uncomfortable truth: encryption algorithms are the easy part. Key management is where implementations fail. Where do you store the key? How do you rotate it? Who has access? How do you revoke access? How do you handle key loss?

The answer is a key hierarchy. At the top sits your master key, stored in a Hardware Security Module (HSM) or cloud KMS. This master key never encrypts data directly. Instead, it encrypts Data Encryption Keys (DEKs), which encrypt your actual data. This pattern is called envelope encryption.

The benefits are significant. You can rotate your master key without re-encrypting all data—just re-encrypt the DEKs. You can have different DEKs for different data classifications. You can audit master key usage separately from data access.

Here’s envelope encryption using AWS KMS:

import boto3
import os
import base64
from cryptography.hazmat.primitives.ciphers.aead import AESGCM

class EnvelopeEncryption:
    def __init__(self, kms_key_id: str):
        self.kms = boto3.client('kms')
        self.kms_key_id = kms_key_id
    
    def encrypt(self, plaintext: str) -> dict:
        """Encrypt using envelope encryption pattern."""
        # Generate a data key from KMS
        response = self.kms.generate_data_key(
            KeyId=self.kms_key_id,
            KeySpec='AES_256'
        )
        
        # KMS returns both plaintext and encrypted versions of the DEK
        plaintext_key = response['Plaintext']
        encrypted_key = response['CiphertextBlob']
        
        # Encrypt data locally with the plaintext DEK
        nonce = os.urandom(12)
        aesgcm = AESGCM(plaintext_key)
        ciphertext = aesgcm.encrypt(nonce, plaintext.encode('utf-8'), None)
        
        # Return encrypted data + encrypted DEK (never store plaintext DEK)
        return {
            'ciphertext': base64.b64encode(ciphertext).decode('utf-8'),
            'nonce': base64.b64encode(nonce).decode('utf-8'),
            'encrypted_key': base64.b64encode(encrypted_key).decode('utf-8')
        }
    
    def decrypt(self, encrypted_data: dict) -> str:
        """Decrypt using envelope encryption pattern."""
        # Ask KMS to decrypt the DEK
        encrypted_key = base64.b64decode(encrypted_data['encrypted_key'])
        response = self.kms.decrypt(CiphertextBlob=encrypted_key)
        plaintext_key = response['Plaintext']
        
        # Decrypt data locally
        ciphertext = base64.b64decode(encrypted_data['ciphertext'])
        nonce = base64.b64decode(encrypted_data['nonce'])
        
        aesgcm = AESGCM(plaintext_key)
        return aesgcm.decrypt(nonce, ciphertext, None).decode('utf-8')

Notice that the plaintext DEK exists only in memory during encryption/decryption. What gets stored is the encrypted DEK alongside the ciphertext. To decrypt, an attacker needs both your encrypted data and access to your KMS—defense in depth.

Database-Level Encryption: TDE

Transparent Data Encryption operates at the database engine level, encrypting data files, log files, and backups automatically. “Transparent” means applications don’t need modification—encryption and decryption happen below the SQL layer.

TDE protects against physical theft scenarios: someone steals your database server, copies your backup files, or pulls the drives from decommissioned hardware. They get encrypted bits without the key.

TDE does not protect against: SQL injection attacks (the attacker queries through the database engine, which decrypts transparently), compromised application credentials, or malicious DBAs with key access. It’s a floor, not a ceiling.

Here’s enabling TDE in SQL Server:

-- Create a master key in the master database
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ComplexPassword123!';

-- Create a certificate protected by the master key
CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'TDE Certificate';

-- Switch to your target database
USE YourDatabase;

-- Create a database encryption key protected by the certificate
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECertificate;

-- Enable encryption
ALTER DATABASE YourDatabase SET ENCRYPTION ON;

-- Verify encryption status
SELECT db.name, db.is_encrypted, dm.encryption_state, dm.percent_complete
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id;

For PostgreSQL, native TDE arrived in version 16. For earlier versions, you can use filesystem-level encryption (LUKS on Linux) or encrypt specific columns using pgcrypto:

-- Enable pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt data using pgp_sym_encrypt
INSERT INTO users (email, ssn_encrypted)
VALUES (
    'user@example.com',
    pgp_sym_encrypt('123-45-6789', 'encryption_key_here')
);

-- Decrypt on read
SELECT email, pgp_sym_decrypt(ssn_encrypted::bytea, 'encryption_key_here') as ssn
FROM users;

Application-Level Field Encryption

TDE encrypts everything uniformly. Sometimes you need granular control: different keys for different tenants, specific compliance requirements for certain fields, or encryption that persists even when data leaves your database.

Application-level encryption handles these cases. You encrypt sensitive fields before they hit the database and decrypt after retrieval. The database stores ciphertext and has no access to plaintext.

The trade-off is searchability. You can’t run WHERE ssn = '123-45-6789' on encrypted data without decrypting every row first. Solutions exist—deterministic encryption enables equality searches, order-preserving encryption enables range queries—but they leak information. For truly sensitive data, accept that you’ll search on other indexed fields and decrypt in application code.

Here’s a practical pattern for field-level encryption:

from dataclasses import dataclass
from typing import Optional
import json

@dataclass
class EncryptedField:
    """Wrapper for encrypted field data."""
    ciphertext: str
    nonce: str
    encrypted_key: str
    key_version: int  # Track which key version encrypted this

class SecureUserRepository:
    def __init__(self, db_connection, envelope_encryption: EnvelopeEncryption):
        self.db = db_connection
        self.crypto = envelope_encryption
        self.current_key_version = 1
    
    def create_user(self, email: str, ssn: str, credit_card: str) -> int:
        """Create user with encrypted PII fields."""
        # Encrypt sensitive fields
        encrypted_ssn = self.crypto.encrypt(ssn)
        encrypted_ssn['key_version'] = self.current_key_version
        
        encrypted_cc = self.crypto.encrypt(credit_card)
        encrypted_cc['key_version'] = self.current_key_version
        
        # Store email in plain (for searching), PII as JSON blobs
        cursor = self.db.execute(
            """INSERT INTO users (email, ssn_encrypted, credit_card_encrypted)
               VALUES (?, ?, ?)""",
            (email, json.dumps(encrypted_ssn), json.dumps(encrypted_cc))
        )
        return cursor.lastrowid
    
    def get_user(self, user_id: int) -> Optional[dict]:
        """Retrieve user with decrypted PII fields."""
        row = self.db.execute(
            "SELECT email, ssn_encrypted, credit_card_encrypted FROM users WHERE id = ?",
            (user_id,)
        ).fetchone()
        
        if not row:
            return None
        
        return {
            'email': row[0],
            'ssn': self.crypto.decrypt(json.loads(row[1])),
            'credit_card': self.crypto.decrypt(json.loads(row[2]))
        }

Performance and Operational Considerations

AES-256-GCM on modern CPUs with AES-NI instructions adds roughly 1-3% overhead for typical workloads. The KMS round-trip for envelope encryption adds latency—cache decrypted DEKs in memory (with appropriate TTL) to avoid per-operation KMS calls.

Key rotation without downtime requires the key version tracking shown above. When you rotate:

  1. Generate new DEK, increment version number
  2. New writes use the new key
  3. Background job re-encrypts old data with new key
  4. After migration completes, revoke old key

Backup encryption is non-negotiable. If your database is encrypted but backups aren’t, you’ve just moved the vulnerability. Ensure backup tools encrypt with separate keys, and test restore procedures regularly—encrypted backups you can’t restore are worthless.

Best Practices Checklist

Use this checklist for your encryption-at-rest implementation:

  • AES-256-GCM for all symmetric encryption
  • Envelope encryption pattern with cloud KMS for key management
  • TDE enabled for database-level protection
  • Application-level encryption for high-sensitivity fields (PII, payment data)
  • Key version tracking for rotation support
  • Quarterly key rotation schedule documented and tested
  • Encrypted backups with separate key hierarchy
  • Audit logging for all key access operations
  • Documented key recovery procedures tested annually
  • Access controls limiting key usage to specific services/roles

Encryption at rest is a layered problem requiring layered solutions. TDE handles the physical theft scenario. Application-level encryption handles the compromised-application scenario. Proper key management makes both sustainable. Implement all three.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.