At which layers can a relational database's data be encrypted at rest — storage volume or filesystem, database engine, and application or column level — and what fundamentally differs between them?
answer
- volume: everything, until the disk is mounted
- TDE: engine encrypts its own files + backups, queries unchanged
- app/column: DB never sees plaintext, indexes break
- higher layer = fewer attackers included, less functionality
- at rest also covers WAL, temp files, backups, snapshots
basics
~20 sVolume or filesystem encryption protects whole disks and is invisible to the database. Engine-level Transparent Data Encryption encrypts the database's own files and backups. Application or column encryption encrypts values before storage, so even the database never sees plaintext — but indexing and range queries break.
solid answer
~50 sThree layers, each higher in the stack than the last: - **Volume / filesystem encryption** — the block device or filesystem is encrypted, covering every file on it including datafiles, logs and temp files. Transparent to the engine, near-zero effort, but any process that can read the mounted filesystem reads plaintext. - **Engine-level (Transparent Data Encryption)** — the database encrypts its own data files, and usually its logs and physical backups, with keys it manages. Queries are unchanged. Protects the files as artifacts — a stolen datafile is useless — but every authenticated session still sees plaintext. - **Application / column-level** — the value is encrypted before it reaches the database, so a compromised database account, a SQL-injection dump, or the engine's memory yield ciphertext. The cost is functional: indexing, sorting, range queries and joins on that column stop working normally. Rule of thumb: the higher the layer, the more attackers it excludes and the more capability you lose.
go deeper
Name the three layers and the one-line difference: who can still see plaintext at each.
Explain that engine-level encryption is transparent because pages are plaintext in memory, and that column-level encryption costs indexing and range queries.
Discuss coverage gaps (WAL, temp, backups), per-tenant keys and crypto-shredding, and where each layer sits in a real threat model.
Argue the control set against named threats and compliance obligations, weighing operational cost, key-management dependency and functional loss rather than encrypting everything.
## What "at rest" means Data at rest is data sitting in persistent storage — datafiles, write-ahead/redo logs, temp and sort spill files, backups, snapshots, exports — as opposed to data moving over a network or being processed in memory. Encryption at rest exists to make *stolen storage* worthless: a decommissioned disk, a cloud snapshot copied to the wrong account, a backup tape, a laptop holding a dump. ## Layer 1: volume or filesystem encryption The operating system or cloud provider encrypts blocks as they are written to the device. The database knows nothing about it; overhead on modern CPUs with hardware acceleration is small. It covers *everything* on the volume automatically, which is its strength — no file is forgotten. Its limit: protection ends the moment the volume is mounted and unlocked. Any process or user on the running host reads plaintext through normal file reads. It defends against physical theft and improper disposal, not against compromise of the running system. ## Layer 2: engine-level, Transparent Data Encryption The database encrypts pages as it writes them to its own files and decrypts them on read into the buffer pool. "Transparent" means no SQL changes: applications, indexes, plans and constraints are unaffected, because the engine works on plaintext pages in memory. Good implementations also cover redo/WAL, temp files and physical backups — verify which, because coverage varies. Compared with volume encryption it narrows the trust boundary: an OS user who copies datafiles gets ciphertext without the key, and engine-produced backups stay encrypted wherever they are shipped. It also allows per-database or per-tablespace keys, enabling crypto-shredding — destroying a key to render one scope of data unreadable. ## Layer 3: application or column-level encryption The application, or a client-side driver library, encrypts a value with a key the database server never holds, and stores ciphertext. Now a full database compromise — stolen credentials, SQL injection, a malicious administrator, a memory dump — yields ciphertext. The price is that the engine can no longer reason about the value: B-tree ordering is meaningless so range queries and ORDER BY break, pattern search breaks, and randomized encryption breaks equality lookups too. Values grow, types become binary, and rotating the key means re-encrypting every row. Teams therefore apply it to a handful of fields, not whole schemas. ## Choosing Most systems run volume encryption as a baseline (cheap, universal), add engine-level encryption where available (covers backups, gives key-scoped destruction), and reserve column-level encryption for a small set of high-sensitivity fields where the threat model genuinely includes an attacker with database access. The layers stack; they are not alternatives. ## The sentence that shows understanding "Volume and engine encryption protect the *media*; only application-level encryption protects against an attacker who is already authenticated to the database."
- If the volume is already encrypted, why bother with database-level encryption?Volume encryption stops protecting once the filesystem is mounted, and it does not travel with the data: a backup or dump copied off that host is plaintext unless separately protected. Engine-level encryption keeps datafiles and physical backups encrypted as artifacts wherever they go, and lets you scope keys per database or tenant so destroying a key destroys that data.
- Which layer would have prevented a leak caused by SQL injection?Only application or column-level encryption, because the injected query runs inside an authenticated session and every layer below returns plaintext to it. Volume and engine encryption defend stolen media, not an attacker speaking SQL. Even then, deterministic encryption still leaks equality patterns, so the field may be partially exposed.
A locked building (volume), a locked filing cabinet inside it (engine-level), and documents written in a cipher only head office can read (application-level). Once you are inside the building with a badge, only the cipher still protects the contents.
saying these in an interview costs you the question
- Claiming encryption at rest protects against SQL injection or stolen credentials
- Assuming volume encryption automatically covers backups shipped elsewhere
- Believing engine-level encryption requires query changes or breaks indexes
- Forgetting logs, temp files and snapshots are also data at rest
- Treating the layers as mutually exclusive alternatives