Handling Nonce and MAC Storage in SQLite VFS Shim for Transparent Encryption

Structural Constraints of SQLite File Encryption Through VFS Layer

Issue Overview

Implementing transparent database encryption through a custom VFS shim in SQLite requires addressing two critical cryptographic metadata storage challenges: nonce (number used once) management and message authentication code (MAC) placement. The core problem arises from SQLite’s expectation of direct file access control, where the database engine assumes full ownership of file layout and I/O patterns. When encrypting data at rest, cryptographic primitives like AES-GCM demand per-page nonces to prevent IV reuse vulnerabilities and MACs to verify integrity. However, SQLite operates on fixed-size pages (default 4096 bytes) with strict assumptions about file structure, making it impossible to append or prepend metadata without violating these expectations.

Three key technical constraints emerge:

1. Nonce storage requirements: Modern authenticated encryption modes require unique nonces per encryption operation. For multi-page databases, this necessitates storing thousands of nonces with guaranteed uniqueness and synchronization to their corresponding encrypted pages.
2. MAC storage overhead: Each encrypted page requires an integrity tag (e.g., 16 bytes for AES-GCM), creating cumulative storage overhead that must coexist with SQLite’s page management system.
3. File boundary conflicts: SQLite’s WAL (Write-Ahead Log) and rollback journal mechanisms create auxiliary files with different access patterns than the main database, requiring separate cryptographic handling while maintaining ACID guarantees.

The fundamental architectural conflict lies in SQLite’s page-oriented I/O model versus cryptographic primitive requirements for per-page metadata. Directly appending MACs to the file (as proposed in the original discussion) fails because SQLite tracks exact file sizes through the xFileSize method and will overwrite any appended data during normal operation. Prepending metadata is equally problematic due to SQLite’s reliance on fixed header positions for version compatibility.

Cryptographic Metadata Management Failure Modes

1. Page Size Mismatch with Crypto Requirements
   SQLite databases configure page sizes through the header’s bytes 16-17, typically set during database creation. Cryptographic metadata (nonce + MAC) consumes additional bytes per page that SQLite doesn’t account for. For example, AES-256-GCM requires 12-byte nonces and 16-byte MACs, adding 28 bytes per 4096-byte page. Attempting to store these externally creates alignment issues when SQLite calculates page offsets, leading to decryption failures during read operations.

2. VFS Shim Layer Limitations
   While the VFS interface allows intercepting file operations, it operates at the byte stream level without inherent awareness of SQLite’s page structure. A naive implementation that appends MACs to the file end will break SQLite’s internal size tracking:

   // Problematic MAC appending pseudocode
   void xWrite(sqlite3_file* file, const void* buf, int amt, sqlite3_int64 offset) {
     encrypt(buf, amt, &ciphertext, &nonce, &mac); 
     real_vfs->xWrite(file->real, ciphertext, amt, offset);
     real_vfs->xWrite(file->real, mac, MAC_SIZE, file_size + offset + amt); // Corrupts file structure
   }
   

   This approach causes file size explosions and misaligned writes when SQLite truncates files during checkpointing or vacuum operations.

3. WAL and Journal File Complexity
   Write-Ahead Logging uses variable-sized frames (default 4096 + 24 bytes) containing page data and commit records. Encrypting WAL files requires either:

   • Frame-level encryption with per-frame nonces/MACs
   • Whole-file encryption that breaks SQLite’s WAL replay mechanism
     Neither approach integrates cleanly with SQLite’s crash recovery logic, risking database corruption if cryptographic metadata becomes desynchronized from actual data pages.

4. Key Management Entanglement
   Transparent encryption solutions must handle key derivation, rotation, and initialization vector management without SQLite API modifications. Common workarounds like PRAGMA statements for key passing create race conditions where SQLite might access encrypted pages before key initialization completes.

Implementation Strategies for Metadata-Aware Encryption

1. Page Header Reservation Technique
Leverage SQLite’s built-in header space reservation mechanism documented in byte 20 of the database header. This byte specifies the number of reserved bytes per page (1-255), originally intended for extensions like encryption. For AES-GCM requirements:

// Database creation with reserved bytes
sqlite3_open(":memory:", &db);
sqlite3_exec(db, "PRAGMA page_size=4096", NULL, NULL, NULL);
sqlite3_exec(db, "PRAGMA reserve_percent=100", NULL, NULL, NULL); // 100% reserved space
// Write initial schema...
// Then extract reserved bytes from header:
uint8_t reserved_bytes = 28; // 12 nonce + 16 MAC
sqlite3_file_control(db, "main", SQLITE_FCNTL_RESERVE_BYTES, &reserved_bytes);

Implementation steps:

1. Intercept xFileControl with SQLITE_FCNTL_RESERVE_BYTES to set reserved space
2. Partition reserved area into nonce and MAC sections
3. Modify xWrite to encrypt data + store nonce/MAC in reserved slots
4. Adjust xRead to verify MAC and decrypt using stored nonce

Critical considerations:

• Reserved bytes reduce usable page space (4096 – 28 = 4068 bytes/data page)
• Requires custom page format:

  | Page Data (4068B) | Nonce (12B) | MAC (16B) |
  

• Must handle reserved bytes in WAL frames and journals

2. External Metadata File Strategy
Maintain cryptographic parameters in a sidecar file indexed by page number:

