Page-Level Encryption: Nonces, MACs, and the Cost of Decrypting Every Read

Encrypting a database file as a whole is a different problem from encrypting its pages, and the difference is where most of the engineering lives. A whole-file cipher must be applied at open and removed at close, which means the plaintext exists in a second file or in memory for the entire session. A page cipher works on the unit the storage engine already reads and writes, so plaintext exists only in the page cache, and each page can be authenticated independently.

The cost of that choice is paid in three places: a nonce must be unique per page-write without storing per-page state, an authentication tag must be stored somewhere that does not corrupt the b-tree, and the key must be available in memory for every read, which is exactly the condition an attacker with code execution wants.

Why the page number is the nonce

A block cipher in chaining mode requires a fresh, unpredictable initialization vector for every message encrypted under the same key. Reusing an IV across two different plaintexts leaks their XOR, and in CBC mode it leaks the shared prefix. A database rewrites the same page number many times over its life, so “one IV per page number” would be catastrophic; a random IV per write is correct but must be stored alongside the ciphertext.

The scheme used by SQLCipher takes the second route: a random IV is generated for each page write and stored at the head of the page, followed by the ciphertext. The IV is not secret; its only requirement is uniqueness, and prepending it means the decryptor needs no external state to reverse the operation.

The important subtlety is what the IV is not used for. It does not authenticate anything by itself. A page encrypted with a valid key but a modified IV decrypts to garbage of the correct length, and the b-tree may still parse that garbage far enough to return wrong data rather than an error. That is the failure mode a MAC prevents.

The MAC is over the ciphertext

A message authentication code computed over the plaintext would have to be verified after decryption, which means the engine has already processed attacker-controlled bytes by the time it discovers they are wrong. Computing it over the IV and the ciphertext lets verification happen before decryption, so a forged page is rejected without the cipher ever seeing it.

Encrypt-then-MAC also gives the property that a page’s integrity is checked without the key used for confidentiality, in principle, though implementations usually derive both from the same master key through separate derivation.

The MAC must cover the page number, or pages can be swapped. A page written at number 42 and copied into the file at number 43 would authenticate correctly if the MAC covered only the bytes, and the b-tree would then read a cell from the wrong page. That is a structural corruption which is indistinguishable from a bug. Including the page number in the authenticated data closes that, and it costs nothing beyond four bytes of input.

The same reasoning applies to the file’s identity. Without it, a page from one database can be transplanted into another encrypted with the same key, and the receiving database will accept it. Binding the MAC to a per-database salt, stored in the file header, makes the transplant detectable.

Where the tag and IV fit in the page

The b-tree page format has a fixed usable size, computed as the page size minus a reserved region declared in the header. A page cipher consumes that reserved region: a fixed number of bytes at the head for the IV and a fixed number at the tail for the MAC. The result is that a 4096-byte page yields a usable size several tens of bytes smaller than an unencrypted one, and every capacity limit derived from the usable size shrinks accordingly.

That shrinkage propagates through the arithmetic described in the SQLite file layout: the local payload threshold, the overflow cutoff, the number of cells that fit on a page, and the fanout of interior pages all move. A table that fit in three levels of b-tree before encryption may need four after, and a row just under the overflow threshold may cross it. The effect is small per page and not always small per database, because the fanout change applies at every level.

There is a second-order effect on free space. Cells no longer align with the page’s natural boundaries, so the last cell on a page may leave more slack than before, and the fragmented-bytes accounting changes. A page that was 96 percent full before encryption may be 92 percent full after, which is a few percent more pages for the same data.

Key derivation and the startup cost

A password is not a key. Deriving one from the other uses a deliberately slow function so that guessing is expensive, and PBKDF2 as specified in RFC 8018 with HMAC as the pseudorandom function is the standard choice for this purpose. The cost is linear in the iteration count, and the iteration count is chosen to make a single guess cost something measurable on the hardware of the day.

On a phone that creates a visible startup delay. The derivation happens once per open, before any page can be read, and it cannot be parallelised across cores in the simple formulation because each iteration depends on the last. A database that opens in a few milliseconds unencrypted can take a noticeable fraction of a second encrypted, and on a low-end device more.

