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.

39.1 KB
Raw Blame
"""SQLCipher key lifecycle: KDF floor, credential lifetime, rekey, isolation.

Scope of this module (deliberately narrow — the neighbouring suites already
own the rest):

* ``tests/database/test_kdf_iterations.py`` and
  ``tests/database/test_sqlcipher_utils.py`` own the *env-var semantics* of
  the KDF floor (which values relax it, boolean parsing, the clamp, the
  startup warning). This module does NOT repeat them. It asks the question
  those tests cannot answer: **is the relaxation reachable on a production
  deployment at all**, i.e. does anything we ship set the switch, and is the
  read really call-time (not import-time, which is how its sibling in
  ``fastapi_app`` turned out to be inert).
* ``tests/security/test_cross_user_isolation_census.py`` owns route-level
  IDOR. This module covers the layer *below* it: the key material itself —
  is a derived key bound to one database file, and does creating a database
  fail closed onto an existing file.
* ``tests/security/test_logout_clears_thread_credentials.py`` and
  ``tests/security/test_auth_credential_lifetime_fastapi.py`` own the logout
  and request-context credential paths. This module covers the two teardown
  paths they do not: **server-session expiry** and the **idle-connection
  sweeper**.

Everything here is in-memory or pure AST. **No SQLCipher database is ever
opened** — each open costs a PBKDF2 run, and every property asserted below is
observable without one. Where a derived key is needed, PBKDF2 is called
directly with an explicit low iteration count.

Anti-vacuity
------------
"The credential is gone after X" is worthless unless the credential was
provably present and usable before X. Every teardown assertion in
:class:`TestCredentialLifetime` is preceded by a positive control that
retrieves the passphrase through the SAME accessors production uses
(``get_session_password`` *and* the username-wide ``get_any_session_password``
fallback), and the sweeper tests come in a matched pair: happy path (cleared)
and failure path (NOT cleared).

Findings pinned here (both are asserted as CURRENT behaviour, so they fail
loudly if someone changes them — read the individual docstrings):

1. ``cleanup_idle_connections`` clears ``session_password_store`` *inside*
   the ``try`` that wraps ``close_user_database``, so a raising close skips
   the plaintext-passphrase clear — while the two neighbouring stores
   (thread credentials, per-user locks) are deliberately cleared OUTSIDE
   that try, with comments explaining that they matter most "on the path
   where close raises".
2. ``DatabaseManager.change_password`` returns ``False`` for a failure that
   happens *after* ``PRAGMA rekey`` already succeeded, and the route maps
   ``False`` to "Current password is incorrect" — so a post-rekey error is
   reported to the user as a wrong-password error while their database key
   HAS in fact changed.
"""

from __future__ import annotations

import ast
import hashlib
import re
import secrets
import threading
from datetime import timedelta
from importlib.util import find_spec
from pathlib import Path

import pytest

from local_deep_research.database import encrypted_db
from local_deep_research.database.encrypted_db import DatabaseManager
from local_deep_research.database.session_passwords import (
    SessionPasswordStore,
    session_password_store,
)
from local_deep_research.database import sqlcipher_utils
from local_deep_research.database.sqlcipher_utils import (
    MIN_KDF_ITERATIONS_PRODUCTION,
    MIN_KDF_ITERATIONS_TESTING,
    SALT_SIZE,
    _get_key_from_password,
    _get_min_kdf_iterations,
    create_database_salt,
    get_key_from_password,
    set_sqlcipher_key,
    set_sqlcipher_rekey,
)
from local_deep_research.database.thread_local_session import (
    thread_session_manager,
)
from local_deep_research.web.auth import connection_cleanup
from local_deep_research.web.auth.session_manager import SessionManager

REPO_ROOT = Path(__file__).resolve().parents[2]


class _ReachedSaltCreation(Exception):
    """Sentinel proving control reached the salt-creation step."""


# Cheap-but-real PBKDF2 cost for the key-derivation assertions below. The env
# registry's own min_value for db_config.kdf_iterations is 1000, so this is
# also the lowest value the app can actually be configured with.
FAST_KDF_ITERATIONS = 1000


def _source_of(module_name: str) -> str:
    """Read a module's source WITHOUT importing it.

    ``fastapi_app`` in particular is expensive and side-effectful to import;
    the assertions here are purely syntactic, so the file is parsed directly.
    """
    spec = find_spec(module_name)
    assert spec is not None and spec.origin, f"cannot locate {module_name}"
    return Path(spec.origin).read_text(encoding="utf-8")


def _function_node(tree: ast.Module, name: str) -> ast.FunctionDef:
    for node in ast.walk(tree):
        if isinstance(node, ast.FunctionDef) and node.name == name:
            return node
    raise AssertionError(f"function {name!r} not found")