struct MetadataFile {
  int64_t page_number;
  uint8_t nonce[12];
  uint8_t mac[16];
};

VFS shim implementation outline:

• Intercept xOpen to create/metadata file alongside DB
• Map database page offsets to metadata file records
• Use file locking to synchronize metadata updates
• Implement crash recovery through metadata/journal reconciliation

Advantages:

• Keeps SQLite page structure intact
• Allows separate encryption for WAL/journal files
• Simplifies key rotation (re-encrypt metadata separately)

Disadvantages:

• 2x I/O operations (data + metadata writes)
• Complex cross-file synchronization
• Breaks standard backup tools (must copy both files)

3. SQLite3MultipleCiphers Integration
Adapt the existing SQLite3MultipleCiphers extension instead of building a VFS shim from scratch:

// Initialize with custom cipher
sqlite3mc_config_cipher(
  db, 
  "aes-256-gcm", 
  SQLITE3MC_CIPHER_CONFIG_KEY_DERIVATION, 
  my_hkdf_callback
);

// Register page encryption handlers
sqlite3mc_config_cipher(
  db,
  "aes-256-gcm",
  SQLITE3MC_CIPHER_CONFIG_DECRYPT_PAGE,
  my_decrypt_function
);

Critical implementation details:

• Use SQLITE3MC_OMIT_BUILTIN_CIPHERS to exclude unwanted algorithms
• Implement GenerateKey_t for key derivation with HKDF-SHA256
• Handle GetSalt_t by storing salt in reserved header bytes
• Configure WAL encryption through SQLITE_OPEN_WAL flag checks

4. Hybrid VFS/Cipher Implementation
Combine reserved bytes for nonce storage with end-of-file MAC storage:

1. Reserve 12 bytes/page via header for nonces
2. Append cumulative MACs at file end using xTruncate interception:

// In xTruncate implementation
if(new_size < current_size) {
  // Preserve MAC region during truncation
  read_existing_macs(file, &macs);
  real_vfs->xTruncate(file->real, new_size);
  write_macs(file, macs); 
}

Implementation challenges:

• Maintain MAC index that maps file offsets to MAC values
• Handle concurrent write/truncate operations
• Protect MAC region with checksums to detect tampering

5. Adiantum/XTS-AES Length-Preserving Encryption
For projects accepting reduced security guarantees in exchange for implementation simplicity:

// XTS-AES implementation sketch
void encrypt_page(
  const uint8_t *key,
  uint64_t page_number,
  const uint8_t *plaintext,
  uint8_t *ciphertext
) {
  uint8_t tweak[16];
  memset(tweak, 0, 16);
  memcpy(tweak, &page_number, sizeof(page_number));
  aes_xts_encrypt(key, tweak, plaintext, ciphertext, PAGE_SIZE);
}

Security tradeoffs:

• No per-page nonces (tweak derived from page number)
• No authentication tags (risk of undetected tampering)
• Requires external integrity verification mechanisms

Operational Verification Checklist

1. Page Alignment Validation
   Confirm through test cases that all xRead/xWrite operations occur at page-aligned offsets with exact page sizes. Use SQLite test wrappers:

// Test hook for I/O alignment
sqlite3_vfs_register(
  test_vfs, 
  /* makeDefault */ 0
);

2. WAL Encryption Testing
   Simulate crash recovery scenarios with encrypted WAL:

   • Force checkpoint interruption
   • Test journal rollback with partial encryption
   • Validate recovery through PRAGMA integrity_check

3. Cross-Platform VFS Validation
   Test shim on target platforms with different sector sizes:

   • 512-byte sectors (legacy HDDs)
   • 4096-byte sectors (modern SSDs)
   • 64k clusters (Windows NTFS)

4. Performance Benchmarking
   Measure encryption overhead under load:

   • TPC-C benchmark with encrypted vs plaintext
   • WAL mode transaction throughput
   • Vacuum operation timing comparisons

Debugging Cryptographic Corruption

Symptom: SQLITE_CORRUPT errors after encryption enablement

Diagnostic steps:

1. Dump raw page contents using sqlite3_serialize
2. Verify reserved byte contents match nonce expectations
3. Check MAC validity across all pages
4. Cross-validate with known-good backup copies
5. Test decryption with page_number-to-tweak derivation

Migration Path from Experimental to Production

1. Phase 1: Read-only compatibility
   Implement xRead decryption without xWrite hooks
2. Phase 2: WAL encryption shadowing
   Encrypt WAL writes but maintain plaintext DB
3. Phase 3: Full encryption with rollback guard
   Use SQLITE_FCNTL_ROLLBACK_ATOMIC_WRITE to prevent partial states

Third-Party Integration Matrix

Final Implementation Recommendations

For most production systems requiring transparent encryption:

1. Adopt SQLite3MultipleCiphers with AES-256-GCM
2. Reserve 28 bytes/page via header configuration
3. Derive keys using HKDF-SHA256 with per-database salt
4. Store initialization vectors in reserved header bytes
5. Enable WAL encryption through separate cipher context

For embedded systems with performance constraints:

1. Implement XTS-AES through modified VFS shim
2. Use page numbers as tweak values
3. Add periodic HMAC verification through background thread
4. Disable WAL to simplify journal encryption

Legacy system migration path:

1. Create encrypted copy via .dump/.restore
2. Enable page-level encryption with VFS shim
3. Gradually phase out plaintext databases