Restricting load_extension and ATTACH in Production

Two SQLite features turn a query engine into a general-purpose code-and-file gadget, and both are reachable from plain SQL: load_extension() loads and runs an arbitrary shared object in the host process, and ATTACH DATABASE opens an arbitrary file path as a second schema. In a process that executes SQL it did not fully author — a reporting endpoint that accepts user-supplied WHERE fragments, a rules engine that stores expressions in the database, a plugin host that runs tenant queries — either feature is a direct path from “run this query” to “load /tmp/evil.so” or “read /etc/shadow as a table.” This page is the narrow lockdown within Security Boundaries & Access Control, part of the broader SQLite Architecture & Production Hardening discipline: given a connection that will run partly-trusted SQL, how do you disable extension loading, constrain ATTACH, and prove the restrictions hold.

The defence has two layers because the two features are enabled differently. Extension loading has a dedicated C-level switch — enable_load_extension in Python, SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION at the API — that must be off, and even with it off the SQL-callable load_extension() function should be blocked so an injected call fails loudly. ATTACH has no on/off flag; it is legitimate SQL used by backups and cross-database joins, so it cannot simply be disabled. The tool that governs both, statement by statement, is the authorizer: a callback SQLite invokes as it compiles each statement, which can allow, deny, or ignore individual actions like SQLITE_ATTACH and SQLITE_LOAD_EXTENSION before the statement ever runs.

Figure — A compiled statement passes through the authorizer, which denies SQLITE_LOAD_EXTENSION and SQLITE_ATTACH while allowing ordinary reads and writes; the C-level switch keeps extension loading off underneath.

The authorizer gating load_extension and ATTACH on a partly-trusted connection Partly-trusted SQL is compiled, and each action passes through an authorizer callback. Ordinary SELECT, INSERT, and UPDATE actions return SQLITE_OK and proceed to execution. A SQLITE_LOAD_EXTENSION action and a SQLITE_ATTACH action both return SQLITE_DENY, so compilation fails and the statement never runs. Underneath, the C-level enable_load_extension switch is off, so even a call that bypassed the authorizer could not load native code. Partly-trusted SQL compiled per statement Authorizer set_authorizer() inspects each action SQLITE_OK Execute SELECT · INSERT · UPDATE SQLITE_DENY Blocked LOAD_EXTENSION ATTACH statement never runs C-level switch extension loading OFF

Diagnosis

Establish whether the two features are actually reachable on your connection. Extension loading in Python is off unless code turned it on, but a library, an ORM hook, or a well-meaning “enable spatial functions” line may have flipped it; the only reliable check is to attempt a call and see whether it is refused for the right reason:

import sqlite3

conn = sqlite3.connect("/var/lib/app/data.db")
try:
    conn.execute("SELECT load_extension('/nonexistent.so');")
    print("REACHABLE: load_extension() ran (or tried to) — the loader is enabled")
except sqlite3.OperationalError as exc:
    # 'not authorized' / 'not enabled' == blocked; a file-not-found message == ENABLED
    print(f"loader response: {exc}")

A message mentioning “not authorized” or that extensions are disabled means the loader is already closed; a message that it could not find the object means the loader is enabled and only the missing file stopped it — that is the exposure. For ATTACH, the question is not whether it works (it always does by default) but how often legitimate code relies on it, so you know what an authorizer denial would break. Grep the codebase before you clamp down:

# Count and locate legitimate ATTACH usage so a deny rule doesn't break backups/joins.
grep -rniE "attach\s+database|\battach\b\s+['\"]" --include="*.py" --include="*.sql" .

If extension loading is reachable, or ATTACH is used from code paths that also touch untrusted SQL, this lockdown applies. Backups that legitimately use ATTACH are fine — you will scope the denial to the untrusted connection, not the backup one.

Solution

Harden the untrusted connection in two moves: turn the extension loader off at the C level, and install an authorizer that denies SQLITE_LOAD_EXTENSION and SQLITE_ATTACH while allowing everything else. Cap page growth as well, so that even permitted writes cannot exhaust the device. Read the state back by exercising a denied operation, not by trusting the setter returned.

import sqlite3
import logging

logger = logging.getLogger("locked_sqlite")


def _authorizer(action, arg1, arg2, db_name, trigger):
    """Deny code-loading and file-attaching actions; allow the rest."""
    if action in (sqlite3.SQLITE_ATTACH, sqlite3.SQLITE_DETACH):
        return sqlite3.SQLITE_DENY          # block opening arbitrary files as schemas
    if action == sqlite3.SQLITE_FUNCTION and arg2 == "load_extension":
        return sqlite3.SQLITE_DENY          # block the SQL-callable loader by name
    return sqlite3.SQLITE_OK                # SELECT/INSERT/UPDATE/etc. proceed


