The previous post protected data in transit. But TLS ends at the server, and from there data is written to the database, log files, backups, the cloud provider's disks. This post is about data at rest.
The most important question here isn't "which algorithm" (the answer is almost always AES-256-GCM), but two others: who are you defending against, and where does the key live. Encryption with the key stored next to the data is like locking the door and hanging the key on the handle.
Roadmap: three layers of encryption and what each stops, envelope encryption and a KMS, key rotation, an experiment encrypting single columns in SQLite (cost measured), lookups on encrypted data with a blind index, the limits of encryption at rest, backups and deleting data, then what this site does. The experiments run with Python 3.12, SQLite and the cryptography library on a real machine.
Three layers, three attackers
| Layer | Examples | Stops | Doesn't stop |
|---|---|---|---|
| Disk / volume | BitLocker, FileVault, LUKS, encrypted EBS volumes | A lost laptop, an old drive thrown away or sold, a data-centre worker pulling a drive | Anyone who can log into the running machine: the disk is already unlocked |
| Database (TDE) | Transparent Data Encryption in SQL Server, Oracle, MySQL; SQLCipher for SQLite | Someone who gets the database file or a backup without the key | Anyone who can query the database: it decrypts for them |
| Application / column | The app encrypts ssn, email fields before writing |
Database admins, backups, analytics tools, slow-query logs, the database provider | Bugs in the application itself: the app holds the key and decrypts for them |
Each layer up means fewer people see plaintext, but also more work: the disk layer is nearly free and invisible, while the application layer costs you search, sorting and indexing.
The common real-world setup: disk encryption everywhere (most clouds turn it on by default), plus application-level encryption for a few very sensitive columns such as national id numbers, card numbers, third-party tokens or medical notes. Not the whole database at the application level.
Where the key lives: envelope encryption
The application needs the key to decrypt. If the key sits in a config file on the same server, whoever takes the server has both. If it sits in the database, worse still.
The standard answer is envelope encryption, with two tiers of keys:
- DEK (data encryption key): the key that encrypts the actual data. There can be many DEKs, say one per table, per customer or per file. The DEK is stored right next to the data, but wrapped (encrypted) by the KEK.
- KEK (key encryption key), or master key: only used to wrap and unwrap DEKs. The KEK never leaves the KMS, a key management service like AWS KMS, Google Cloud KMS, HashiCorp Vault, or a hardware security module (HSM).
Startup: app ──"unwrap this DEK"──► KMS (holds the KEK, logs, checks permissions) ──plain DEK──► app (kept in RAM)
Running: app encrypts / decrypts data with the DEK, without calling the KMS again
Why two tiers instead of encrypting directly with the KMS?
- Speed: each KMS call is a network request taking a few milliseconds. Encrypting with a DEK in RAM takes a few microseconds (measured below).
- Control: the KMS logs every unwrap, limits which machines can call it, and can revoke access. Someone who steals a copy of the database along with all the wrapped DEKs still can't do anything without calling the KMS.
- Cheap rotation: the next section.
Rotating keys
Keys should be replaced periodically, and must be replaced when a leak is suspected. With envelope encryption there are two levels:
| Rotate what | Do what | Measured with 20,000 rows |
|---|---|---|
| KEK | Unwrap the DEK with the old KEK, wrap it with the new one. The data doesn't change | 3 µs (rewrap 1 DEK) |
| DEK | Decrypt and re-encrypt every row | 45 ms |
Rotating the KEK is almost free, however large the data. Rotating a DEK costs in proportion to the data, and with billions of rows it has to run gradually in the background. So each ciphertext should record the key version used, so old and new data can both be read during the transition. It's the same idea as the backward-compatible migration rule for zero-downtime deploys: the old and the new must both work while you switch.
Experiment: encrypting the email column in SQLite
A table of 20,000 users. The plain version stores emails in clear text with a UNIQUE index. The encrypted version stores emails through AES-256-GCM, with the DEK wrapped by a KEK standing in for a KMS:
def encrypt_email(row_id: int, email: str) -> bytes:
nonce = os.urandom(12)
aad = f"user:{row_id}:email".encode() # bind the ciphertext to the right row and column
return nonce + dek.encrypt(nonce, email.encode(), aad)
AAD (additional authenticated data) is a small but important detail. Without it, anyone who can modify the database could copy person A's encrypted email into person B's row, and the application would happily decrypt A's email in B's row. With AAD containing the row id, that fails:
ciphertext moved to another row -> InvalidTag (AAD binds row id)
The cost:
| Unencrypted | Encrypted column | |
|---|---|---|
| Write 20,000 rows | 8.9 ms | 53.2 ms (+2.2 µs per row) |
| Look up by email | 0.59 µs | 2.10 µs (blind-index hash + query + decrypt) |
| Email size | 17 B | 45 B (+12 B nonce, +16 B tag) + 16 B blind index |
About 2 µs more per row: negligible next to a web request of tens of milliseconds. The real cost isn't CPU, it's what you lose, next section.
Lookups on encrypted data: the blind index
Each GCM ciphertext has a random nonce, so encrypting the same email twice gives two different ciphertexts. That's a good thing, but it also means WHERE email = ? no longer works.
A blind index solves exact lookups: store an extra column with an HMAC (post 2) of the value, under a separate key from the DEK:
def blind_index(email: str) -> bytes:
return hmac.new(index_key, email.lower().encode(), hashlib.sha256).digest()[:16]
# lookup: WHERE email_idx = blind_index(input), then decrypt the email column
HMAC is deterministic (the same email gives the same index), so it can carry a UNIQUE constraint and be searched by equality. Without index_key nobody can compute the index for a guessed email. But some things are still lost:
| Operation | Still possible? |
|---|---|
WHERE email = ? |
Yes, via the blind index |
UNIQUE |
Yes, on the blind index |
LIKE '%example%' |
No (it counted 0 rows in the experiment) |
ORDER BY email, greater/less-than comparisons |
No |
| Distribution statistics | Partly leaked: two rows with the same blind index have the same value |
The last row is a real leak. For a column with few distinct values, such as gender or province, a blind index reveals almost the whole distribution, so don't use it for that kind of column. There are more advanced techniques like order-preserving or homomorphic encryption, but each trades away more security or is very slow. The practical rule: only encrypt at the application level the columns that don't need complex search.
The limit: the application decrypts for the attacker
This is the most misunderstood point. If the application has an SQL injection bug or an API that returns other people's data, column encryption doesn't help at all. The attacker doesn't read the database file. They ask the application to read for them, and the application has the DEK in RAM and decrypts as it would for any other request.
Encryption at rest stops people who go around the application: stolen backups, curious database admins, discarded disks, a dump accidentally pushed somewhere public. Stopping the application's own bugs is the job of parameterised queries, per-object permission checks, and limiting what each API returns (post 9).
Backups, logs, and deleting data
- Backups are often the biggest leak: they live long, get copied to many places, and are less guarded than the main database. Encrypt backups with a different key, kept somewhere else. And test restores regularly: an encrypted backup whose key is lost is no backup at all.
- Logs are where sensitive data slips in most often: request bodies, SQL statements with values, error messages. Masking data before logging matters as much as encrypting the database.
- Deleting by destroying the key (crypto-shredding): if each customer's data has its own DEK, deleting the DEK makes that data unreadable everywhere, including in old backups. It's a practical way to honour a right to erasure (as in GDPR) when backups can't be edited.
What this site does
viet.sarl holds little sensitive data: emails, display names, Argon2id hashes of passwords (not passwords, post 3), and links to Google accounts. No card numbers or national ids. The site encrypts no columns at the application level, and with this little data that's a reasonable trade-off. The things that matter more here are:
- Secrets (
JWT_SECRET, the Google OAuth keys, the SMTP password) live in.env.productionon the server and never go into git (.gitignoreblocks them). - The application refuses to start in production if
JWT_SECRETis shorter than 32 characters or is still the default development secret. - SQLite backups must be treated like the main database: they contain emails and password hashes.
If the site ever needs to store something truly sensitive, such as a user's API access token for another service, that's when to use exactly the pattern from the experiment: AES-GCM, AAD bound to the row, and a DEK wrapped by a KEK that lives off the server.
Summary
- Three layers: disk (stops lost hardware), database (stops lost files and backups), application (stops even database admins). The higher the layer, the fewer people see the data, and the more features you lose.
- The key question is where the key lives. Envelope encryption: a DEK encrypts the data, a KEK in a KMS wraps the DEK.
- Rotating the KEK: 3 µs (rewrap 1 DEK). Rotating the DEK: re-encrypt every row (45 ms for 20,000 rows). Record the key version in the ciphertext.
- Encrypting one column costs about 2.2 µs per row. AAD stops ciphertexts being copied to another row.
- A blind index (HMAC with its own key) gives exact lookups and
UNIQUE, but losesLIKEand sorting, and leaks frequencies. - Encryption at rest doesn't stop application bugs, because the application decrypts for the attacker. Backups and logs are the most common leaks.
The next post goes back to the application: how "Sign in with Google" works. OAuth 2.0 and OpenID Connect, the authorization code flow with PKCE following this site's real code, and the role of state.