class _SpyCursor:
    """Records every SQL string executed against it."""

    def __init__(self) -> None:
        self.statements: list[str] = []

    def execute(self, sql, *args, **kwargs):
        self.statements.append(str(sql))
        return self


# ---------------------------------------------------------------------------
# 1. KDF floor — can the relaxation be reached in production?
# ---------------------------------------------------------------------------


class TestKdfFloorProductionReachability:
    """``_get_min_kdf_iterations`` drops the floor from 100_000 to 1 when
    ``PYTEST_CURRENT_TEST`` or ``LDR_TEST_MODE`` is set.

    Which values flip it is covered elsewhere (see module docstring). What is
    NOT covered elsewhere, and is the only thing that decides whether this is
    a production risk, is: (a) does the read actually happen at call time,
    and (b) does anything we ship ever set either variable.
    """

    def test_floor_is_read_at_call_time_not_frozen_at_import(self, monkeypatch):
        """The module is ALREADY imported by the time this test body runs.

        This is the positive/negative pair for the "call time vs import time"
        question that the branch's own commit ``bdb41057d`` had to correct for
        the sibling in ``fastapi_app``: flipping the env var after import must
        change the answer, with no reimport. Both directions are asserted, so
        a build where the value was captured at import fails here whichever
        way it happened to be captured.
        """
        monkeypatch.delenv("PYTEST_CURRENT_TEST", raising=False)
        monkeypatch.delenv("LDR_TEST_MODE", raising=False)
        assert _get_min_kdf_iterations() == MIN_KDF_ITERATIONS_PRODUCTION

        monkeypatch.setenv("PYTEST_CURRENT_TEST", "tests/x.py::test_y (call)")
        assert _get_min_kdf_iterations() == MIN_KDF_ITERATIONS_TESTING

        monkeypatch.delenv("PYTEST_CURRENT_TEST", raising=False)
        assert _get_min_kdf_iterations() == MIN_KDF_ITERATIONS_PRODUCTION

        monkeypatch.setenv("LDR_TEST_MODE", "1")
        assert _get_min_kdf_iterations() == MIN_KDF_ITERATIONS_TESTING

    def test_the_env_reads_live_inside_the_function_not_at_module_scope(self):
        """Structural half of the test above: a future refactor that hoists
        either lookup to module scope would freeze it at import time, which is
        exactly how ``fastapi_app.is_testing`` became inert."""
        tree = ast.parse(
            _source_of("local_deep_research.database.sqlcipher_utils")
        )
        fn = _function_node(tree, "_get_min_kdf_iterations")
        inside = {
            n.value
            for n in ast.walk(fn)
            if isinstance(n, ast.Constant) and isinstance(n.value, str)
        }
        assert "PYTEST_CURRENT_TEST" in inside
        assert "LDR_TEST_MODE" in inside

        # No module-level statement may mention either name. Function and
        # class bodies are excluded from the walk; anything left is executed
        # at import.
        module_level = ast.Module(
            body=[
                stmt
                for stmt in tree.body
                if not isinstance(
                    stmt, (ast.FunctionDef, ast.AsyncFunctionDef, ast.ClassDef)
                )
            ],
            type_ignores=[],
        )
        hoisted = {
            n.value
            for n in ast.walk(module_level)
            if isinstance(n, ast.Constant)
            and isinstance(n.value, str)
            and n.value in ("PYTEST_CURRENT_TEST", "LDR_TEST_MODE")
        }
        assert not hoisted, (
            f"{sorted(hoisted)} read at module scope in sqlcipher_utils — the "
            "KDF floor would be frozen at import time"
        )

    def test_web_cookie_test_mode_is_still_import_time(self):
        """The web cookie policy uses an explicit startup switch.

        Unlike the KDF test helper, ``fastapi_app.is_testing`` must not change
        per test or per request. Its default exercises production cookie
        behavior, regardless of when pytest first imports the app.
        """
        tree = ast.parse(_source_of("local_deep_research.web.fastapi_app"))
        assigns = [
            stmt
            for stmt in tree.body
            if isinstance(stmt, ast.Assign)
            and any(
                isinstance(t, ast.Name) and t.id == "is_testing"
                for t in stmt.targets
            )
        ]
        assert len(assigns) == 1, (
            "fastapi_app.is_testing is no longer a single module-level "
            "assignment — re-read the startup-policy comment before changing "
            "this test"
        )

    def test_nothing_we_ship_sets_the_test_mode_switch(self):
        """The relaxation is only reachable in production if an operator (or
        one of our own deployment artifacts) sets the variable.

        Scanned: the Dockerfile, every compose file, the cookiecutter Docker
        template and the unraid templates — i.e. everything a user deploys —
        plus ``src/`` for code that sets the variable on its own. NOT scanned:
        ``.github/workflows`` and ``scripts/ci`` (CI legitimately sets
        ``LDR_TEST_MODE=1`` against the production image) and ``tests/``.
        """
        assert not _shipped_artifacts_enabling_test_mode(REPO_ROOT)
        assert not _source_files_setting_test_mode(REPO_ROOT / "src")

    def test_the_shipped_artifact_scanner_actually_detects_a_violation(
        self, tmp_path
    ):
        """Positive control: a static scan that resolves nothing passes
        vacuously, so feed both scanners a planted violation."""
        (tmp_path / "docker-compose.yml").write_text(
            "services:\n  ldr:\n    environment:\n      - LDR_TEST_MODE=1\n",
            encoding="utf-8",
        )
        assert _shipped_artifacts_enabling_test_mode(tmp_path)

        pkg = tmp_path / "src"
        pkg.mkdir()
        (pkg / "boot.py").write_text(
            'import os\nos.environ["LDR_TEST_MODE"] = "1"\n', encoding="utf-8"
        )
        assert _source_files_setting_test_mode(pkg)

        # ...and the scanners must not fire on a mere mention, or the
        # assertions above would be pinned by docstrings rather than by code.
        clean = tmp_path / "clean"
        clean.mkdir()
        (clean / "docker-compose.yml").write_text(
            "# do not set LDR_TEST_MODE here\n", encoding="utf-8"
        )
        assert not _shipped_artifacts_enabling_test_mode(clean)
        (clean / "reader.py").write_text(
            'import os\nos.environ.get("LDR_TEST_MODE")\n', encoding="utf-8"
        )
        assert not _source_files_setting_test_mode(clean)

    def test_derived_key_is_a_passphrase_to_sqlcipher_not_a_raw_key(self):
        """``set_sqlcipher_key`` hands SQLCipher ``PRAGMA key = "x'<hex>'"``.

        SQLCipher only treats that form as a RAW key when the hex is 64 chars
        (32-byte key) or 96 chars (key + salt); anything else falls back to
        the passphrase path, where SQLCipher runs its own PBKDF2 at
        ``kdf_iter``. Our derivation is PBKDF2-HMAC-SHA512 with the default
        dklen, i.e. 64 BYTES = 128 hex chars — so despite the "pre-derived
        key" naming, ``PRAGMA kdf_iter`` (and therefore the floor tested
        above) still gates at-rest strength on top of our own PBKDF2.

        Pinned because a future switch to ``dklen=32`` would silently change
        which of the two KDFs is load-bearing.
        """
        key = _get_key_from_password("pw", b"salt", FAST_KDF_ITERATIONS)
        assert len(key) == hashlib.sha512().digest_size == 64
        assert len(key.hex()) == 128
        assert len(key.hex()) not in (64, 96), (
            "derived key now matches a SQLCipher RAW-key length — PRAGMA "
            "kdf_iter would stop applying; re-check the KDF floor's role"
        )

    def test_the_key_pragma_carries_the_derived_key_never_the_passphrase(
        self, tmp_path, monkeypatch
    ):
        """Positive control for the above, and a leak check in one: the SQL
        must contain exactly the derived hex and must not contain the
        plaintext passphrase."""
        monkeypatch.setenv(
            "LDR_DB_CONFIG_KDF_ITERATIONS", str(FAST_KDF_ITERATIONS)
        )
        monkeypatch.setenv("LDR_TEST_MODE", "1")
        db_path = tmp_path / "ldr_user_deadbeefdeadbeef.db"
        create_database_salt(db_path)

        password = "Correct-Horse-Battery-Staple-1!"  # noqa: S105
        cursor = _SpyCursor()
        set_sqlcipher_key(cursor, password, db_path=db_path)

        expected = get_key_from_password(password, db_path=db_path).hex()
        assert cursor.statements == [f"PRAGMA key = \"x'{expected}'\""]
        assert password not in cursor.statements[0]


