# Database TDE or column-level encryption for sensitive fields

- **Pattern:** `ab-001418` (`finserv-encryption.data-at-rest.tde-or-column-encryption`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-001418
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-001418)

## Why it matters

CWE-311 and CWE-312 describe the same failure at two layers: storing sensitive data without encryption (CWE-311) and storing passwords or credentials in a recoverable format (CWE-312). When neither TDE nor column-level encryption is present, every backup file, DB replica, and read replica contains plaintext cardholder data. PCI-DSS 4.0 Req-3.4 requires protection of stored cardholder data; NIST SC-28 requires protecting the confidentiality of data at rest. A breach that dumps the database — via SQL injection, misconfigured IAM, or insider access — becomes a full cardholder data exposure with no additional attacker steps. OWASP 2021 A02 (Cryptographic Failures) specifically calls out missing database encryption as a root cause.

## Severity rationale

High because absence of TDE or column encryption means any database read path — including replicas, snapshots, and developer copies — exposes sensitive data without additional exploitation steps.

## Remediation

Enable TDE at the cloud provider level first (it covers every table automatically), then add application-level column encryption for the highest-sensitivity fields. In Prisma, annotate encrypted columns so they're never queried or returned raw:

```typescript
// prisma/schema.prisma — document encryption expectations in comments
model Account {
  id         String @id @default(cuid())
  // Encrypted with AES-256-GCM before insert; decrypted on read in AccountService
  cardToken  String @db.Text
  // Encrypted with AES-256-GCM; never selected in list queries
  ssn        String? @db.Text
  balance    Decimal @db.Decimal(15, 2)
}
```

For PostgreSQL, enable encryption at rest in AWS RDS by checking "Enable encryption" when creating the instance, or use `pgcrypto` for column-level encryption on an existing unencrypted instance.

## Detection

- **ID:** `tde-or-column-encryption`
- **Severity:** `high`
- **What to look for:** Count all database tables that store sensitive data and enumerate which ones have TDE or column-level encryption. Quote the actual TDE setting or encryption function found. Check cloud database provider settings and application code for encryption patterns in schema migrations or ORM definitions.
- **Pass criteria:** Database has TDE enabled covering at least 1 sensitive table, OR at least 90% of sensitive columns have application-level encryption verified in schema files or ORM definitions. Report the count even on pass (e.g., "TDE enabled on PostgreSQL, 4 of 4 sensitive tables covered").
- **Fail criteria:** Database has no TDE enabled and no column-level encryption detected — 0 encryption mechanisms found for sensitive data.
- **Skip (N/A) when:** Project stores no financial data in its own database (purely external payment processor delegation — cite the actual processor found).
- **Detail on fail:** `"0 of 3 sensitive tables have encryption — no TDE, no column-level encryption in application code"`
- **Remediation:**
  - **For PostgreSQL/MySQL:** Enable encryption at rest via your database provider's console (AWS RDS, Supabase, etc.).
  - **For Application-Level:** Define encrypted columns in your schema migrations:
    ```typescript
    // Prisma example
    model Account {
      id String @id @default(cuid())
      cardNumber String @db.VarChar(255) // Application-encrypted before insert
      balance Decimal @db.Decimal(15, 2) // Application-encrypted before insert
    }
    ```

## External references

- cwe CWE-311
- cwe CWE-312
- owasp A02
- nist SC-28
- pci-dss Req-3.4

Taxons: cryptography-and-secrets

HTML version: https://auditbuffet.com/patterns/ab-001418