Security Boundaries & Access Control for SQLite #

SQLite has no server process, no GRANT/REVOKE, no network authentication, and no per-row access control. Every one of those responsibilities is delegated to the host: the operating system’s file permissions are the access-control list, the connection URI is the privilege scope, and a handful of PRAGMA and sqlite3_db_config() switches decide whether a malicious payload can rewrite the schema or load native code. On edge gateways, embedded Linux targets, desktop apps, and Python automation runners, leaving those switches at their defaults produces a wide, silent attack surface: an over-permissive directory mode exposes unencrypted telemetry, a writable connection handed to a dashboard renderer corrupts the write-ahead log, and a stock trusted_schema=ON turns an injected view into arbitrary extension loading. This topic is where the SQLite Architecture & Production Hardening discipline pins down each of those boundaries as a deterministic, testable control rather than an implicit assumption. It builds directly on file system permissions and ownership at the storage layer and hands off query-level defense to hardening SQLite against SQL injection.

Core Mechanism & Crash-Safety Defaults #

Because a SQLite database is an ordinary file, the security model begins and ends with what the operating system will let a process do to that file. There is no daemon to mediate access, so two facts follow. First, any process that can open the database file with write permission can rewrite every byte of it, bypassing every application-level check — access control that lives only in your Python code is advisory, not enforced. Second, the -wal and -shm sidecar files created under PRAGMA journal_mode = WAL inherit the parent directory’s ownership and mode at creation time; if that directory is group- or world-writable, an attacker who can write the -wal file can inject frames that the engine will faithfully replay on the next checkpoint. Filesystem hardening is therefore not a hygiene nicety — it is the foundation the whole journaling modes crash-safety guarantee rests on.

Above the filesystem sits the connection scope. A connection opened with a read-only URI (file:edge.db?mode=ro) is prevented by the engine from ever acquiring a write lock, which means a compromised or buggy read consumer cannot mutate the database, cannot trigger a checkpoint, and cannot hold the exclusive lock that stalls the single writer. The related immutable=1 flag tells SQLite the file will not change for the connection’s lifetime and suppresses all locking and -shm mapping — a powerful optimization for genuinely static assets, and a data-corruption footgun the instant the file is actually written to by anyone. Treat immutable=1 as reserved for read-only media or truly frozen reference data.

Innermost is the engine’s trust configuration, and this is where SQLite’s defaults are most surprising to application developers. By default PRAGMA trusted_schema = ON, meaning schema objects — views, triggers, generated columns, and CHECK constraints — are allowed to call application-defined and unsafe functions during ordinary query execution. If an attacker can influence the schema (through an injection vector that reaches DDL, or by tampering with the file directly), a booby-trapped view can run load_extension() or a registered function with side effects the moment an innocent SELECT touches it. The hardening posture is to disable it and to enable the engine’s dedicated defensive mode, which blocks writes to shadow tables and forbids schema corruption tricks:

-- Trust configuration for an untrusted-input deployment
PRAGMA trusted_schema = OFF;   -- schema objects may NOT call unsafe/app functions (default ON is unsafe under injection)
PRAGMA foreign_keys = ON;      -- enforce referential integrity; default OFF silently ignores FK constraints
PRAGMA cell_size_check = ON;   -- detect corrupt B-tree cells early instead of crashing mid-read

Extension loading deserves its own note: the C-level sqlite3_load_extension() entry point is disabled by default, but the SQL function load_extension() follows the same switch, and re-enabling it for a legitimate plugin re-opens the door for injected schema. Where you must load a native extension, load it once at startup from the C/Python side, then leave the SQL-callable path disabled. The SQLITE_DBCONFIG_DEFENSIVE flag (set via conn.setconfig() on Python 3.12+, or through the C API on older runtimes) is the single most important switch for a database that will ever see untrusted input.

Step-by-Step Implementation #

1. Verify prerequisites and baselines #

Before scoping connections, confirm the storage perimeter is already correct — connection-level controls are worthless if the file is world-writable. On a Linux edge node the database file should be mode 0600, its parent directory 0750, and both owned by the dedicated service account. Enforce and verify the perimeter first:

