Setting umask for Multi-Process SQLite Access

Two processes share one SQLite database — a writer daemon ingesting data and a reader service rendering it — and one of them fails at sqlite3.connect() or on the first write with SQLITE_CANTOPEN (code 14) or SQLITE_READONLY (code 8), even though the database file itself looks perfectly readable. The cause is almost never the .db file’s mode in isolation. It is the process umask in effect when the database was first created: that mask stamped the permissions of the main file, its -wal write-ahead log and -shm shared-memory index, and — most easily overlooked — the containing directory, and it did so with bits that the other identity cannot use. SQLite in WAL mode needs to create and lock the -wal and -shm companions, which requires write access to the directory, not just to the database file. This page fixes that exact mismatch by establishing a shared group and a deliberate umask before the first open, and verifying the resulting modes — one concrete procedure within File System Permissions & Ownership, part of the SQLite Architecture & Production Hardening discipline.

Diagnosis

Confirm the failure is a permission-set mismatch and not a genuinely missing or corrupt file by stat-ing the whole file-set plus its directory, from the identity that is failing. The relevant facts are owner, group, and mode on four paths: the directory, the .db, and — if the database has been opened in WAL mode at least once — the -wal and -shm files.

# Run AS the failing service account, e.g. `sudo -u reader ...`, so the checks reflect its view.
ls -ld  /var/lib/shared/
ls -l   /var/lib/shared/app.db /var/lib/shared/app.db-wal /var/lib/shared/app.db-shm
id      # the failing identity's uid, gid, and supplementary groups

The signature of this problem: the .db may be mode 0644 (world-readable, owner-writable) so a naive check looks fine, but the directory is 0755 owned by the writer’s user and group, and the -shm file is 0644 owned by the writer. The reader, running under a different uid, is in the writer’s group only if you arranged it — and even in the group, 0644/0755 grants the group no write bit. WAL coordination then fails: the reader cannot write the -shm index or create a -wal file in the directory, so SQLite reports SQLITE_READONLY on a write path or SQLITE_CANTOPEN when it cannot establish shared memory at all.

Read the same facts from Python so the check can live in a startup probe:

import os, stat, grp

def report(path: str) -> str:
    st = os.stat(path)
    return (f"{path}: mode={stat.filemode(st.st_mode)} "
            f"owner_uid={st.st_uid} group={grp.getgrgid(st.st_gid).gr_name} "
            f"group_writable={bool(st.st_mode & stat.S_IWGRP)}")

for p in ("/var/lib/shared", "/var/lib/shared/app.db",
          "/var/lib/shared/app.db-wal", "/var/lib/shared/app.db-shm"):
    try:
        print(report(p))
    except FileNotFoundError:
        print(f"{p}: absent (never opened in WAL, or created elsewhere)")

If group_writable is False on the directory or the -shm file, and the two services run under different uids, you have the umask problem — not a bug in SQLite or a damaged file.

Figure — the same file-set under two process masks: umask 0022 leaves the directory and -shm un-writable by the group, so the reader is denied; umask 0007 with a shared group makes the whole set group-writable and both processes can lock it.

SQLite file-set permissions under umask 0022 versus umask 0007 Two panels compare the same directory holding app.db, app.db-wal and app.db-shm. Left: under umask 0022 the directory is mode 0755 and the files are 0644, so a reader service in the shared group has no write bit and is denied with SQLITE_READONLY when it tries to write the -shm index. Right: under umask 0007 with a shared group the directory is 2770 setgid and the files are 0660, group-writable, so the reader opens and locks the set successfully. umask 0022 · wrong reader shares the group but has no write bit shared data directory drwxr-xr-x 0755 writer:sqlitegrp app.db -rw-r--r-- 0644 app.db-wal -rw-r--r-- 0644 app.db-shm -rw-r--r-- 0644 reader cannot write -shm SQLITE_READONLY / SQLITE_CANTOPEN umask 0007 · shared group whole set is group-writable; setgid keeps the group shared data directory drwxrws--- 2770 writer:sqlitegrp app.db -rw-rw---- 0660 app.db-wal -rw-rw---- 0660 app.db-shm -rw-rw---- 0660 reader opens and locks the set fcntl advisory lock granted

Solution

Fix it in one place: give both services a common supplementary group, set a umask of 0007 in each process before the first database open, and put a setgid bit on the directory so newly created -wal and -shm files inherit the shared group automatically. A umask of 0007 clears no owner or group bits and clears all “other” bits, so SQLite creates the database at 0660 and, because the directory is setgid, the companions land at 0660 in the shared group too — group-writable, world-closed.

Set the mask in the process, not just the shell, so it holds regardless of how the service is launched, then open and confirm WAL is active (which is what forces the -wal/-shm files into existence):

import os, stat, sqlite3, grp

DB = "/var/lib/shared/app.db"
SHARED_GROUP = "sqlitegrp"