def _shipped_artifacts_enabling_test_mode(root: Path) -> list[str]:
    """Deployment artifacts that ASSIGN LDR_TEST_MODE (a mention doesn't
    count — the pattern requires ``=``/``:`` or a ``-e`` docker flag)."""
    patterns = [
        "Dockerfile*",
        "docker-compose*.yml",
        "docker-compose*.yaml",
        ".env*",
        "cookiecutter-docker/**/*",
        "unraid-templates/**/*",
    ]
    assign = re.compile(
        r"(?:-e\s+|export\s+|ENV\s+)?LDR_TEST_MODE\s*[:=]", re.IGNORECASE
    )
    hits: list[str] = []
    for pattern in patterns:
        for path in root.glob(pattern):
            if not path.is_file():
                continue
            try:
                text = path.read_text(encoding="utf-8", errors="ignore")
            except OSError:  # pragma: no cover - unreadable file
                continue
            for lineno, line in enumerate(text.splitlines(), 1):
                stripped = line.strip()
                if stripped.startswith("#"):
                    continue
                if assign.search(line):
                    hits.append(f"{path.relative_to(root)}:{lineno}")
    return hits


def _source_files_setting_test_mode(root: Path) -> list[str]:
    """Python files that WRITE either relaxation variable into the
    environment (``os.environ[...] = ...`` / ``setenv`` / ``setdefault``)."""
    write = re.compile(
        r"""environ(?:\[\s*['"](?:LDR_TEST_MODE|PYTEST_CURRENT_TEST)['"]\s*\]"""
        r"""\s*=|\.setdefault\(\s*['"](?:LDR_TEST_MODE|PYTEST_CURRENT_TEST)['"])"""
    )
    hits: list[str] = []
    for path in root.rglob("*.py"):
        text = path.read_text(encoding="utf-8", errors="ignore")
        for lineno, line in enumerate(text.splitlines(), 1):
            if write.search(line):
                hits.append(f"{path.name}:{lineno}")
    return hits