# Provision the perimeter, then read it back and fail loudly on drift
install -o svc-iot -g svc-iot -m 0750 -d /opt/telemetry
install -o svc-iot -g svc-iot -m 0600 /dev/null /opt/telemetry/edge.db

# Verify — expect 600 for the db and 750 for the dir; anything looser is a finding
stat -c '%a %U:%G %n' /opt/telemetry/edge.db /opt/telemetry

The mechanics of octal modes, umask inheritance for the -wal/-shm companions, and container UID mapping are covered in depth under file system permissions and ownership; the rule for this step is simply that the check above must pass before any connection is opened.

2. Select the target security posture #

Not every deployment needs the same controls. Pick the posture from the intent of the connection, not from convenience. Use this decision table to choose the connection mode and trust switches:

The governing principle is least privilege per connection: a process that only reads must be physically unable to write, and only the ingestion path runs with mode=rw. Never open a single shared read-write handle and hope the application layer refrains from writing.

3. Apply configuration with verification #

Apply the posture at connection open, then read every switch back and assert it took effect — a PRAGMA that silently no-ops (for example, setting trusted_schema after the schema is already loaded, or a pooled connection that reused a stale handle) is a boundary you believe you have but do not. The sqlite3 module makes both the apply and the readback explicit:

import os
import sqlite3
import logging

logger = logging.getLogger("sqlite.security")

def open_hardened(db_path: str, *, read_only: bool = False) -> sqlite3.Connection:
    if not os.path.exists(db_path):
        raise FileNotFoundError(f"database not found: {db_path}")

    # 1. Refuse to open a file whose perimeter drifted (defense in depth).
    mode = os.stat(db_path).st_mode & 0o777
    if mode & 0o077:
        raise PermissionError(f"{db_path} is group/world accessible: {oct(mode)}")

    scope = "ro" if read_only else "rw"
    uri = f"file:{db_path}?mode={scope}"
    conn = sqlite3.connect(uri, uri=True, timeout=5.0)  # timeout is SECONDS, not ms

    # 2. Engine trust configuration. DBCONFIG_DEFENSIVE blocks shadow-table
    #    writes and schema-corruption tricks; available on Python 3.12+.
    try:
        conn.setconfig(sqlite3.SQLITE_DBCONFIG_DEFENSIVE, True)
    except AttributeError:
        logger.warning("defensive mode unavailable on this runtime; upgrade to Python 3.12+")

    conn.execute("PRAGMA trusted_schema = OFF;")  # schema objects may not call unsafe funcs
    conn.execute("PRAGMA foreign_keys = ON;")     # enforce FK integrity (default OFF)
    conn.execute("PRAGMA busy_timeout = 5000;")   # shared VFS-level retry window, in ms
    if not read_only:
        conn.execute("PRAGMA journal_mode = WAL;")  # concurrent readers + single writer

    # 3. VERIFY every boundary actually applied — never trust a silent PRAGMA.
    assert conn.execute("PRAGMA trusted_schema;").fetchone()[0] == 0, "trusted_schema still ON"
    assert conn.execute("PRAGMA foreign_keys;").fetchone()[0] == 1, "foreign_keys not enforced"
    assert conn.execute("PRAGMA busy_timeout;").fetchone()[0] == 5000, "busy_timeout not set"
    if not read_only:
        jm = conn.execute("PRAGMA journal_mode;").fetchone()[0]
        assert jm.lower() == "wal", f"journal_mode fell back to {jm}"

    return conn

Every dynamic value that reaches a statement after this point must be passed as a bound parameter (? or :name), never string-formatted — that boundary is the subject of hardening SQLite against SQL injection, and no amount of connection hardening substitutes for it.

Workload Profiles & Threshold Reference #

Security posture scales with the trust of the input and the hostility of the storage medium. The following profiles map the four canonical deployment shapes to concrete boundary settings. Values are starting points to be validated against your recovery-time and threat requirements.

