Transparent Data Encryption encrypts a database's files on disk without changing queries. Which attacks does it genuinely stop, which does it not, and which files besides the main datafiles must be covered?
answer
- protects artifacts, not sessions
- buffer pool holds plaintext → queries unchanged
- gaps: WAL, temp spill, replicas, logical dumps, core dumps
- key on the same volume = obfuscation
- verify by scanning the raw datafile for a known string
basics
~20 sIt stops anyone who obtains the files without the key: stolen disks, copied datafiles, leaked snapshots and physical backups. It stops nothing arriving through an authenticated session — injection, stolen credentials, over-broad privileges. Coverage must include redo/WAL logs, temp files, replicas and backups.
solid answer
~50 s**Stops:** theft or improper disposal of disks, a copied datafile, a snapshot shared to the wrong account, a physical backup landing in an unprotected bucket — anything where the attacker holds bytes but not the key. It also satisfies most "encrypted at rest" obligations and enables crypto-shredding when keys are scoped per tenant or tablespace. **Does not stop:** SQL injection, stolen application credentials, an over-privileged report user, a malicious administrator, or a memory dump — pages are decrypted into the buffer pool, so every legitimate session sees plaintext. It is not an access-control mechanism at all. **Coverage checklist:** redo/WAL and archived logs, replicas and the replication stream, temp/sort spill files, logical dumps produced by client tools, snapshots and backups. Each is data at rest and each is a common gap: a physical backup of encrypted files is encrypted, a logical dump generally is not. And the key must live where a file thief cannot reach — not on the same volume.
go deeper
Say it protects files taken off the machine and does nothing against someone who can log in and run queries.
Add why it is transparent (plaintext pages in memory) and list the coverage gaps: logs, temp files, replicas, logical dumps.
Discuss key custody outside the data volume, the availability dependency on the key service, restore-time key retention, and how you verify encryption empirically.
Position it as the media-loss and compliance control in a layered posture, and name what covers the residual risks: privileges, auditing, and field-level encryption for the few crown-jewel columns.
## How it works The engine encrypts pages on their way out to storage and decrypts them on the way in. Above the buffer pool everything is plaintext, so SQL, indexes, plans, constraints and applications are unaffected — hence "transparent". Modern CPUs with AES instructions keep overhead modest, though extra CPU per physical I/O shows on read-heavy workloads that miss cache. ## The precise threat model It defends **artifacts**, not **sessions**. The guarantee is: possession of the encrypted bytes without the key yields nothing. That covers a genuinely common class of incident — decommissioned drives, misplaced backup media, snapshots shared too broadly, a datafile copied by someone with filesystem but not database access, an object-storage bucket left public. What it explicitly does not cover: any path where the database itself decrypts for the requester. SQL injection, phished credentials, an application role with SELECT on everything, an administrator exporting a table, or reading process memory all return plaintext. Candidates who answer "we're encrypted at rest, so a breach isn't a data breach" are wrong in the way that matters. ## Coverage: where plaintext escapes 1. **Write-ahead / redo logs.** They contain row images. If unencrypted, an attacker reconstructs recent data. Check whether archived logs inherit the setting. 2. **Replicas and replication streams.** A replica has its own storage and its own key configuration; an unencrypted standby quietly undoes the control. Logical replication and CDC consumers receive plaintext by design. 3. **Temp and sort spill files.** Large sorts, hash joins and temporary tables spill to disk; older or partial implementations leave these unencrypted. 4. **Backups.** Physical backups of encrypted files stay encrypted — but are useless without the key, so key custody becomes a restore-time concern. Logical dumps produced by client tools are plaintext unless you encrypt them yourself. 5. **Exports, core dumps, swap.** A crash dump can contain buffer-pool plaintext; swap can contain page images. Disable core dumps or encrypt swap on hosts holding sensitive data. ## Key custody A key stored on the same volume as the datafiles reduces the control to obfuscation — the thief takes both. Keys belong in an external key manager, HSM, or cloud key service, released to the instance at startup under its own authentication. This creates a real availability dependency: if the key service is unreachable, the database may not start or may not open an encrypted tablespace. Plan for that, and retain historical key versions so old backups stay restorable. ## Verifying it is on Do not trust configuration flags alone. The convincing check is to read the raw file: scan a datafile for a string you know is stored in it. Finding it means that file is not encrypted. Repeat for the log directory, a temp file and a backup artifact — that four-way check catches most misconfigurations. ## Interview framing "It moves the risk from stolen media to stolen credentials and key custody. It is necessary for compliance and disposal risk, and it is not part of my defence against application-level compromise — that is privileges, least-privilege accounts, and for the top-sensitivity fields, encryption above the database."
- Your database uses Transparent Data Encryption and you take a nightly logical dump with a client export tool. Is that dump encrypted?No. A logical dump is produced through the SQL layer, so it contains plaintext rows regardless of how the datafiles are stored. You must encrypt the dump yourself — pipe it through an encryption step, write it to an encrypted target, or use the engine's physical backup tooling, which copies already-encrypted pages.
- What new failure mode does engine-level encryption introduce for availability?A dependency on the key service. If the key manager is unreachable at startup, or a key version was rotated away, the instance may refuse to open encrypted tablespaces — and restoring an old backup fails if the key that wrapped it no longer exists. Key retention, versioning and key-service availability become part of the database's recovery plan.
saying these in an interview costs you the question
- Saying encryption at rest means a credential compromise is not a data breach
- Assuming redo/WAL, temp files and replicas are automatically covered
- Believing a logical dump inherits the encryption of the datafiles
- Storing the key file next to the datafiles it protects
- Ignoring that losing a key version makes old backups unrestorable