# ---------------------------------------------------------------------------
# 2. Credential lifetime
# ---------------------------------------------------------------------------


@pytest.fixture
def store_cleanup():
    """Yield a recorder of (username, session_id) pairs seeded into the REAL
    process-global store, and drop them afterwards.

    The real global is used deliberately: these tests are about the actual
    object the queue processor, scheduler and RAG factory read from.
    """
    seeded: list[tuple[str, str]] = []
    yield seeded
    for username, session_id in seeded:
        session_password_store.clear_session(username, session_id)
        session_password_store.clear_all_for_user(username)


class TestCredentialLifetime:
    def test_passphrase_survives_server_session_expiry(self, store_cleanup):
        """FINDING (bounded): expiring a server-side session does NOT drop
        the plaintext SQLCipher passphrase.

        ``validate_session`` deletes the expired session inline and
        ``cleanup_expired_sessions`` deletes it in bulk; neither touches
        ``session_password_store``. Only three call sites clear it — logout,
        change-password, and the idle-connection sweeper — so between session
        expiry and the next sweep (300s) the passphrase remains live and,
        because ``get_any_session_password`` is a username-wide scan, usable
        by any background caller that resolves a password by username alone
        (``get_user_db_session`` with no session_id, the RAG factory,
        ``library.py``).

        HTTP replay is NOT the exposure here: ``require_auth`` rejects a dead
        session id. The exposure is in-process credential lifetime.
        """
        sm = SessionManager()
        username = "kl_expiry_user"
        password = "Expiry-Passphrase-1!"  # noqa: S105
        session_id = sm.create_session(username, remember_me=False)
        session_password_store.store_session_password(
            username, session_id, password
        )
        store_cleanup.append((username, session_id))

        # POSITIVE CONTROL: the credential is present and usable through both
        # accessors, and the session that authorised it validates.
        assert sm.validate_session(session_id) == username
        assert (
            session_password_store.get_session_password(username, session_id)
            == password
        )
        assert (
            session_password_store.get_any_session_password(username)
            == password
        )

        # Age the session past its own timeout and let the two expiry paths
        # run. Both must agree the session is gone.
        sm.sessions[session_id]["last_access"] -= sm.session_timeout + (
            timedelta(seconds=1)
        )
        assert sm.validate_session(session_id) is None
        assert session_id not in sm.sessions
        sm.cleanup_expired_sessions()
        assert not sm.has_active_sessions_for(username)

        # THE FINDING: the passphrase that session authorised is still here,
        # and still resolvable by username alone.
        assert (
            session_password_store.get_session_password(username, session_id)
            == password
        ), "session expiry now clears the password store — update this test"
        assert (
            session_password_store.get_any_session_password(username)
            == password
        )

    def test_store_ttl_outlives_the_session_timeout_by_construction(self):
        """The 24h store TTL is a fixed constant, unrelated to the (default
        2h) session timeout, so the window above is structural rather than
        incidental."""
        store = SessionPasswordStore()
        assert store.ttl == 24 * 3600

        sm = SessionManager()
        assert store.ttl > sm.session_timeout.total_seconds(), (
            f"session_password_store TTL ({store.ttl}s) is expected to exceed "
            f"the session timeout ({sm.session_timeout.total_seconds()}s); if "
            "that is no longer true the expiry window above has closed"
        )