Two cross-cutting rules apply to every profile. Keep trusted_schema = OFF and defensive mode ON everywhere untrusted input can reach the database — there is no workload where re-enabling schema execution buys enough to justify the exposure. And align the busy_timeout at both the driver and PRAGMA layers so that pooled connections and other language bindings share one deterministic retry window rather than racing for the exclusive lock.

Failure Documentation & Edge Cases #

Reader holds the write lock and stalls the checkpoint #

Trigger: a connection opened mode=rw “just in case” is used only for reads but keeps a transaction open, pinning a WAL snapshot so the checkpointer cannot reclaim frames; the -wal file grows without bound and writers begin returning SQLITE_BUSY.

Diagnosis: PRAGMA wal_checkpoint(PASSIVE); returns a non-zero busy column, and the -wal file on disk keeps growing (ls -l /opt/telemetry/edge.db-wal).

Fallback: open read consumers with mode=ro so they can never take a write lock, and configure a fallback routing strategy that buffers writes when the primary lock exceeds the timeout instead of spinning.

trusted_schema left ON under an injection vector #

Trigger: untrusted input reaches a DDL path (or the file is tampered with) and installs a view or trigger that calls load_extension() or a registered function; a later benign SELECT executes it.

Diagnosis: PRAGMA trusted_schema; returns 1, and an audit of sqlite_schema shows views/triggers referencing functions the application never intended to expose (SELECT sql FROM sqlite_schema WHERE sql LIKE '%load_extension%';).

Fallback: set PRAGMA trusted_schema = OFF and enable defensive mode before executing any query, then treat every input as hostile per hardening SQLite against SQL injection.

immutable=1 on a file that is actually written #

Trigger: a reader opens ?immutable=1 for speed, but another process appends to the database; the immutable reader now returns stale or internally inconsistent pages and may report SQLITE_CORRUPT.

Diagnosis: PRAGMA integrity_check; from a normal connection is clean, but the immutable reader sees corruption — the tell that the file changed under an immutable handle.

Fallback: drop immutable=1 and use plain mode=ro; reserve the immutable flag for read-only media or reference data that is genuinely frozen for the connection’s lifetime.

-wal / -shm sidecars created world-readable #

Trigger: the database file is 0600 but the parent directory’s umask produces group- or world-readable -wal/-shm files, leaking uncommitted telemetry frames to other local accounts.

Diagnosis: stat -c '%a %n' /opt/telemetry/edge.db* shows the companions looser than the main file.

Fallback: tighten the directory mode and the service account umask so sidecars inherit 0600; the full inheritance model lives under file system permissions and ownership.

Production Hardening Checklist #

• Database file is 0600 and its directory 0750, both owned by the dedicated service account
• -wal and -shm sidecars inherit 0600 (verified with stat, not assumed from umask)
• Every read consumer opens with mode=ro; only the ingestion path uses mode=rw
• immutable=1 is used only for genuinely static, never-written files
• PRAGMA trusted_schema = OFF applied and read back on every connection
• SQLITE_DBCONFIG_DEFENSIVE enabled (or the runtime upgraded until it can be)
• SQL-callable load_extension() left disabled; any native extension loaded once at startup from the host side
• foreign_keys = ON and busy_timeout aligned across driver and PRAGMA layers
• All dynamic inputs passed as bound parameters — zero string interpolation into SQL
• Offline snapshots encrypted at rest and verified with PRAGMA integrity_check post-copy
• Backup and key-rotation success/failure explicitly logged to catch silent archive corruption

Related Pages #

• File System Permissions & Ownership — the storage-layer perimeter this topic builds on
• Hardening SQLite Against SQL Injection — query-boundary defense for untrusted input
• Busy Timeout Configuration — deterministic retry windows for lock contention
• Journaling Modes Deep Dive — how WAL sidecars and checkpointing interact with access control
• Fallback Routing Strategies — graceful degradation when a write boundary is contended
• SQLite Architecture & Production Hardening — the parent guide for the full hardening discipline