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.

9.1 KB
Raw Blame
"""Session-vs-engine lifetime after close_user_database.

Incident context (PR #5596): the never-open-on-miss invariant of
``get_session`` protects the CACHE. Separately, an Engine object handed
out by ``open_user_database`` / ``get_session`` may OUTLIVE its cache
entry: ``close_user_database`` calls ``engine.dispose()`` (after a WAL
checkpoint), which closes the pool's connections but leaves the engine
usable -- its creator closure retains the pre-derived hex key, so the
next checkout transparently reconnects without retaining the plaintext
password in that closure.

This file covers both halves of that:

* N1 -- OPEN EXPOSURE, asserted as an ``xfail(strict)`` on the correct
  behavior: a ``Session`` handed out before ``close_user_database`` keeps
  reading plaintext AFTER the close, because ``dispose()`` only drops the
  pooled connections while the creator closure retains the same derived
  hex key. ``close_user_database`` is what a logout runs, so a logout does
  not revoke sessions that are already in flight. The test asserts what
  MUST happen -- the held session can no longer read the user's data --
  so it flips to passing when revocation lands.
* N3 the credential plane around that close holds and is asserted
  normally: a later ``open_user_database(correct)`` cold-opens and
  republishes a NEW engine object (the cache was emptied by the close),
  and a later ``open_user_database(wrong)`` fails CLOSED through the
  verifier.
* N4 a resurrected session is NOT a cross-user credential bypass: it is
  bound to ITS user's engine forever and can never reach another user's
  tables.
"""

import uuid

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/2 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()


@pytest.mark.xfail(
    strict=True,
    reason=(
        "close_user_database is what a logout runs, so it must revoke "
        "sessions already handed out; today engine.dispose() only drops "
        "the pooled connections and the engine's creator closure "
        "retains the same derived key and reconnects, so a held "
        "Session keeps reading plaintext after logout. Fix: invalidate "
        "the creator (or the engine's key material) in "
        "encrypted_db.py::close_user_database."
    ),
)
def test_close_user_database_revokes_a_held_session(manager):
    """N1 (CORRECT behavior, currently unimplemented): a Session obtained
    before ``close_user_database`` must not be able to read the user's
    data afterwards.

    ``close_user_database`` is the manager-level logout. If a Session
    that predates it keeps serving decrypted rows, then "log out" does
    not mean "stop decrypting" for any request already holding one."""
    if not manager.has_encryption:
        pytest.skip("requires SQLCipher (encrypted mode) to be meaningful")

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

    session = manager.get_session(username)
    assert session is not None
    assert session.bind is engine

    manager.close_user_database(username)
    assert username not in manager.connections
    assert username not in manager._password_verifiers

    try:
        with pytest.raises(Exception):  # noqa: B017,PT011 - shape not pinned
            session.execute(
                text("SELECT note FROM gap_n_canary WHERE id = 1")
            ).scalar()
    finally:
        session.close()


def test_cold_reopen_after_close_republishes_and_fails_closed(manager):
    """N3: after ``close_user_database`` the cache is empty, so the next
    ``open_user_database(correct)`` is a COLD open that publishes a NEW
    engine object reading the canary, and ``open_user_database(wrong)``
    fails closed through the verifier without disturbing it."""
    if not manager.has_encryption:
        pytest.skip("requires SQLCipher (encrypted mode) to be meaningful")

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

    republished = manager.open_user_database(username, password)
    assert republished is not None
    assert republished is not engine, (
        "open_user_database must republish a fresh engine after the cache "
        "entry was closed (cold open), not resurrect the cache identity"
    )
    assert manager.connections[username] is republished
    with republished.connect() as conn:
        assert (
            conn.execute(
                text("SELECT note FROM gap_n_canary WHERE id = 1")
            ).scalar()
            == "gap-N"
        )

    # A wrong password on the next credential-taking open fails CLOSED.
    assert manager.open_user_database(username, "lifetime-wrong") is None
    assert manager.connections[username] is republished, (
        "a rejected wrong-password open disturbed the republished engine"
    )
    assert manager._password_matches_cached(username, password) is True
    assert manager._password_matches_cached(username, "lifetime-wrong") is False


def test_session_cannot_reach_another_users_database(manager):
    """N4: a Session (live or resurrected) is bound to its own user's
    engine and can never read another user's tables -- get_session is not
    a cross-user credential bypass."""
    if not manager.has_encryption:
        pytest.skip("requires SQLCipher (encrypted mode) to be meaningful")

    alice = f"life_alice_{uuid.uuid4().hex[:8]}"
    bob = f"life_bob_{uuid.uuid4().hex[:8]}"
    alice_password = "AliceLifetimePw1!"  # noqa: S105
    bob_password = "BobLifetimePw2!"  # noqa: S105

    alice_engine = manager.create_user_database(alice, alice_password)
    bob_engine = manager.create_user_database(bob, bob_password)
    with bob_engine.begin() as conn:
        conn.execute(
            text(
                "CREATE TABLE gap_n_b_private "
                "(id INTEGER PRIMARY KEY, secret TEXT)"
            )
        )
        conn.execute(
            text(
                "INSERT INTO gap_n_b_private (id, secret) "
                "VALUES (1, 'bobs-secret')"
            )
        )

    alice_session = manager.get_session(alice)
    assert alice_session is not None
    assert alice_session.bind is alice_engine
    assert alice_session.bind is not bob_engine

    # Alice cannot see Bob's table -- distinct engine, distinct file.
    with pytest.raises(Exception, match="no such table"):
        alice_session.execute(text("SELECT * FROM gap_n_b_private")).fetchall()

    # ... and closing Alice's connection does not change that. Whether the
    # close revokes the session (see the N1 xfail) or the engine
    # resurrects, the one thing that must never happen is Alice reaching
    # Bob's tables -- so the failure shape is deliberately not pinned.
    manager.close_user_database(alice)
    with pytest.raises(Exception):  # noqa: B017,PT011 - shape not pinned
        alice_session.execute(text("SELECT * FROM gap_n_b_private")).fetchall()
    # close_user_database's dispose() can leave the session's underlying
    # DBAPI connection already closed, in which case Session.close()'s own
    # internal rollback raises the same error instead of no-op'ing --
    # cleanup of a session already known to be broken has to tolerate
    # that, the same "shape not pinned" way the read above does.
    try:
        alice_session.close()
    except Exception:  # noqa: BLE001 - cleanup of an already-dead session
        pass

    # Bob's own accessor still reads his private data.
    bob_session = manager.get_session(bob)
    assert bob_session is not None
    rows = bob_session.execute(
        text("SELECT secret FROM gap_n_b_private WHERE id = 1")
    ).fetchall()
    bob_session.close()
    assert rows and rows[0][0] == "bobs-secret"