Encryption Granularity

[Nailik]

Hi,

i try to understand the Architecture and have read the .io page about the vfs shim.

Could you please tell me if the encryption will encrypt the database file itself on write (and decrypt on read) or only the page that is written?
Same for the journals, i would read it like it's always just the page modified but i would like to be sure.

[utelle]

Could you please tell me if the encryption will encrypt the database file itself on write (and decrypt on read) or only the page that is written?

SQLite performs all file I/O operations through VFS implementations. A VFS is actually only a collection of API functions providing the various required I/O operations (like reading or writing a block of data). SQLite itself includes several VFS implementations (for example, several variants for Windows platforms and for Unix platforms).

VFS implementations can be chained. That is, a VFS can intercept a VFS API call, perform some operations based on the parameter values, and then forward the call to the next VFS in the chain. And this is, how the SQLite3 Multiple Ciphers VFS works. It intercepts all VFS API calls. Many calls are simply forwarded to the underlying default SQLite VFS, but the calls that actually read or write data to or from a file are handled by it.

For a write operation the database page buffer will be encrypted, and then the encrypted buffer will be forwarded to the default VFS; for a read operation the underlying VFS will be called first to retrieve a page buffer from the file, this buffer will then be decrypted, and finally the decrypted buffer will be returned to SQLite.

For database files data are always read or written in page-sized chunks. Journal files are a bit different, because they usually contain not just database pages, but also additional meta data needed to manage the journal. The SQLite3 Multiple Ciphers VFS encrypts or decrypts database pages only. That is, journal meta data are read and written as is.

[Nailik]

Thanks for the detailed answer.

So the page conent in the rollback and wal journal is also encrypted because it's basically a database page and only the metadata (like frame header and page number) are not encrypted right?

[utelle]

So the page content in the rollback and wal journal is also encrypted because it's basically a database page and only the metadata (like frame header and page number) are not encrypted right?

Yes, exactly. Not encrypting page content in the journals would compromise security. Especially for WAL journals that would be a critical issue, because they can exist for long periods of time, while rollback journals only exist until a transaction is finished.