class _FakeSessionManager:
    """Only what ``cleanup_idle_connections`` calls: nobody is logged in."""

    def __init__(self) -> None:
        self.cleanup_calls = 0

    def cleanup_expired_sessions(self) -> None:
        self.cleanup_calls += 1

    def get_active_usernames(self) -> set:
        return set()

    def has_active_sessions_for(self, username: str) -> bool:
        return False


class _FakeDBManager:
    """A db_manager with one connected user whose close can be made to raise."""

    def __init__(self, username: str, *, close_raises: bool) -> None:
        self.connections: dict = {}
        self._connections_lock = threading.RLock()
        self._username = username
        self._close_raises = close_raises
        self.close_calls: list[str] = []

    def get_connected_usernames(self) -> set:
        return {self._username}

    def close_user_database(self, username: str) -> None:
        self.close_calls.append(username)
        if self._close_raises:
            raise RuntimeError("WAL checkpoint failed while closing")


@pytest.fixture
def sweeper_env(monkeypatch):
    """Neutralise everything in ``cleanup_idle_connections`` that reaches out
    of process (research registry, sockets), and pin the 30-minute periodic
    dispose closed so the sweep is deterministic."""
    monkeypatch.setattr(
        connection_cleanup, "get_usernames_with_active_research", lambda: set()
    )
    monkeypatch.setattr(
        connection_cleanup, "_disconnect_all_user_sockets", lambda _u: None
    )
    popped: list[str] = []
    monkeypatch.setattr(
        connection_cleanup, "_pop_per_user_locks", popped.append
    )
    import time as _time

    monkeypatch.setattr(
        connection_cleanup, "_last_dispose_time", _time.monotonic()
    )
    return popped


class TestIdleSweeperCredentialTeardown:
    """The idle sweeper is the teardown path for the MAJORITY of users (most
    close the tab rather than clicking logout), so what it does and does not
    clear decides real-world credential lifetime.
    """

    @staticmethod
    def _seed(username: str, password: str, store_cleanup) -> int:
        """Put the passphrase in BOTH stores the sweeper is meant to clear,
        and prove it is retrievable from each before the sweep runs."""
        session_id = "sweeper-session"
        session_password_store.store_session_password(
            username, session_id, password
        )
        store_cleanup.append((username, session_id))
        tid = threading.get_ident()
        with thread_session_manager._lock:
            thread_session_manager._thread_credentials[tid] = (
                username,
                password,
            )
        assert (
            session_password_store.get_any_session_password(username)
            == password
        )
        assert thread_session_manager._thread_credentials[tid][1] == password
        return tid

    def test_happy_path_clears_both_credential_stores(
        self, sweeper_env, store_cleanup
    ):
        """POSITIVE CONTROL for the failure case below: when
        ``close_user_database`` succeeds, both the plaintext passphrase and
        the per-thread credential cache are dropped."""
        username = "kl_sweep_ok"
        password = "Sweep-OK-1!"  # noqa: S105
        tid = self._seed(username, password, store_cleanup)
        db = _FakeDBManager(username, close_raises=False)

        connection_cleanup.cleanup_idle_connections(_FakeSessionManager(), db)

        assert db.close_calls == [username]
        assert session_password_store.get_any_session_password(username) is None
        assert tid not in thread_session_manager._thread_credentials
        assert username in sweeper_env  # locks popped

    def test_a_raising_close_leaves_the_plaintext_passphrase_behind(
        self, sweeper_env, store_cleanup
    ):
        """FINDING: ``session_password_store.clear_all_for_user`` sits INSIDE
        the ``try`` that wraps ``close_user_database``, so a raising close
        skips it — while ``clear_user_credentials`` and
        ``_pop_per_user_locks``, placed just below, are deliberately OUTSIDE
        that try with comments saying they matter most "on the path where
        close raises". The plaintext SQLCipher master passphrase is the one
        of the three that does not get that treatment.

        Severity is bounded by ``close_user_database`` catching its own
        dispose/checkpoint errors internally today — so the raising close is
        injected here rather than provoked. The asymmetry is still real: it
        makes the passphrase's survival depend on an internal try/except two
        modules away, and the sweeper has already destroyed the session,
        severed the sockets and told the scheduler the user is gone.

        The contrast is the assertion: same sweep, same user, thread cache
        cleared, passphrase not.
        """
        username = "kl_sweep_raises"
        password = "Sweep-Raises-1!"  # noqa: S105
        tid = self._seed(username, password, store_cleanup)
        db = _FakeDBManager(username, close_raises=True)

        connection_cleanup.cleanup_idle_connections(_FakeSessionManager(), db)

        assert db.close_calls == [username]
        # Cleared, because they run outside the try:
        assert tid not in thread_session_manager._thread_credentials
        assert username in sweeper_env
        # NOT cleared, because it runs inside it — and still usable through
        # the username-wide fallback that background workers use:
        assert (
            session_password_store.get_any_session_password(username)
            == password
        ), (
            "clear_all_for_user now survives a raising close — the ordering "
            "defect this test pins has been fixed; delete it"
        )


