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.

10.3 KB
Raw Blame
"""``create_user_database`` duplicate / concurrent / orphaned-salt safety.

Incident context (PR #5596): the connection cache is only as trustworthy as
the code paths that publish into it. ``_cache_connection`` (engine +
verifier, atomically) runs at the END of a successful create; the guards
in front of it decide who may get there:

* the ``db_path.exists()`` guard rejects a second create for an existing
  database (line ~526);
* the orphaned-salt recovery branch (lines ~560-568) removes a
  salt-without-db left by a dead create and retries, keeping usernames
  reusable.

Round 1 and the incident files covered the OPEN path deeply; the CREATE
path has no real-SQLCipher security coverage beyond happy-path fixtures.
This file pins:

* H1 duplicate create (same AND different password): ValueError, no
  rekey side effect (old password still opens, new still fails), on-disk
  artifacts byte/mode-identical, cache and verifier untouched;
* H2 a create racing an already-materialized DB file: the late create
  fails cleanly with the exists-ValueError and the in-flight winner
  survives intact (see the module FINDING note in the test docstring for
  why the simultaneous barrier variant is deliberately NOT asserted);
* H3 the orphaned-salt recovery branch: a salt without a .db is recovered
  from, the created DB opens with the chosen password, and only the
  documented artifacts remain.
"""

import hashlib
import threading
import time
import uuid

import pytest
from sqlalchemy import text

from local_deep_research.database.encrypted_db import DatabaseManager
from local_deep_research.database.sqlcipher_utils import create_database_salt

# 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 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 _dir_fingerprint(root) -> dict:
    """name -> (size, sha256, mode) for every file under root."""
    fingerprint = {}
    for path in sorted(root.rglob("*")):
        if path.is_file():
            data = path.read_bytes()
            fingerprint[path.name] = (
                len(data),
                hashlib.sha256(data).hexdigest(),
                path.stat().st_mode,
            )
    return fingerprint


def test_duplicate_create_raises_and_changes_nothing(manager):
    """H1: a second create -- same or different password -- must be a clean
    no-op that changes nothing on disk or in the caches, and must NOT rekey
    (the original password keeps working, the impostor password keeps
    failing)."""
    if not manager.has_encryption:
        pytest.skip("requires SQLCipher (encrypted mode) to be meaningful")

    username = f"dup_{uuid.uuid4().hex[:8]}"
    password = "DuplicateCreatePw1!"  # noqa: S105
    other_password = "ImpostorPassword2!"  # noqa: S105
    engine = manager.create_user_database(username, password)
    with engine.begin() as conn:
        conn.execute(
            text(
                "CREATE TABLE gap_h_canary (id INTEGER PRIMARY KEY, note TEXT)"
            )
        )
        conn.execute(
            text("INSERT INTO gap_h_canary (id, note) VALUES (1, :note)"),
            {"note": "gap-H"},
        )

    before = _dir_fingerprint(manager.data_dir)

    with pytest.raises(ValueError, match="already exists"):
        manager.create_user_database(username, password)
    with pytest.raises(ValueError, match="already exists"):
        manager.create_user_database(username, other_password)

    # Nothing on disk moved: same files, same bytes, same modes.
    assert _dir_fingerprint(manager.data_dir) == before, (
        "a rejected duplicate create mutated on-disk artifacts"
    )

    # Cache untouched: same engine object, verifier still for the ORIGINAL
    # password only.
    assert manager.connections[username] is engine
    assert manager._password_matches_cached(username, password) is True
    assert manager._password_matches_cached(username, other_password) is False

    # No rekey happened: the original password still opens (warm AND after
    # a close), the impostor still fails, and the data survived.
    assert manager.open_user_database(username, password) is engine
    manager.close_user_database(username)
    assert manager.open_user_database(username, other_password) is None
    reopened = manager.open_user_database(username, password)
    assert reopened is not None
    with reopened.connect() as conn:
        assert (
            conn.execute(
                text("SELECT note FROM gap_h_canary WHERE id = 1")
            ).scalar()
            == "gap-H"
        )


