How do you encrypt data at rest and in transit in a database?

Encryption in transit

Without TLS, database traffic — including the authentication handshake and every query/result — travels as plaintext over the network, readable by anyone positioned to intercept it (a compromised network device, a malicious actor on a shared network, a misconfigured cloud VPC).

# PostgreSQL: require SSL/TLS for connections
sslmode=require   # (or better: verify-full, which also validates the server's certificate)

Most managed cloud database services enforce or strongly encourage TLS by default; self-managed deployments must explicitly configure server-side TLS certificates and require clients to use them (sslmode=require/verify-full in PostgreSQL, useSSL=true for many JDBC-based connections). verify-full (or equivalent) additionally validates that the server's certificate matches the expected hostname, preventing a man-in-the-middle from presenting a different valid certificate.

Encryption at rest — the storage layer

Transparent Data Encryption (TDE) or full-disk/volume encryption encrypts the physical files/blocks the database is stored on, so that if the underlying storage media (a disk, a backup file, a cloud storage snapshot) is stolen or accessed outside the running database process, the data is unreadable without the encryption key.

-- Most cloud-managed databases: TDE is a configuration toggle, not application code
-- e.g., AWS RDS: "Enable encryption" at instance creation, backed by a KMS key

Important limitation: TDE protects data at the storage layer — it does nothing once a query is actually executed through an authenticated connection to a running database instance; at that point, the database decrypts data transparently and returns plaintext results to any authorized query, exactly as if encryption weren't enabled at all. TDE's threat model is specifically "someone got physical/file-level access to the storage without going through the database's authentication," not "protect data from an authenticated but malicious/compromised application."

Column-level / application-level encryption — defense in depth for the most sensitive data

For specific highly sensitive fields (SSNs, payment card data, health records), encrypting the value itself (before it's stored, using an application-managed key, or via database features like PostgreSQL's pgcrypto) means the data stays encrypted even to someone with a valid, authenticated database connection who queries the raw column directly — a materially stronger guarantee than TDE alone provides, at the cost of losing the ability to index, sort, or query that column's plaintext value directly in SQL (you generally need to decrypt in the application layer, or query by a separate deterministic hash/token if you need equality lookups).

-- Example using PostgreSQL's pgcrypto extension
INSERT INTO payment_methods (customer_id, card_number_encrypted)
VALUES (42, pgp_sym_encrypt('4111111111111111', 'encryption-key'));

SELECT pgp_sym_decrypt(card_number_encrypted, 'encryption-key') FROM payment_methods WHERE customer_id = 42;

(In production, the encryption key itself should come from a dedicated key-management service — AWS KMS, HashiCorp Vault — never hardcoded or stored alongside the data it protects.)

Layer both: TLS in transit is close to non-negotiable for any production system handling real user data, TDE-equivalent at-rest encryption is a reasonable default (and often required for compliance regimes like PCI-DSS, HIPAA), and column-level encryption should be reserved specifically for the subset of fields whose exposure would be most damaging — applying it universally adds real query/indexing complexity for data that often doesn't need that extra layer.