# ---------------------------------------------------------------------------
# 3. Rekey on password change
# ---------------------------------------------------------------------------


class _RaisingConnectCtx:
    """An ``engine.connect()`` context manager that runs the body fine and
    then fails on exit — the shape of a commit/close error arriving AFTER
    ``PRAGMA rekey`` has already rewritten every page."""

    def __init__(self, conn, raise_on_exit: bool) -> None:
        self._conn = conn
        self._raise = raise_on_exit

    def __enter__(self):
        return self._conn

    def __exit__(self, *exc):
        if self._raise:
            raise RuntimeError("disk I/O error on connection close")
        return False


class _FakeEngine:
    def __init__(self, raise_on_exit: bool) -> None:
        self.conn = _SpyCursor()
        self._raise = raise_on_exit

    def connect(self):
        return _RaisingConnectCtx(self.conn, self._raise)


def _bare_manager(tmp_path) -> DatabaseManager:
    """A ``DatabaseManager`` WITHOUT running ``__init__``.

    ``__init__`` probes SQLCipher availability by creating a throwaway
    encrypted database — a real PBKDF2 run this module refuses to pay. Only
    the attributes the methods under test touch are set.
    """
    mgr = DatabaseManager.__new__(DatabaseManager)
    mgr.connections = {}
    mgr._password_verifiers = {}
    mgr._connection_epochs = {}
    mgr._init_locks = {}
    mgr._rekey_locks = {}
    mgr._password_change_locks = {}
    mgr._verifier_key = secrets.token_bytes(32)
    mgr._connections_lock = threading.RLock()
    mgr._initialized_data_dirs = set()
    mgr._data_dir_override = None
    mgr.data_dir = tmp_path
    mgr.has_encryption = True
    return mgr


