Skip to content

~95% on SimpleQA (e.g. Qwen3.6-27B on a 3090). Supports all local and cloud LLMs (llama.cpp, Ollama, Google, ...). 10+ search engines - arXiv, PubMed, your private documents. Everything Local & Encrypted.

11.3 KB
Raw Blame
"""WAL-deletion rollback attack against a warm per-user database.

Research context (sqlite.org/howtocorrupt.html, §"Things that can go
wrong": deleting the -wal file silently rolls the database back to the
state at the last checkpoint -- transactions committed after the
checkpoint are simply lost. For an app whose database rows carry
security state, rollback = silent revivification of revoked state.

Probed and pinned here against the real manager (HOLD on all three
fronts):

* AH1 mid-open WAL deletion SURVIVES: POSIX unlink semantics mean the
  engine's still-open fd keeps the WAL inode alive; the close-time
  ``PRAGMA wal_checkpoint(TRUNCATE)`` (encrypted_db.py::_checkpoint_wal)
  flushes those frames into the main .db through that fd BEFORE
  the final connection close deletes the (name-less) WAL. The post-
  checkpoint canary written into the deleted WAL is readable after
  reopen -- no rollback, no corruption.
* AH2 post-close there IS no rollback window: ``close_user_database``
  checkpoints and SQLite removes the -wal on the last close (round-4 S1
  property), so deleting "the -wal" after close is a no-op against a
  nonexistent file and the canary is durable.
* AH3 a FOREIGN -wal (captured live from a different user's database
  and planted before open) is refused at the credential plane -- the
  open returns None, nothing is cached, and none of the other user's
  data is ever served. That half is asserted normally.
* AH4 the availability half is an OPEN EXPOSURE, asserted as an
  ``xfail(strict)`` on the correct behavior: SQLite's WAL recovery
  WRITES into the target's main .db during that failed open, so removing
  the planted artifacts is not enough -- the account stays permanently
  unopenable and only a restore of the main .db recovers it. A planted
  sidecar must be a recoverable nuisance, not account destruction.
"""

import hashlib
import shutil
import tempfile
import uuid
from pathlib import Path

import pytest
from sqlalchemy import text

from local_deep_research.database.encrypted_db import DatabaseManager

# Module-level gate. Without a working SQLCipher build every test below
# is a no-op, and the dozens of in-body ``pytest.skip`` calls let a CI
# lane report this whole battery green having executed nothing. Skipping
# at import time makes the missing dependency one visible, uniform signal
# per module instead.
pytest.importorskip("sqlcipher3", reason="requires SQLCipher (encrypted mode)")


@pytest.fixture
def manager(tmp_path, monkeypatch):
    """A DatabaseManager writing to an isolated data directory.

    Same self-contained pattern as the round-1..6 files, with the
    sanctioned test-mode KDF knob.
    """
    monkeypatch.setenv("LDR_DATA_DIR", str(tmp_path))
    monkeypatch.setenv("LDR_DB_CONFIG_KDF_ITERATIONS", "1000")
    mgr = DatabaseManager()
    mgr.data_dir = tmp_path
    yield mgr
    mgr.close_all_databases()


def _write_row(engine, table: str, value: str) -> None:
    with engine.begin() as conn:
        conn.execute(
            text(
                f"CREATE TABLE IF NOT EXISTS {table} "  # noqa: S608
                "(id INTEGER PRIMARY KEY, v TEXT)"
            )
        )
        conn.execute(
            text(f"INSERT INTO {table} (v) VALUES (:v)"),  # noqa: S608
            {"v": value},
        )


def test_midopen_wal_deletion_does_not_lose_committed_rows(manager):
    """AH1 (HOLD): unlinking the -wal while the engine is open does NOT
    roll back the post-checkpoint transaction -- the close-time
    checkpoint runs through the still-open fd and flushes it first."""
    if not manager.has_encryption:
        pytest.skip("requires SQLCipher (encrypted mode) to be meaningful")

    username = f"ah1_{uuid.uuid4().hex[:8]}"
    password = "WalRollbackPw1!"  # noqa: S105
    engine = manager.create_user_database(username, password)
    _write_row(engine, "gap_ah", "pre-checkpoint")
    manager.close_user_database(username)  # checkpoint #1

    engine = manager.open_user_database(username, password)
    _write_row(engine, "gap_ah", "AH1-CANARY")  # lives in the WAL only
    db_path = manager._get_user_db_path(username)
    wal = db_path.with_name(db_path.name + "-wal")
    assert wal.exists(), "setup: WAL expected while warm with fresh writes"

    wal.unlink()  # THE ATTACK (sqlite.org/howtocorrupt rollback vector)

    # Close-time checkpoint through the still-open fd, then reopen.
    manager.close_user_database(username)
    reopened = manager.open_user_database(username, password)
    assert reopened is not None, "reopen failed after mid-open WAL deletion"
    with reopened.connect() as conn:
        rows = [
            row[0]
            for row in conn.execute(text("SELECT v FROM gap_ah")).fetchall()
        ]
    assert "pre-checkpoint" in rows
    assert "AH1-CANARY" in rows, (
        "the post-checkpoint transaction was lost to a WAL-deletion "
        "rollback -- the close-time checkpoint did not flush the "
        "still-open WAL inode"
    )