def open_shared_writer(db_path: str = DB) -> sqlite3.Connection:
    # umask is PER PROCESS and affects every file this process creates from here on.
    # 0o007 -> new files 0660, new dirs 0770: owner+group full, others nothing.
    old = os.umask(0o007)                       # set before connect() so the DB/-wal/-shm inherit it
    try:
        conn = sqlite3.connect(db_path, isolation_level=None, timeout=10.0)
        conn.execute("PRAGMA journal_mode=WAL;")   # creates -wal and -shm; needs a writable directory
        conn.execute("PRAGMA synchronous=NORMAL;") # fsync at checkpoint, not every commit
        conn.execute("PRAGMA busy_timeout=4000;")  # let a second process wait out a checkpoint lock

        # Read the PRAGMA back and assert WAL actually engaged; a directory the process
        # cannot write to makes WAL silently fall back and the -shm never appears.
        mode = conn.execute("PRAGMA journal_mode;").fetchone()[0].lower()
        assert mode == "wal", f"journal_mode is {mode!r}, not wal — directory likely not writable"
        return conn
    finally:
        os.umask(old)                            # restore so unrelated code is unaffected

Provision the directory once, out of band, so it carries the shared group and the setgid bit before any process opens the database:

sudo groupadd -f sqlitegrp
sudo usermod -aG sqlitegrp writer          # both service accounts join the shared group
sudo usermod -aG sqlitegrp reader
sudo install -d -o writer -g sqlitegrp -m 2770 /var/lib/shared   # 2770 = setgid + rwxrwx---
# setgid (the leading 2) makes every file created inside inherit group `sqlitegrp`,
# so the -wal and -shm files are group-owned regardless of which process creates them.

With the directory at 2770 and each process running umask 0007, whichever service opens the database first creates a file-set the other can immediately read and write.

Verification

First, prove the created modes match intent — the read-back here is the on-disk permission bits, asserted directly:

import os, stat

conn = open_shared_writer()
conn.execute("CREATE TABLE IF NOT EXISTS t(x);")
conn.execute("INSERT INTO t(x) VALUES (1);")   # force a real write so -wal/-shm exist

for suffix in ("", "-wal", "-shm"):
    st = os.stat(DB + suffix)
    assert st.st_mode & stat.S_IWGRP, f"{DB+suffix} is not group-writable (mode {oct(st.st_mode)})"
    assert not (st.st_mode & stat.S_IWOTH), f"{DB+suffix} is world-writable — too permissive"
dir_st = os.stat(os.path.dirname(DB))
assert dir_st.st_mode & stat.S_ISGID, "data directory is missing the setgid bit"
assert dir_st.st_mode & stat.S_IWGRP, "data directory is not group-writable — WAL companions will fail"

Second, prove the other identity can actually open and write. The definitive test runs as the reader account and performs a write that must touch -shm:

sudo -u reader python3 - <<'PY'
import sqlite3
c = sqlite3.connect("/var/lib/shared/app.db", isolation_level=None, timeout=10.0)
c.execute("PRAGMA journal_mode=WAL;")
c.execute("INSERT INTO t(x) VALUES (2);")   # succeeds only if reader can write -shm and the dir
print("reader wrote row; shared access confirmed")
PY

If the writer created the set under umask 0007 and the directory is setgid, this second process opens, locks, and commits without SQLITE_READONLY or SQLITE_CANTOPEN. A failure here almost always means one process was launched with a stricter mask than the other — which the next section addresses.

Failure Modes & Gotchas

WAL needs write access to the directory, not just to the .db file. Engineers frequently chmod 0660 the database file, see the reader still fail, and conclude the permissions are hopeless. The -wal and -shm files are created and truncated inside the directory, and a checkpoint must be able to write them, so a directory the reader cannot write makes WAL coordination impossible even when the main file is perfectly group-writable. Always fix the directory mode (and its setgid bit) together with the files; the directory-write requirement for the shared-memory index is documented in SQLite’s write-ahead logging reference. On a filesystem where advisory locking behaves differently, the interaction with these permissions changes again — see managing file locks on FAT32 vs ext4.

umask is a per-process property, not a per-file one, and it only affects files created after it is set. Setting umask 0007 does nothing to files that already exist with the wrong bits, and it does not propagate to a child process that resets its own mask or to a service started before the mask was applied. If the writer daemon was launched by systemd with a default UMask=0022, calling os.umask(0o007) inside the Python code fixes files created from that point but not the ones the service already wrote — you must both set the process mask and chmod/chgrp the pre-existing set once. For services managed by systemd, set it declaratively in the unit — UMask=0007 in the [Service] section — so every file the daemon creates, including on restart, gets the intended bits without relying on in-code calls that run too late.

Root-created files ignore your group scheme and re-break access. A one-off maintenance command, migration, or backup run as root creates the .db, -wal, or -shm owned by root:root at root’s umask, and the unprivileged services can no longer write them — a silent regression that surfaces as SQLITE_READONLY the next time a normal process runs. Never touch the live file-set as root without immediately restoring ownership (chown writer:sqlitegrp and chmod 0660), and prefer running maintenance under the same service account. Ownership drift like this is exactly what the broader security boundaries and access control discipline exists to prevent.