class TestRekeyOnPasswordChange:
    def test_rekey_derives_exactly_the_key_the_next_open_will_derive(
        self, tmp_path, monkeypatch
    ):
        """Constructive half of "is the rekey atomic": whatever else happens,
        the NEW key written by ``PRAGMA rekey`` must be bit-identical to the
        one a subsequent open derives, or the database is unopenable by
        anyone.

        Both sides read the same per-database ``.salt`` file, so this also
        pins that a rekey never rotates the salt out from under the file.
        """
        monkeypatch.setenv(
            "LDR_DB_CONFIG_KDF_ITERATIONS", str(FAST_KDF_ITERATIONS)
        )
        monkeypatch.setenv("LDR_TEST_MODE", "1")
        db_path = tmp_path / "ldr_user_0123456789abcdef.db"
        salt = create_database_salt(db_path)
        assert len(salt) == SALT_SIZE

        old_password = "Old-Passphrase-1!"  # noqa: S105
        new_password = "New-Passphrase-2!"  # noqa: S105

        rekey_cursor = _SpyCursor()
        set_sqlcipher_rekey(rekey_cursor, new_password, db_path=db_path)
        (rekey_sql,) = rekey_cursor.statements

        open_cursor = _SpyCursor()
        set_sqlcipher_key(open_cursor, new_password, db_path=db_path)
        (open_sql,) = open_cursor.statements

        rekeyed_hex = re.search(r"x'([0-9a-f]+)'", rekey_sql).group(1)
        reopen_hex = re.search(r"x'([0-9a-f]+)'", open_sql).group(1)
        assert rekeyed_hex == reopen_hex

        # NEGATIVE CONTROL: the equality above is not trivially true of any
        # two derivations against this file.
        old_cursor = _SpyCursor()
        set_sqlcipher_key(old_cursor, old_password, db_path=db_path)
        assert (
            re.search(r"x'([0-9a-f]+)'", old_cursor.statements[0]).group(1)
            != rekeyed_hex
        )

        # ...nor of the same password against a DIFFERENT database file.
        other_path = tmp_path / "ldr_user_fedcba9876543210.db"
        create_database_salt(other_path)
        other_cursor = _SpyCursor()
        set_sqlcipher_key(other_cursor, new_password, db_path=other_path)
        assert (
            re.search(r"x'([0-9a-f]+)'", other_cursor.statements[0]).group(1)
            != rekeyed_hex
        )

    def test_change_password_succeeds_when_the_connection_closes_cleanly(
        self, tmp_path, monkeypatch
    ):
        """POSITIVE CONTROL for the failure case below."""
        mgr, calls = self._arm(tmp_path, monkeypatch, raise_after_rekey=False)
        assert mgr.change_password("u", "old", "new") is True
        assert calls == ["new"]

    def test_change_password_reports_false_after_the_rekey_already_landed(
        self, tmp_path, monkeypatch
    ):
        """FINDING (now FIXED by the serialized rekey lifecycle): a failure
        AFTER ``PRAGMA rekey`` has already landed used to be swallowed into
        the same ``False`` the route flashed as "Current password is
        incorrect" — telling the user their old password was wrong while the
        only passphrase that could open the database was the new one.

        The staged lifecycle classifies this as INDETERMINATE: the pending
        epoch was durably published before the physical rekey, so every
        subsequent access is fenced until verified recovery, and the route
        answers 503 instead of a wrong-password 401. This pins the typed
        classification, not the old boolean.
        """
        from local_deep_research.database.rekey_lock import (
            RekeyIndeterminateError,
        )

        mgr, calls = self._arm(tmp_path, monkeypatch, raise_after_rekey=True)

        with pytest.raises(RekeyIndeterminateError):
            mgr.change_password("u", "old", "new")

        assert calls == ["new"], "the rekey must have actually been issued"

    class _PragmaConnection:
        """Raw-connection stand-in answering the lifecycle's pragma probes."""

        def __init__(self):
            self.closed = False

        def execute(self, sql, *args, **kwargs):
            sql = sql.strip().upper()
            if sql.startswith("PRAGMA SYNCHRONOUS ="):
                return None
            if sql.startswith("PRAGMA BUSY_TIMEOUT ="):
                # The post-rekey checkpoint bounds its busy wait first.
                return None
            if sql == "PRAGMA SYNCHRONOUS":

                class _R:
                    @staticmethod
                    def fetchone():
                        return (2,)

                return _R()
            if sql.startswith("PRAGMA WAL_CHECKPOINT"):

                class _C:
                    @staticmethod
                    def fetchone():
                        return (0, 0, 0)

                return _C()
            if sql == "SELECT COUNT(*) FROM SQLITE_MASTER":

                class _S:
                    @staticmethod
                    def fetchone():
                        return (1,)

                return _S()
            if sql.startswith("PRAGMA QUICK_CHECK"):
                return [("ok",)]
            if sql.startswith("PRAGMA CIPHER_INTEGRITY_CHECK"):
                return []
            raise AssertionError(f"unexpected probe: {sql}")

        def commit(self):
            pass

        def close(self):
            self.closed = True

    @classmethod
    def _arm(cls, tmp_path, monkeypatch, *, raise_after_rekey: bool):
        """Wire a manager whose rekey is observed and whose post-rekey
        phase can be made to fail. No SQLCipher, no PBKDF2 — raw
        connections are pragma stand-ins, matching the staged lifecycle's
        seams (_make_sqlcipher_connection + set_sqlcipher_rekey)."""
        mgr = _bare_manager(tmp_path)
        db_path = mgr._get_user_db_path("u")
        db_path.write_bytes(b"")
        monkeypatch.setattr(
            mgr,
            "_make_sqlcipher_connection",
            lambda path, password: cls._PragmaConnection(),
        )

        calls: list[str] = []

        def rekey(connection, new_password, *, db_path):
            calls.append(new_password)
            if raise_after_rekey:
                raise OSError("simulated failure after the rekey landed")

        monkeypatch.setattr(encrypted_db, "set_sqlcipher_rekey", rekey)
        return mgr, calls

    def test_route_reports_a_post_rekey_failure_as_a_wrong_password(self):
        """The half of the finding that lives in the web layer: the route has
        exactly one ``False`` branch and it flashes "Current password is
        incorrect". Asserted structurally (the string must NOT be inside the
        ``if success:`` body, and MUST be in the code that follows it)."""
        tree = ast.parse(_source_of("local_deep_research.web.routers.auth"))
        fn = _function_node(tree, "change_password")
        success_ifs = [
            node
            for node in ast.walk(fn)
            if isinstance(node, ast.If)
            and isinstance(node.test, ast.Name)
            and node.test.id == "success"
        ]
        assert len(success_ifs) == 1
        node = success_ifs[0]

        def strings(nodes) -> set:
            out = set()
            for item in nodes:
                for sub in ast.walk(item):
                    if isinstance(sub, ast.Constant) and isinstance(
                        sub.value, str
                    ):
                        out.add(sub.value)
            return out

        assert "Current password is incorrect" not in strings(node.body)
        index = fn.body.index(node)
        assert "Current password is incorrect" in strings(fn.body[index + 1 :])


# ---------------------------------------------------------------------------
# 4. Isolation of key material
# ---------------------------------------------------------------------------


