Read-Only WAL Connections Require Write Permissions for SHM/WAL Files

Understanding SQLite WAL Mode and Read-Only Connection Requirements

Issue Overview: Read-Only Connections in WAL Mode and File System Permissions

SQLite’s Write-Ahead Logging (WAL) mode introduces unique file system requirements compared to traditional rollback journal modes. A critical question arises: Can a process with read-only file system permissions reliably access a WAL-mode database while preserving snapshot isolation, even when concurrent writers are active? The answer hinges on how SQLite manages the -wal (Write-Ahead Log) and -shm (Shared Memory) files, which are essential for WAL operation.

Key Technical Dynamics:

1. WAL File Mechanics:
   In WAL mode, writers append changes to the -wal file instead of modifying the main database file directly. Readers use the -wal file to reconstruct a consistent snapshot of the database. The -shm file acts as a shared-memory index to coordinate access to the -wal file across processes.

   • A checkpoint operation transfers changes from the -wal file to the main database file.
   • Long-running read transactions block checkpoints to maintain snapshot consistency.

2. Read-Only Connections and Locking:
   A read-only connection in SQLite (opened with SQLITE_OPEN_READONLY) does not modify the database. However, in WAL mode, such connections still interact with the -shm file to coordinate locks and track active readers. Specifically:

   • Readers acquire shared locks on the database to signal their presence to writers.
   • The -shm file contains metadata about active readers, which writers use to determine when checkpoints are safe.

3. File System Permissions vs. SQLite Semantics:
   Even if a connection is logically read-only (no INSERT, UPDATE, etc.), SQLite may still require physical write access to the directory containing the database. This is because:

   • The -shm file must be writable to update reader counters and lock states.
   • If the -wal or -shm files are missing, SQLite may attempt to create them when opening the database, which requires write permissions.

Core Conflict:
A read-only process without write permissions cannot create or modify the -shm file. If these files are already present (e.g., created by a prior writer), the read-only process may function. However, if the files are missing or truncated, the connection will fail. This creates a dependency: readers in WAL mode require the -wal and -shm files to exist before opening the database, which may necessitate write permissions during initialization.

Possible Causes: Why Read-Only WAL Connections Fail Without Write Permissions

1. Missing -wal or -shm Files

When a read-only connection opens a WAL-mode database:

• SQLite checks for the presence of the -wal and -shm files. If they are missing, it attempts to create them.
• Failure: If the process lacks write permissions, file creation fails, and the connection is aborted.

Example Scenario:
A database is converted to WAL mode by a writer process. Later, a read-only user attempts to open the database after the writer has closed it and deleted the -wal/-shm files (via sqlite3_close with checkpointing). The read-only user cannot recreate these files, leading to an error.

2. Inadequate Locking Mechanism Permissions

The -shm file uses byte-range locks to coordinate readers and writers. On Unix-like systems:

• flock()/fcntl() Behavior: Read locks (shared) on the -shm file do not require write permissions. However, SQLite’s default VFS (virtual file system) may still attempt to write to the -shm file to update reader counters.
• File System Compatibility: On systems where locking relies on file modifications (e.g., some networked file systems), write permissions may be implicitly required even for read locks.

3. Checkpoint Blocking Without -shm Coordination

A read-only connection without access to the -shm file cannot update its reader state. Writers may proceed with checkpoints prematurely, violating snapshot isolation. This results in:

• Data Inconsistency: Readers might observe partial writes if checkpoints proceed while they are active.
• Silent Corruption: In extreme cases, a checkpoint could truncate the -wal file while a reader is using it, leading to crashes or undefined behavior.

Resolving Read-Only WAL Access: Solutions and Configuration

1. Pre-Create -wal and -shm Files

Ensure the auxiliary files exist before read-only connections are opened:

• Persistent WAL Mode: Use PRAGMA journal_mode=WAL; followed by sqlite3_file_control(SQLITE_FCNTL_PERSIST_WAL, 1). This prevents SQLite from deleting the -wal and -shm files on connection close.

  // C API example
  sqlite3_open("database.db", &db);
  sqlite3_file_control(db, "main", SQLITE_FCNTL_PERSIST_WAL, (void*)1);
  

• File System Permissions: Set directory permissions to allow writers to create -wal/-shm files, but restrict write access to the main database file if needed.

2. Immutable Database Configuration

For truly read-only databases (no writers), mark the database as immutable:

• Query Parameter: Append ?immutable=1 to the database URI.

  sqlite3_open_v2("file:database.db?immutable=1", &db, SQLITE_OPEN_READONLY, NULL);
  

• Effect: SQLite skips all checks for -wal/-shm files and assumes no writes will occur. Use this only if the database is guaranteed not to change.

3. Custom VFS for Locking Without Write Access

Modify SQLite’s locking behavior to avoid -shm file writes:

• Alternative Locking Mechanisms: Implement a custom VFS that uses named semaphores or in-memory structures for reader tracking.
• Risks: This approach is complex and may introduce platform-specific dependencies.

4. Directory and File Ownership

Ensure read-only users have execute permissions on the directory (to traverse it) and read permissions on all files:

chmod a+rX /path/to/db_directory
chmod a+r database.db database-wal database-shm

Note: Write permissions on the directory are not required if -wal/-shm files already exist.

5. Monitoring and Maintenance

• Checkpoint Scheduling: Use PRAGMA wal_checkpoint(TRUNCATE) during maintenance windows to minimize -wal file size.
• File Existence Checks: Implement a watchdog process to recreate -wal/-shm files if they are deleted unexpectedly.

Final Recommendations:

1. Use SQLITE_FCNTL_PERSIST_WAL to keep -wal/-shm files permanently.
2. Grant read-only users read access to all database files and execute access to the directory.
3. Avoid SQLITE_OPEN_READONLY for WAL databases unless auxiliary files are guaranteed to exist.
4. For immutable databases, use the immutable parameter to bypass WAL checks entirely.

By addressing these factors, read-only processes can reliably access WAL-mode databases without requiring write permissions, provided the auxiliary files are managed correctly.