Reading SQLite WAL databases without write permission

A process that only wants to run SELECT statements can still need permission to write beside a database. SQLite's write-ahead log explains this apparent contradiction. Readers need information about committed pages that may not yet be in the main database file, and they coordinate with other connections through shared memory. The permission requirement came from that coordination machinery, not from the SQL statements the application intended to execute.

SQLite 3.22.0, released on 22 January 2018, allows an application without write access to the database or its directory to read a WAL database when the corresponding -wal and -shm files already exist. That is a specific filesystem change. It makes a read-only account more useful without turning a reader into a writer or changing WAL transaction semantics.

I like SQLite because the database is an application file with a SQL engine attached. Permissions ought to express that relationship clearly. This release removes an obstacle to giving a reader only the access it needs, while leaving the requirements of the underlying file format visible.

Why a reader needs more than the main file

The WAL documentation describes a commit as an append to a separate log. Updated pages go into that log, and a commit record makes the transaction visible. A later checkpoint copies committed pages into the main database. Between those operations, the newest committed value of a page can exist only in the WAL.

A reader records an end mark when it starts a read transaction. It then looks for each page in the log up to that mark and uses the main database when no applicable log entry exists. The end mark gives the reader a stable view while another connection appends subsequent transactions. Reading only the main file would bypass that view and could omit committed work.

Searching the entire log for every page would be expensive. SQLite therefore maintains a WAL index in shared memory. In the usual implementation, that shared memory is backed by the -shm file beside the database. Connections on the same machine can use it to locate pages and coordinate access. The index is not an independent replacement for the log, and copying it alone cannot preserve the contents of a transaction.

These details explain the old permission problem. A connection could be a SQL reader while its filesystem setup still required writable coordination files. Version 3.22.0 permits an existing, readable set of auxiliary files to satisfy that requirement. Removing write permission from the directory no longer necessarily prevents the database from opening.

Read-only and immutable describe different promises

The WAL documentation gives several routes to read-only access. Existing readable WAL and shared-memory files are one route. A writable containing directory can permit SQLite to create those files even when the database itself is read-only. An immutable connection supplies a different guarantee about the underlying data.

The URI parameter documentation distinguishes mode=ro from immutable=1. A mode of ro requests read-only access. Setting immutable asserts that the database cannot change, including through a different process with greater privileges. SQLite can omit locking and change detection when the caller makes that stronger assertion.

A URI for an ordinary read-only connection can use file:/srv/catalog/catalog.db?mode=ro when the application's open interface enables URI filenames. That connection does not claim that every other process has stopped writing. Its ability to open a WAL database still depends on the relevant auxiliary files and permissions. A separate writer may remain part of the system, so the connection must retain the coordination appropriate to that arrangement.

An immutable connection instead uses file:/srv/catalog/catalog.db?immutable=1. That is suitable only when the deployment actually prevents changes. Setting the parameter because an account lacks write permission confuses the account's capabilities with the state of the file. Another account may still modify it. The URI documentation warns that violating the assertion can produce incorrect query results or corruption errors because SQLite has skipped the checks that would otherwise detect change.

This distinction matters to packaging as much as to access control. A database embedded in an unchanging artifact has a different lifetime from a reporting connection pointed at a live application's database. Both readers may issue identical SQL, but they cannot make identical promises about the files beneath that SQL.

Publishing a database still requires its committed state

Read-only access does not make the main database file a complete snapshot of a live WAL database. The WAL documentation treats the WAL as part of the persistent state. Separating it from the main file can discard committed transactions. A permission fix cannot reconstruct pages that a file-copy operation omitted.

A checkpoint moves committed pages into the database, but readers can limit its progress. An existing reader still needs the view associated with its end mark. The checkpoint must avoid overwriting pages in ways that invalidate that view. A long read transaction can therefore delay completion even while other connections continue to use the database.

For a database prepared for read-only distribution, the documentation recommends converting to rollback journal mode before putting it on read-only media. That recommendation remains useful after this release. A completed preparation step can produce an artifact whose interpretation does not depend on preserving a live application's coordination files.

I prefer that separation when distributing data. A producer can have its own concurrency requirements, while the published artifact can have a simpler lifetime. The new reader support is valuable when WAL files are intentionally part of the arrangement, but it does not require every static database to retain WAL mode.

What changes for application boundaries

The practical beneficiary is a process that needs to query an existing WAL database without receiving permission to create or alter files in its directory. The database owner can prepare the required files, and the reader can use its restricted account. This makes operating-system permissions a more accurate expression of the reader's role.

The same release includes more visibility into database execution. Its release notes say sqlite3_trace_v2() now reports individual statements executed inside triggers, and the command-line shell gains an .eqp trigger option. Those additions address a related problem of understanding work that is not evident from the application's top-level SQL. They do not change the permission rules, but they make the engine's internal activity easier to inspect.

SQLite's appeal for me includes the ability to reason about that activity without deploying a separate database service. WAL already requires attention to the relationship between transactions and files. Version 3.22.0 makes one restricted deployment possible within that existing model, with the auxiliary files and the immutable assertion determining which kind of reader can open the database.