~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.
Python academiaanthropicarxivbravedeep-researchencryptionhome-automationhomeserverlocallocal-deep-researchlocal-llmmistralollamaopenaipubmedresearchresearch-toolretrieval-augmented-generationsearxngself-hosted
"""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}"