Encryption at Rest: TDE Concepts, pgcrypto & Column Encryption

Encryption at Rest: TDE Concepts, pgcrypto & Column Encryption

Why Encrypt Data at Rest?

Data at rest means data stored on disk—database files, backups, log files, and temporary files. If an attacker steals a physical hard drive or gains raw file access, plaintext data is immediately readable unless encryption protects it. Encryption at rest ensures that even without the database engine, the data remains unreadable. This matters for compliance with frameworks such as HIPAA, PCI-DSS, and SOC 2, and it is tested directly on the Oracle DBA CA, Microsoft DP-300, and DAMA CDMP exams.

Three Approaches Compared

1. Application-Level (Column) Encryption — pgcrypto in PostgreSQL

The `pgcrypto` extension adds cryptographic functions directly inside PostgreSQL. Encryption and decryption happen at the SQL layer before data is written to or read from storage. The database engine itself stores only ciphertext in the column; even a DBA with OS-level access to the data files sees scrambled bytes.

The encryption boundary is at the SQL statement: only a caller who supplies the correct passphrase (or key) can decrypt. This means key management is the application's or DBA's responsibility. If the passphrase is lost, data is unrecoverable.

Core functions:

• `pgp_sym_encrypt(plaintext text, passphrase text)` — returns `bytea` ciphertext using OpenPGP symmetric encryption.
• `pgp_sym_decrypt(ciphertext bytea, passphrase text)` — returns the original plaintext.

Because the output is `bytea`, the encrypted column is defined as `bytea`, not `text`.

2. Oracle Transparent Data Encryption (TDE)

Oracle TDE encrypts data files, redo logs, and undo segments at the storage layer. The word transparent means the database engine decrypts data automatically when an authorized session reads it; applications require no code changes. The encryption boundary is between the Oracle engine and the OS/storage layer.

Oracle TDE supports tablespace-level encryption (the entire tablespace is encrypted) and column-level encryption (individual columns). The master encryption key is stored in an Oracle Wallet or a Hardware Security Module (HSM). If someone steals the data files but not the wallet, they cannot read the data.

3. Azure SQL / SQL Server Transparent Data Encryption (TDE)

Azure SQL Database enables TDE by default on all new databases. It encrypts the entire database data file, log file, and backups using AES-256. The encryption boundary is between the SQL Server engine and the storage subsystem. Two key management modes are available:

• Service-managed TDE: Microsoft manages the Database Encryption Key (DEK) automatically.
• Customer-managed TDE (Bring Your Own Key, BYOK): the customer stores the master key in Azure Key Vault.

Applications connect and query normally; decryption is invisible. This maps to DP-300 exam content on Azure SQL security.

Where the Encryption Boundary Lies

| Approach | Boundary | Who Holds the Key | Transparent to App? |

|---|---|---|---|

| pgcrypto (PostgreSQL) | SQL function call | Application / DBA | No — app must call decrypt function |

| Oracle TDE | Database engine ↔ OS | Oracle Wallet / HSM | Yes |

| Azure SQL TDE | Database engine ↔ Storage | Microsoft or Key Vault | Yes |

Installing pgcrypto and Encrypting a Column

The `pgcrypto` extension ships with the standard PostgreSQL distribution. A superuser installs it once per database:

```sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

```

An `employees` table with an encrypted SSN column:

```sql

CREATE TABLE employees (

emp_id SERIAL PRIMARY KEY,

name TEXT NOT NULL,

ssn_enc BYTEA

);

```

Inserting a row:

```sql

INSERT INTO employees (name, ssn_enc)

VALUES (

'Jane Smith',

pgp_sym_encrypt('123-45-6789', 'my_strong_passphrase')

);

```

Decrypting for authorized queries:

```sql

SELECT name,

pgp_sym_decrypt(ssn_enc, 'my_strong_passphrase') AS ssn

FROM employees;

```

A direct `SELECT ssn_enc FROM employees` returns binary ciphertext. Inspecting the underlying data file with OS tools (`strings`, `pg_filedump`) reveals no readable SSN.

Passphrase and Key Security

A passphrase hardcoded in application source code defeats the purpose of encryption. Best practices:

• Store passphrases in a secrets manager (HashiCorp Vault, AWS Secrets Manager, Azure Key Vault).
• Rotate keys on a schedule; re-encrypt stored data after rotation.
• Limit which database roles can execute decrypt queries using `GRANT` and row-level security.
• Never log decrypted values; ensure query logging excludes sensitive parameters.

Safety and Operational Cautions

• Key loss = data loss. Always maintain secure, tested backups of encryption keys and wallets in a separate physical location from the encrypted data.
• Performance impact. Column encryption adds CPU overhead on every read/write of encrypted columns. Benchmark before deploying to production.
• Backup encryption. Encrypting the live database does not automatically encrypt logical backups (`pg_dump` output). Pipe dumps through `gpg` or use encrypted backup tools.
• Audit logging. Enable PostgreSQL's `pgaudit` extension or Oracle's Unified Auditing to log who decrypted what and when. Encryption without auditing leaves gaps in your security posture.
• Physical server access. TDE and pgcrypto protect data on stolen disks. They do not protect against a privileged OS user on a running server who can read process memory. Use defense-in-depth: physical security, OS hardening, and network controls alongside encryption.