class TestKeyMaterialIsolation:
    def test_derived_key_is_bound_to_the_target_database_file(
        self, tmp_path, monkeypatch
    ):
        """One password derives DIFFERENT keys for different databases,
        because the salt lives beside the file. So even a caller that somehow
        pointed at another user's path would not produce a usable key for it —
        the binding is to the file, not just to the username."""
        monkeypatch.setenv(
            "LDR_DB_CONFIG_KDF_ITERATIONS", str(FAST_KDF_ITERATIONS)
        )
        monkeypatch.setenv("LDR_TEST_MODE", "1")
        password = "Shared-Passphrase-1!"  # noqa: S105

        alice = tmp_path / "ldr_user_aaaaaaaaaaaaaaaa.db"
        bob = tmp_path / "ldr_user_bbbbbbbbbbbbbbbb.db"
        create_database_salt(alice)
        create_database_salt(bob)

        key_a = get_key_from_password(password, db_path=alice)
        key_b = get_key_from_password(password, db_path=bob)
        assert key_a != key_b

        # POSITIVE CONTROL: the derivation is deterministic, so the
        # inequality above is the salt talking and not nondeterminism.
        assert get_key_from_password(password, db_path=alice) == key_a

    def test_legacy_saltless_databases_share_one_key_per_password(
        self, tmp_path, monkeypatch
    ):
        """Documented residual, pinned so it is not forgotten: v1 databases
        (no ``.salt`` sidecar) all fall back to ``LEGACY_PBKDF2_SALT``, so two
        legacy users who pick the SAME password hold IDENTICAL keys. Isolation
        for them rests entirely on the username-derived filename, not on the
        key material."""
        monkeypatch.setenv(
            "LDR_DB_CONFIG_KDF_ITERATIONS", str(FAST_KDF_ITERATIONS)
        )
        monkeypatch.setenv("LDR_TEST_MODE", "1")
        password = "Same-Passphrase-1!"  # noqa: S105

        legacy_a = tmp_path / "ldr_user_1111111111111111.db"
        legacy_b = tmp_path / "ldr_user_2222222222222222.db"
        assert not sqlcipher_utils.has_per_database_salt(legacy_a)
        assert not sqlcipher_utils.has_per_database_salt(legacy_b)

        assert get_key_from_password(
            password, db_path=legacy_a
        ) == get_key_from_password(password, db_path=legacy_b)

        # ...and the v2 path is what fixes it — same two paths, with salts.
        create_database_salt(legacy_a)
        create_database_salt(legacy_b)
        assert get_key_from_password(
            password, db_path=legacy_a
        ) != get_key_from_password(password, db_path=legacy_b)

    def test_create_user_database_refuses_an_already_occupied_file(
        self, tmp_path, monkeypatch
    ):
        """User database filenames are ``sha256(username)[:16]`` — 64 bits,
        so a collision is not cryptographically out of reach for an attacker
        who can choose a username. This is the guard that makes such a
        collision fail closed instead of writing user A's tables into user
        B's encrypted file: creation refuses outright when the path exists.
        """
        mgr = _bare_manager(tmp_path)
        victim_path = mgr._get_user_db_path("victim")
        victim_path.write_bytes(b"not really a database")

        sentinel_calls: list[Path] = []

        def _sentinel_salt(db_path):
            sentinel_calls.append(Path(db_path))
            raise _ReachedSaltCreation

        monkeypatch.setattr(
            encrypted_db, "create_database_salt", _sentinel_salt
        )

        with pytest.raises(ValueError, match="already exists"):
            mgr.create_user_database("victim", "AnyPassphrase-1!")
        assert not sentinel_calls, "creation continued past the exists guard"

        # POSITIVE CONTROL: with the path free, the same call DOES proceed
        # past that guard (proving the ValueError above came from the guard
        # and not from some earlier unrelated rejection).
        with pytest.raises(_ReachedSaltCreation):
            mgr.create_user_database("newcomer", "AnyPassphrase-1!")
        assert sentinel_calls == [mgr._get_user_db_path("newcomer")]

    def test_username_hash_is_truncated_to_64_bits(self):
        """Pins the collision surface the guard above compensates for, and
        that two distinct usernames do map to distinct files."""
        from local_deep_research.config.paths import (
            get_user_database_filename,
        )

        name = get_user_database_filename("alice")
        assert (
            name == f"ldr_user_{hashlib.sha256(b'alice').hexdigest()[:16]}.db"
        )
        assert len(name) == len("ldr_user_") + 16 + len(".db")
        assert get_user_database_filename("alice") != (
            get_user_database_filename("bob")
        )

        # Fails closed rather than collapsing onto sha256("") — see #5481.
        with pytest.raises(ValueError):
            get_user_database_filename("")