def open_locked(db_path: str) -> sqlite3.Connection:
    """Open a connection safe to run partly-trusted SQL against."""
    conn = sqlite3.connect(db_path)
    # Disable the C-level extension loader: this is the real switch. Even if the
    # authorizer were removed, no native object could be loaded with this off.
    conn.enable_load_extension(False)          # SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION -> 0

    conn.execute("PRAGMA trusted_schema = OFF;")   # an injected view/trigger cannot call unsafe functions
    conn.execute("PRAGMA max_page_count = 262144;")# cap the DB at 262144 * page_size (~1 GiB at 4 KiB); SQLITE_FULL past it
    conn.execute("PRAGMA foreign_keys = ON;")      # default OFF silently ignores FK constraints

    # The authorizer is the gate for ATTACH (which has no on/off pragma) and a
    # second line of defence for the loader. It runs at compile time.
    conn.set_authorizer(_authorizer)

    # --- read-back verification against SQLite's actual behaviour ---------------
    trusted = conn.execute("PRAGMA trusted_schema;").fetchone()[0]
    max_pg  = conn.execute("PRAGMA max_page_count;").fetchone()[0]
    if trusted != 0:
        raise RuntimeError(f"trusted_schema is {trusted}, expected 0 (OFF)")
    if max_pg != 262144:
        raise RuntimeError(f"max_page_count is {max_pg}, expected 262144")

    logger.info("connection locked: loader off, ATTACH/DETACH denied, max_page_count=%s", max_pg)
    return conn

The authorizer returns one of three constants per action: SQLITE_OK lets it through, SQLITE_DENY fails compilation of the whole statement, and SQLITE_IGNORE lets the statement run but treats the specific action as a no-op. Denying SQLITE_ATTACH (and SQLITE_DETACH for symmetry) at compile time means an ATTACH statement is rejected before it can open any file, and because the authorizer runs during preparation, the rejection is total — there is no partial execution. Keeping the C-level loader off underneath the SQLITE_FUNCTION name-check matters because it is the switch that actually prevents native code from entering the process; the authorizer rule is what makes an attempt fail loudly rather than silently no-op. Pair this with parameterised queries so untrusted values never reach the parser in the first place, exactly as covered in hardening SQLite against SQL injection.

Verification

Prove each restriction by attempting the forbidden operation and asserting it raises. Verifying by reading a flag is not enough — the point is the behaviour, so drive it:

import sqlite3

conn = open_locked("/var/lib/app/data.db")

# 1. The SQL-callable loader must be blocked (not merely "file not found").
try:
    conn.execute("SELECT load_extension('/tmp/whatever.so');")
    raise SystemExit("FAIL: load_extension() reached the loader")
except sqlite3.DatabaseError as exc:
    assert "authoriz" in str(exc).lower() or "not enabled" in str(exc).lower(), \
        f"loader blocked for the wrong reason: {exc}"
    print(f"OK: extension loading denied -> {exc}")

# 2. ATTACH must be denied at compile time by the authorizer.
try:
    conn.execute("ATTACH DATABASE '/etc/passwd' AS leak;")
    raise SystemExit("FAIL: ATTACH was allowed")
except sqlite3.DatabaseError as exc:
    assert "authoriz" in str(exc).lower(), f"ATTACH blocked for the wrong reason: {exc}"
    print(f"OK: ATTACH denied -> {exc}")

# 3. Ordinary SQL must still work — the lockdown is targeted, not total.
n = conn.execute("SELECT count(*) FROM sqlite_master;").fetchone()[0]
print(f"OK: ordinary queries unaffected (sqlite_master rows={n})")

All three must pass: the loader call and the ATTACH both raise with an authorization message, and the plain SELECT returns normally. If the ATTACH succeeds, the authorizer is not installed on this handle; if the loader call fails only with a file-not-found error, enable_load_extension(False) did not run on this connection. Keep this check in your startup smoke test and in CI so a refactor cannot quietly reopen either door, and hold the filesystem perimeter underneath it per file system permissions and ownership — an authorizer is worthless if an attacker can write the database file directly.

Failure Modes & Gotchas

The SQL load_extension() function can stay callable even when you think loading is off. enable_load_extension(False) disables the C-level API, but on some builds the SQL function is governed separately, and enabling it “just for a moment” to register a helper leaves the function reachable for the rest of the connection’s life. Do not toggle the loader on mid-connection; if you must load a trusted extension, do it on a dedicated privileged connection at startup, load exactly what you need, then never enable it on the connection that runs untrusted SQL. The authorizer name-check on load_extension is the belt to that suspenders — it denies the SQL function regardless of the C-level state.

ATTACH is used legitimately, so a blanket deny will break backups and cross-database work. The SQLite online backup path and many maintenance scripts open a second database with ATTACH, and ATTACH ':memory:' or a temp database is a normal, safe pattern for scratch space. Denying SQLITE_ATTACH on every connection will break those flows. Scope the authorizer to the connections that actually run partly-trusted SQL, and run backups and admin tasks on separate, un-authorized handles. If you need to allow a narrow, known-safe attach (a fixed in-memory scratch database) while denying everything else, inspect the target in the authorizer’s arguments and return SQLITE_OK only for the exact path you trust — never for a caller-supplied one.

The authorizer runs on every statement compilation, so a hot path pays for it. The callback fires once per action per prepared statement, which for a query touching many columns and tables is many calls; a heavyweight authorizer (regex, logging, allocation on each call) measurably slows statement preparation on a constrained CPU. Keep the callback a tight sequence of integer comparisons as above, do no I/O or logging inside it, and rely on statement caching so preparation happens rarely. Remember too that temp_store and an in-memory ATTACH interact with these rules — routing scratch data to memory is fine, but it is still an attach the authorizer sees, so account for it explicitly rather than being surprised by a denial. These are the same connection-scoping and trust-boundary concerns that run through the whole security boundaries and access control topic.