def test_postclose_wal_deletion_window_is_empty(manager):
    """AH2 (HOLD): after a clean close there is no -wal to delete; the
    deletion is a no-op and the canary is durable across reopen."""
    if not manager.has_encryption:
        pytest.skip("requires SQLCipher (encrypted mode) to be meaningful")

    username = f"ah2_{uuid.uuid4().hex[:8]}"
    password = "PostClosePw1!"  # noqa: S105
    engine = manager.create_user_database(username, password)
    _write_row(engine, "gap_ah", "AH2-CANARY")
    manager.close_user_database(username)

    db_path = manager._get_user_db_path(username)
    wal = db_path.with_name(db_path.name + "-wal")
    assert not wal.exists(), (
        "setup: close_user_database must have checkpointed and removed "
        "the WAL (round-4 S1 property)"
    )

    wal.unlink(missing_ok=True)  # the "attack" hits nothing

    reopened = manager.open_user_database(username, password)
    assert reopened is not None
    with reopened.connect() as conn:
        rows = [
            row[0]
            for row in conn.execute(text("SELECT v FROM gap_ah")).fetchall()
        ]
    assert rows == ["AH2-CANARY"], "canary not durable across close/reopen"


def _stage_foreign_wal_attack(manager, stash: Path):
    """Create a target user and a second user, capture the second user's
    LIVE -wal, close everything cleanly, back the target's main .db up,
    and plant the foreign -wal at the target's path.

    Returns ``(target, password, target_db, backup, fingerprint_before)``.
    """
    target = f"ah3t_{uuid.uuid4().hex[:8]}"
    other = f"ah3o_{uuid.uuid4().hex[:8]}"
    password = "ForeignWalPw1!"  # noqa: S105
    engine_t = manager.create_user_database(target, password)
    _write_row(engine_t, "gap_ah", "target-secret")
    engine_o = manager.create_user_database(other, password)
    _write_row(engine_o, "gap_ah", "OTHER-USER-SECRET")

    other_db = manager._get_user_db_path(other)
    other_wal = other_db.with_name(other_db.name + "-wal")
    assert other_wal.exists(), "setup: other user's WAL expected mid-open"
    stolen = stash / "stolen.wal"
    shutil.copy2(other_wal, stolen)
    manager.close_all_databases()

    target_db = manager._get_user_db_path(target)
    backup = stash / "target.db.bak"
    shutil.copy2(target_db, backup)
    fingerprint_before = hashlib.sha256(target_db.read_bytes()).hexdigest()

    # THE ATTACK: plant the foreign WAL before the target's cold open.
    shutil.copy2(stolen, target_db.with_name(target_db.name + "-wal"))
    return target, password, target_db, backup, fingerprint_before


def _clear_sidecars(target_db: Path) -> None:
    for suffix in ("-wal", "-shm"):
        target_db.with_name(target_db.name + suffix).unlink(missing_ok=True)


def _read_rows(engine) -> list:
    with engine.connect() as conn:
        return [
            row[0]
            for row in conn.execute(text("SELECT v FROM gap_ah")).fetchall()
        ]


def test_foreign_wal_planted_before_open_is_refused(manager):
    """AH3 (credential plane, holds today): a live -wal captured from
    ANOTHER user's database and planted at this user's path before a cold
    open is REFUSED -- the open returns None, nothing is cached, and none
    of the other user's data is ever served. Restoring the main .db from
    a backup brings the account back serving its OWN data."""
    if not manager.has_encryption:
        pytest.skip("requires SQLCipher (encrypted mode) to be meaningful")

    with tempfile.TemporaryDirectory() as stash:
        target, password, target_db, backup, _ = _stage_foreign_wal_attack(
            manager, Path(stash)
        )

        assert manager.open_user_database(target, password) is None, (
            "the target opened with a FOREIGN WAL planted -- frames keyed "
            "with another database's key were accepted"
        )
        assert target not in manager.connections
        assert target not in manager._password_verifiers

        # Restore the main .db from the pre-attack backup: full recovery,
        # and the target reads its OWN row, never the other user's.
        shutil.copy2(backup, target_db)
        _clear_sidecars(target_db)
        recovered = manager.open_user_database(target, password)
        assert recovered is not None, "restore-from-backup failed to reopen"
        rows = _read_rows(recovered)
        assert rows == ["target-secret"], f"cross-user data served: {rows}"


@pytest.mark.xfail(
    strict=True,
    reason=(
        "a refused open must not destroy the account: today SQLite's WAL "
        "recovery WRITES the foreign -wal's frames into the target's main "
        ".db during the failed open, so deleting the planted sidecars is "
        "not enough and only a restore of the main .db recovers the user. "
        "Fix: validate/quarantine unexpected -wal/-shm sidecars before "
        "handing the path to sqlcipher3, or open the target read-only for "
        "the credential probe (encrypted_db.py::open_user_database)."
    ),
)
def test_foreign_wal_planted_before_open_leaves_the_target_recoverable(
    manager,
):
    """AH4 (CORRECT behavior, currently unimplemented): after the planted
    foreign -wal is refused, the target's main .db must be untouched and
    removing the planted artifacts must be enough to restore service.

    Anyone able to drop a file next to the database can otherwise destroy
    an account outright -- the credential plane holds, but the data plane
    is gone without an out-of-band backup."""
    if not manager.has_encryption:
        pytest.skip("requires SQLCipher (encrypted mode) to be meaningful")

    with tempfile.TemporaryDirectory() as stash:
        target, password, target_db, _, fingerprint_before = (
            _stage_foreign_wal_attack(manager, Path(stash))
        )

        assert manager.open_user_database(target, password) is None, (
            "setup: the planted-WAL open must still be refused"
        )

        assert (
            hashlib.sha256(target_db.read_bytes()).hexdigest()
            == fingerprint_before
        ), "the refused open WROTE into the target's main .db"

        _clear_sidecars(target_db)
        recovered = manager.open_user_database(target, password)
        assert recovered is not None, (
            "removing the planted artifacts did not restore service -- the "
            "account is destroyed by a file an attacker merely dropped "
            "next to the database"
        )
        rows = _read_rows(recovered)
        assert rows == ["target-secret"], f"cross-user data served: {rows}"