def test_create_racing_materialized_file_fails_cleanly_and_winner_survives(
    manager,
):
    """H2: thread T1 runs a full create; once its DB file has MATERIALIZED
    on disk (but while T1 may still be mid-migration), a second create for
    the same user must fail with the exists-ValueError, touch nothing, and
    leave T1's outcome intact: one shared cached engine, verifier armed,
    data readable.

    FINDING (documented, deliberately not asserted as a test):
    ``create_user_database`` does NOT acquire the per-user init lock (only
    ``open_user_database`` does). Two creates that both pass the
    ``db_path.exists()`` check before either materializes the file can
    interleave through the orphaned-salt recovery branch, whose
    ``_remove_partial_user_db_files`` may delete the other thread's
    partial DB. This test pins the contract that HOLDS today (a create
    racing an already-existing file fails cleanly); the
    both-past-the-guard interleaving is reported as a finding rather than
    shipped as a test.
    """
    if not manager.has_encryption:
        pytest.skip("requires SQLCipher (encrypted mode) to be meaningful")

    username = f"conccreate_{uuid.uuid4().hex[:8]}"
    password = "ConcurrentCreatePw1!"  # noqa: S105
    db_path = manager._get_user_db_path(username)
    outcome = {}
    errors = {}

    def creator():
        try:
            outcome["engine"] = manager.create_user_database(username, password)
        except Exception as exc:  # noqa: BLE001 - surfaced via `errors` below
            errors["creator"] = exc

    t1 = threading.Thread(target=creator, name="gap-h-creator")
    t1.start()
    try:
        # Wait until the winner's DB file exists but do NOT wait for the
        # create to finish -- the loser must fail cleanly even mid-create.
        deadline = time.monotonic() + 30
        while not db_path.exists():
            if time.monotonic() > deadline or not t1.is_alive():
                break
            time.sleep(0.005)
        assert db_path.exists(), (
            "setup: the creator thread never materialized the DB file"
        )

        with pytest.raises(ValueError, match="already exists"):
            manager.create_user_database(username, password)
    finally:
        t1.join(timeout=60)
    assert not t1.is_alive(), "creator thread hung"
    assert not errors, f"creator thread raised: {errors}"
    assert outcome["engine"] is not None

    # Exactly one engine published, verifier armed for the password, and
    # the engine genuinely works.
    assert manager.connections[username] is outcome["engine"]
    assert manager._password_matches_cached(username, password) is True
    with outcome["engine"].connect() as conn:
        assert conn.execute(text("SELECT 1")).scalar() == 1


def test_orphaned_salt_recovery_creates_usable_database(manager):
    """H3: a .salt without a .db (a create killed mid-way, or pre-#4934
    cleanup that removed only the .db) must NOT brick the username: the
    recovery branch removes the orphan and the create succeeds. Without
    the branch, ``create_database_salt``'s FileExistsError would propagate
    and the username would stay permanently un-registerable -- so SUCCESS
    here is direct evidence the recovery branch ran. The resulting DB must
    open with the chosen password and leave only documented artifacts.
    """
    if not manager.has_encryption:
        pytest.skip("requires SQLCipher (encrypted mode) to be meaningful")

    username = f"orphan_{uuid.uuid4().hex[:8]}"
    password = "OrphanSaltRecoveryPw1!"  # noqa: S105
    db_path = manager._get_user_db_path(username)

    # The orphan: exactly the state the recovery branch exists for. Import
    # and call the same salt creator encrypted_db.py itself uses.
    create_database_salt(db_path)
    assert db_path.with_suffix(db_path.suffix + ".salt").exists()
    assert not db_path.exists()

    engine = manager.create_user_database(username, password)
    assert engine is not None, (
        "orphaned-salt recovery failed (username bricked)"
    )
    with engine.connect() as conn:
        assert conn.execute(text("SELECT 1")).scalar() == 1

    # Post-conditions: the .db exists, a 32-byte salt sits beside it, and
    # nothing beyond the documented artifact family is present.
    assert db_path.exists()
    artifacts = {p.name for p in manager.data_dir.iterdir()}
    stem = db_path.name
    allowed = {
        stem,
        stem + ".salt",
        stem + "-wal",
        stem + "-shm",
        stem + "-journal",
        # Durable rekey-fencing sidecars (PR #6662): the owner-only stable
        # lock inode and the committed epoch projection beside the db.
        stem + ".rekey.lock",
        stem + ".rekey.epoch",
    }
    assert artifacts <= allowed, (
        f"unexpected artifacts after recovery: {artifacts}"
    )

    # And the database reopens with the chosen password after a close.
    manager.close_user_database(username)
    reopened = manager.open_user_database(username, password)
    assert reopened is not None
    with reopened.connect() as conn:
        assert conn.execute(text("SELECT 1")).scalar() == 1
    assert manager._password_matches_cached(username, password) is True