The default iteration count has been raised substantially across major format versions as hardware got faster, which means a database created by an older version opens faster than one created by a newer version, and an application that hard-codes an expectation about open latency will be wrong on some fraction of devices. A database whose count was set explicitly at creation keeps that cost for its lifetime.

The derived key is then used to derive two subkeys, one for the cipher, one for the MAC, typically by running the derivation function over the master key with fixed distinct salts. This is why a single password yields independent keys rather than one key used for both purposes, which would be a protocol design error.

The key cache is the actual boundary

Once derived, the key sits in memory for the life of the connection. Every page read decrypts with it; every page write encrypts with it. The key is not re-derived per page because that would be unusably slow.

That memory is the target. An attacker with the ability to read the process’s memory (through a debugger, a core dump, a compromised runtime, or a device where the OS’s own protections have failed) obtains the key and with it the entire database, regardless of how strong the cipher is. The encryption protects the file at rest, against a stolen device or an extracted backup; it does not protect against code running in the same process.

Practical mitigations reduce the window rather than closing it. The connection can be closed when the app goes to the background and reopened on return, which pays the derivation cost on every foreground transition and keeps the key absent during the periods when the device is most likely to be out of the user’s hands. The key can be held in memory that is locked against swapping, where the platform offers such an API, so that it does not reach the swap file. Neither measure changes the fact that the key must exist in plaintext for the cipher to use it.

A stronger arrangement wraps the database key with a key held by the platform’s secure hardware, so that the wrapping key never enters the app’s address space. That moves the boundary rather than removing it: the app still holds the unwrapped database key while it is open, but an attacker who copies the file and the wrapped key from a backup cannot unwrap it without the hardware. This is the arrangement that makes a stolen backup useless, and it is the reason file-level encryption by the platform and page-level encryption by the database are complementary rather than redundant.

What encryption does not cover

Three artefacts escape the page cipher unless they are handled explicitly.

The write-ahead log contains page images, so its frames must be encrypted with the same scheme, and its frame headers must be authenticated or they can be altered to point at the wrong page. An implementation that encrypts the main file and leaves the log in plaintext has published the database’s recent history.

The rollback journal contains original page images, which are plaintext versions of exactly the pages the cipher was protecting. It must be encrypted too, or a crash-recovery file becomes a leak.

Temporary files used for sorting large queries spill row data. An in-memory sort that exceeds its limit writes to a temporary store, and unless that store is also encrypted, the spill contains plaintext rows. The setting that controls the temporary store’s behaviour is the one to check, because the default may be a file in the same directory.

-- Keep temporary storage out of the file system, or encrypted with the same key.
PRAGMA temp_store = MEMORY;
PRAGMA cipher_memory_security = ON;   -- wipes key material on free, at a cost

The second pragma is not free: wiping memory on every free defeats some allocator optimisations and can slow page churn measurably. It is the right default for a database whose key is the thing being protected, and the wrong default for one processing large analytical queries.

Rotating a key without rewriting the world

Changing the password requires re-encrypting every page, because the derived key changes and the existing ciphertext was produced under the old one. The operation is a full rewrite of the database, so it needs free space equal to the database size unless it is done page by page in place with the original preserved until the rewrite completes.

Doing it in place is possible because each page is independently encrypted: the engine can read a page with the old key, write it with the new key at the same offset, and continue. The hazard is a crash in the middle, which leaves a database half under each key. The safe procedure writes to a new file and swaps it in with the atomic replacement sequence, paying the double space cost. On a device where the database is the largest thing in the container, that cost is often the reason the rotation is deferred until it is forced.

The final consideration is what happens when the key is wrong. A page that fails its MAC can mean a wrong key, a corrupted page, or a torn write from a crash during a transaction. Distinguishing them requires the header’s own authentication to be checked first: if the header verifies, the key is right and the page is damaged; if it does not, the key is wrong. Implementations that report a single generic error for both cases make a support problem out of a diagnosis that one comparison would settle.