Encrypted PGlite (#553): SQLCipher design adapted — spec v1

Research and design only. Sources are cited inline. [unverified] marks claims I did not check against code or docs.

Inputs read: issue #553 (no comments yet), #1017 (native key), PR #111 (Tauri shell), Journal's src/lib/db/pglite.ts, pglite-worker.ts, pgliteWorkerHost.ts, PGlite 0.5.10's shipped dist/ (minified; the source maps have no sourcesContent), SQLCipher src/sqlcipher.c (master, the codec now lives there and crypto_impl.c is gone), the SQLCipher design page, the PostgreSQL TDE wiki, and Percona's pg_tde architecture page.

---

1. SQLCipher, precisely

Aspect	What SQLCipher does	Source
What is encrypted	The whole main database file, plus rollback journal, WAL and statement journal pages, all with the database key. Other temp files are not, so apps must keep temp storage in memory.	design page
Unit	One SQLite page, default 4096 bytes (cipher_page_size). Each page is encrypted on its own at the pager/VFS boundary. The page cache in RAM stays plaintext.	design page
Cipher	AES-256-CBC.	design page
IV	A fresh random IV on every page write, stored in the page's reserved bytes at the end of the page. On encrypt it fills the whole reserve with random bytes, then writes the IV and HMAC over the front of it.	sqlcipher_page_cipher, sqlcipher.c ~L1749
Reserve size	IV (16) + HMAC (64 for SHA512) = 80, rounded up to the cipher block size. SQLite's own "reserved bytes per page" field shrinks the usable page, so file offsets don't change. A 4096-byte page holds 4016 bytes of payload.	sqlcipher_codec_ctx_reserve_setup ~L1218
MAC	HMAC-SHA512 over ciphertext ‖ IV ‖ pgno (4-byte page number, little-endian by default). Verified before decryption, compared in constant time. The source comment says this prevents "tampering with the ciphertext, manipulation of the IV, or resequencing otherwise valid pages out of order".	sqlcipher_page_hmac ~L1708
MAC key	A separate key: PBKDF2 with 2 iterations (FAST_PBKDF2_ITER) over the encryption key, using the salt XOR 0x3a (HMAC_SALT_MASK).	design page; sqlcipher.c L135, L144
KDF	PBKDF2-HMAC-SHA512, 256,000 iterations (PBKDF2_ITER, PRAGMA kdf_iter).	sqlcipher.c L111
Raw key	x'<64 hex>' uses the key directly and skips the KDF. x'<96 hex>' gives key plus salt. This is the mode for keychain-held random keys.	comment above sqlcipher_cipher_ctx_key_derive
Salt / header	16 random salt bytes take the first 16 bytes of the file (FILE_HEADER_SZ). The rest of page 1 is encrypted, so the file looks random from byte 0. cipher_plaintext_header_size (e.g. 32, for iOS shared-container WAL detection) keeps the SQLite header in plaintext and moves the salt out to the app.	sqlcipher.c L93, ~L2106, pragma ~L3106
Key check	No separate key-check record. A wrong key makes page 1's HMAC fail, and the open reports "file is not a database".	behaviour of sqlcipher_page_cipher decrypt path
Rekey	sqlite3_rekey sets a new write-context key, opens one write transaction, reads and rewrites every page, then commits (or rolls back). Crash safety comes from SQLite's own journal, which is written with the old key.	sqlite3_rekey_v2 ~L3880, comment ~L3482
Integrity check	PRAGMA cipher_integrity_check checks every page's HMAC.	~L2085
Tamper limits	It catches modified pages, swapped IVs and pages moved within a file. It does not catch replay of an older valid version of the same page, or rollback of the whole file: the HMAC has no version or counter. The design page says nothing about rollback.	inferred from the HMAC inputs
Memory	Key buffers are mlocked and wiped when freed (sqlcipher_shield, sqlcipher_memset).	design page

The parts that carry over to PGlite: (a) encrypt at the boundary between the engine and storage, so the engine is untouched; (b) a fixed unit with a fresh random IV on every write; (c) a MAC that binds the unit's position; (d) a separate MAC key, or an AEAD; (e) a raw-key mode with no KDF; (f) rekey as one atomic rewrite.

2. What Postgres writes, and what the TDE projects learned

• Relation files (heap, index, FSM, VM) are made of 8 KB blocks (BLCKSZ) and split into 1 GB segment files (base/<dboid>/<relfilenode>[.N]). md.c reads and writes whole blocks at blkno * BLCKSZ. (storage page layout, file layout)

• WAL lives in 16 MB segments (pg_wal/) made of 8 KB pages (XLOG_BLCKSZ). A new segment is pre-filled with zeros. Old segments are recycled by rename. The last, partial WAL page is rewritten many times as records are appended. (WAL internals)

• Small files: global/pg_control (8 KB written, ~512 B of content), SLRUs (pg_xact, pg_multixact, pg_subtrans, 8 KB pages), pg_filenode.map, pg_internal.init, pg_stat/*, PG_VERSION, postgresql*.conf, postmaster.pid.

• The Postgres page has no reserved bytes for an IV or tag. That's why the TDE wiki design (AES-XTS for data, CTR for WAL) and Cybertec's patch (AES-CTR per the issue; the exact mode per version is [unverified]) both drop integrity. The wiki says outright that it "does not guarantee data integrity, i.e., protection against those with write access". Both derive IVs from LSN, block number and segment number, and the wiki lists the trouble cases: hint-bit writes with no new LSN, GiST pages with fixed LSNs, unlogged relations. (PG wiki TDE)

• Percona pg_tde uses AES-CBC for data and AES-CTR for WAL, with internal per-relation keys wrapped by a principal key (AES-GCM) from a key provider. System catalogs and general metadata are not covered, and the full feature set needs Percona's patched server. (pg_tde architecture)

What this means for us: working below Postgres, in the filesystem, solves the problem that pushed the in-engine designs toward deterministic IVs and no MAC. We control the physical layout, so each block can grow by an IV and tag, as SQLCipher's reserve does, with no engine change. Coverage is also total: every file under PGDATA is encrypted, including pg_control, pg_xact and catalogs, which the wiki and pg_tde leave out. Porting pg_tde is rejected. It would need a custom PGlite WASM build with Percona's SMGR patches and OpenSSL in WASM, it covers fewer files, and it has no integrity. The issue already defaults to the filesystem layer, and nothing here argues against that.

3. How PGlite sees I/O (verified in 0.5.10 dist/)

• Filesystem interface: init(pg, emscriptenOpts) → {emscriptenOpts}, syncToFs(relaxed?), initialSyncFs(), dumpTar(), closeFs() (pglite-*.d.ts L329).

• EmscriptenBuiltinFilesystem (Ye in chunk-6QROZKQ5.js) does nothing by itself. IdbFs extends it: its preRun mounts Emscripten IDBFS at /pglite/<dataDir> and symlinks PGDATA to it. initialSyncFs and syncToFs both call FS.syncfs.

• Emscripten IDBFS is MEMFS plus a reconciler. syncfs builds local and remote sets keyed by path with timestamps, then writes the whole node.contents of each changed file as one IndexedDB value. This build also wraps MEMFS stream_ops.write/close and node_ops.rmdir/symlink/unlink/rename to call queuePersist (review S4: these wrappers are inactive in PGlite). So the whole database lives in worker RAM in plaintext, and one dirty WAL page rewrites the 16 MB segment. That matches the known cost of PGlite writes.

• BaseFilesystem (Ge, used by NodeFS and OpfsAhpFS) mounts a custom Emscripten FS that forwards to synchronous abstract methods: read(fd, buf, off, len, position) and write(...) return byte counts, not promises (d.ts ~L372).

• PGlite calls fs.syncToFs after each execProtocolRaw (default syncToFs: true), under a mutex. With relaxedDurability it doesn't await the flush (index.js: s(this,o_)?t():await t()), so queries can run and mutate MEMFS while a flush is in flight.

The hook decision

There are two places to put the SQLCipher boundary:

	A. Encrypt on each pread/pwrite (BaseFilesystem pass-through)	B. MEMFS plus dirty-block tracking, encrypted flush in syncToFs
SQLCipher fidelity	Closest: every engine write goes straight to ciphertext storage	Same unit and format. Encryption happens at the PGlite durability point, not at each write()
Crypto API	Must be synchronous, so no WebCrypto. Needs pure-JS or WASM AES-GCM (e.g. @noble/ciphers) [speed unverified]	WebCrypto AES-GCM, async and hardware-backed
Storage	Must be synchronous: only OPFS sync access handles qualify. IndexedDB and Tauri IPC are async. OPFS breaks on Safari and is shaky in WKWebView [unverified]	Any async store: IndexedDB, or Tauri files through IPC
RAM	Small (page cache only)	Whole DB in worker RAM, same as today's IdbFs
Atomicity across files	Needs its own journal; Postgres fsync order must reach real storage	One atomic batch per sync: a snapshot of a crashed-but-consistent process

Recommendation: B. A would need sync storage, which only OPFS provides, plus sync JS crypto, in exactly the webviews where OPFS is unreliable. B keeps WebCrypto, works with both IndexedDB and Tauri backends, and puts no more plaintext in RAM than today (SQLCipher's page cache is plaintext too). Implementation: subclass EmscriptenBuiltinFilesystem. In preRun, mount MEMFS at PGDATA and wrap its ops the way IDBFS does:

• stream_ops.write(stream, buf, off, len, pos): mark blocks floor(pos/8192)…floor((pos+len-1)/8192) dirty on that inode.

• node_ops.setattr with a size (truncate/ftruncate), stream_ops.allocate, stream_ops.msync (mmap writeback; whether PG in PGlite uses mmap at all is a spike item [unverified]). (Review B3: allocate doesn't exist in this build, and writes past EOF need extra tracking.)

• node_ops.mknod/mkdir/symlink/rename/unlink/rmdir: mark metadata dirty.

4. On-disk format (format v1)

The logical unit is an 8 KB block of each file, at offset i * 8192. That is the same unit as BLCKSZ and XLOG_BLCKSZ, so Postgres's aligned writes never need a read-modify-write. A small file such as PG_VERSION gets one short final block. This replaces the issue's 64 KB chunk: one dirty WAL page then costs 8 KB, not 64 KB. Measure both in the spike; IndexedDB per-record overhead may favour larger chunks.

Storage records, behind the backend interface getAll / putBatch / deleteBatch in one atomic batch:

Record	Key (plaintext)	Value
Header	"hdr"	{magic:"PGLENC", v:1, cipher:"AES-256-GCM", block:8192, kdf:"raw"|{pbkdf2-sha256, iter, salt16}, wrappedDEK, keyCheck, gen}. This is the SQLCipher salt/header equivalent, and the only plaintext metadata.
Inode	["i", fileId]	nonce12 ‖ GCM(DEK, {path, type, mode, size, mtime, symlinkTarget}) ‖ tag16, AAD = "i" ‖ fileId ‖ gen
Block	["b", fileId, blockIdx]	nonce12 ‖ GCM(DEK, block) ‖ tag16, AAD = "b" ‖ fileId ‖ blockIdx

• fileId is a random 128-bit id assigned at create, like an inode number. Paths sit only inside encrypted inode records, so rename (WAL recycling) rewrites one inode record and no blocks, and swapping blocks between files or positions fails the tag check. AAD plays the part of SQLCipher's pgno in the HMAC.

• Overhead is 28 B per block, stored next to the block, so no offset translation is needed in IndexedDB. The Tauri file backend would use physical block = 8192 + 28 (or 8192 + 32, aligned) at header + i * stride. That is SQLCipher's reserve moved outside the page.

• Cipher: AES-256-GCM with a random 96-bit nonce per write, not SQLCipher's CBC + HMAC-SHA512:

  • GCM is the AEAD WebCrypto provides: one pass, hardware AES, a 28 B overhead instead of 80, and no separate MAC key to derive.

  • Nonce reuse is the GCM hazard: a repeated (key, nonce) leaks the XOR of plaintexts and allows tag forgery. Deterministic nonces are unsafe here for three reasons. The tail WAL page is rewritten constantly. A snapshot that crashes and rolls back would reissue counters. A Tauri backend may have written uncommitted ciphertext to disk that an attacker can still read. With random nonces, NIST SP 800-38D caps one key at about 2^32 encryptions. A heavy user at 100k block writes a day reaches about 2^28 in ten years, and a DEK rotation resets the count. That is fine, but record the write counter in the header and force a rotation at 2^30 as a safety rail.

  • CBC + HMAC (SQLCipher-exact) is the fallback if a webview lacks GCM; all target webviews support it [unverified for WebKitGTK].

• Zero blocks: WAL pre-allocation writes 16 MB of zeros, which is 2,048 block records once per new segment. Encrypt them like any other block in v1 (no sparse holes, which would leak the WAL fill level). The benchmark will show whether segment switches hurt.

• Leaks, which SQLCipher has too (file size): the number of files, blocks per file (sizes to 8 KB), and which blocks change on each sync and when. File names, sizes to the byte, modes and contents are hidden. (Review S3: short final blocks actually leak exact sizes; pad every block.)

Sync algorithm (crash consistency)

1. Synchronous snapshot at the start of syncToFs: copy the plaintext of every dirty block and every dirty inode, then clear the dirty sets. JS is single-threaded and the engine's execProtocolRawSync runs to completion, so this copy is a consistent cut even under relaxedDurability, where the next query may run during step 2.

2. Async: encrypt each block with WebCrypto, then build one batch: block puts, deletes for truncated or unlinked blocks, inode puts and deletes, and the header with gen + 1.

3. Commit as one IndexedDB readwrite transaction. Request durability: "strict" where supported. On failure, merge the snapshot's ids back into the dirty sets. (Review S8: default to relaxed; use strict only on hide, close and migration.)

4. Flushes stay serialized, as TimedIdbFs already does.

Each committed state is a crash image of an idle Postgres, and Postgres replays WAL on the next open.

Open, wrong key, tamper

• initialSyncFs: read the header, unwrap the DEK (a GCM tag failure throws WrongKeyError), and decrypt keyCheck. Only then touch MEMFS. Decrypt all inodes and blocks. Any tag failure, a missing block below size, or an extra block throws CorruptDatabaseError, and nothing is half-loaded.

• Detected: modified, swapped or truncated blocks and inodes. Not detected (same as SQLCipher): replay of an older valid block, or rollback of the whole store to an older snapshot by someone with write access to the profile. (Review S1: whole-file deletion and inode replay aren't detected either.)

Keys and rekey

• Two tiers, borrowed from pg_tde's principal → internal keys. The caller passes a KEK CryptoKey: native gets a random 256-bit raw key from the OS keychain (#1017), so there's no KDF, as with SQLCipher's raw key. The package generates a random DEK, wraps it under the KEK with AES-GCM, and stores it in the header.

• changeKey(newKEK): rewrap the DEK and write one header record. It's instant and atomic.

• rotateDataKey(), SQLCipher-style rekey: decrypt and re-encrypt every block and inode under a new DEK. Make it resumable.

• Hand-off: CryptoKey is structured-cloneable, and pgliteWorkerHost.ts receives options over postMessage, so the key can travel in the init options.

Migrating an existing plaintext DB

Do it at the filesystem level, not through pg_dump. Open idb://journal-db-pg18 as raw IDBFS records, write the encrypted store, open it with PGlite and verify, and only then delete the old IndexedDB database. Caveat: deleting an IndexedDB database doesn't securely erase it, so be honest about this in the UI. dumpTar keeps working, so exports are plaintext by design. (Review B2: this needs an explicit open/migration state machine.)

5. Where native persistence lives

Recommended for v1: IndexedDB in the webview. Atomic transactions give all-or-nothing syncs for free, and it runs entirely inside the worker. Tauri files come later behind the same backend interface: real fsync and backup control, but Tauri invoke isn't available inside Web Workers [unverified for v2], and atomicity would have to be built in Rust. (Review S10: make IndexedDB vs a Rust-side store an owner decision now.)

6. Questions that need the owner (v1; see the review for the revised list)

1. Threat model: lost or stolen device, or also an offline disk image? Is whole-store rollback detection in scope? (Recommend no.)

2. Two-tier keys: is a cheap KEK changeKey enough, or must it re-encrypt everything?

3. Block size: let the benchmark decide (8 vs 32 vs 64 KB)?

4. Acceptable leaks: file count, sizes to the block, write timing.

5. Plaintext leftovers: (the review reframes this as a blocker; owner direction: credentials to the keychain, the React Query cache into PGlite, wipe leftovers after migration).

6. App lock: close PGlite and drop the decrypted DB from RAM, or only gate the UI?

7. Phased plan (v1)

Phase	Work	Size
0. Spike (2–3 days)	Minimal EncryptedMemFs, an FS-op log of Postgres, open and sync timings against IdbFs at 10 and 100 MB, webview checks. Gate: within 2×.	S–M
1. Package (#553)	Format v1, header/KEK/DEK, backend interface, changeKey, rotateDataKey, tests, benchmark. About 700 lines of source and 700 of tests.	L
2. Journal integration (#1017)	Keychain command, worker hand-off, migration with verification, recovery, Settings.	L
3. Optional	Tauri file backend if IndexedDB misses the target.	M