Coverage for src/local_deep_research/database/encrypted_db.py: 93%
1111 statements
« prev ^ index » next coverage.py v7.16.2, created at 2026-10-22 21:20 +0000
1"""
2Encrypted database management using SQLCipher.
3Handles per-user encrypted databases with browser-friendly authentication.
4"""
6import hmac
7import secrets
8import errno
9import os
10import stat
11import threading
12import time
13from collections.abc import Generator
14from contextlib import contextmanager
15from contextvars import ContextVar
16from dataclasses import dataclass
17from enum import StrEnum, unique
18from pathlib import Path
19from typing import Any, Dict, Never, Optional, assert_never
21from loguru import logger
22from sqlalchemy import create_engine, event, text
23from sqlalchemy.engine import Engine
24from sqlalchemy.orm import Session, sessionmaker
25from sqlalchemy.pool import QueuePool, StaticPool
27from ..config.paths import get_data_directory, get_user_database_filename
28from ..settings.env_registry import get_env_setting
30# ``redact_secrets`` stays local to the exception handlers that use it. A
31# module-scope import would initialize the broad ``security`` package while
32# encrypted_db is still defining its bootstrap state, unnecessarily widening
33# this low-level module's dependency surface.
34#
35# Historically, ``security/__init__.py`` eagerly re-exported file-integrity
36# symbols, which created this cycle during database bootstrap:
37#
38# database/encrypted_db.py
39# → security/__init__.py
40# → security/file_integrity/integrity_manager.py
41# → database/session_context.py
42# → database/encrypted_db.py (mid-load, ``db_manager`` not yet
43# defined → ImportError)
44#
45# File-integrity exports are now lazy, and test_integrity_bootstrap_guard.py
46# locks in the current fail-loud session-context import. Keeping this import
47# local avoids coupling encrypted_db bootstrap to unrelated security package
48# initialization. Other files in PRs #4168/#4175/#4181 can import
49# ``redact_secrets`` at module scope because they are not on that bootstrap
50# path.
51from .sqlcipher_compat import connect_sqlcipher, get_sqlcipher_module
52from .pool_config import (
53 MAX_OVERFLOW,
54 POOL_PRE_PING,
55 POOL_RECYCLE_SECONDS,
56 POOL_SIZE,
57)
58from .rekey_lock import (
59 DatabaseKeyCheckUnavailableError,
60 RekeyEpochState,
61 RekeyEpochUnreadableError,
62 RekeyIndeterminateError,
63 RekeyLockContentionError,
64 RekeyOperationalError,
65 RekeyRecoveryRequiredError,
66 RekeySidecarIOError,
67 RekeySidecarOperation,
68 RekeyStage,
69 RekeyUnavailableError,
70 acquire_rekey_lock,
71 begin_rekey_epoch,
72 clear_pending_rekey_epoch,
73 commit_rekey_epoch,
74 publish_new_database_epoch,
75 read_or_initialize_rekey_epoch,
76 read_rekey_epoch,
77 read_rekey_epoch_state,
78)
79from .sqlcipher_utils import (
80 set_sqlcipher_key,
81 set_sqlcipher_rekey,
82 apply_cipher_defaults_before_key,
83 apply_sqlcipher_pragmas,
84 apply_performance_pragmas,
85 check_sqlcipher_key,
86 is_operational_sqlite_error,
87 get_sqlcipher_busy_timeout_ms,
88 sqlcipher_busy_timeout_pragma,
89 create_database_salt,
90 get_salt_file_path,
91 has_per_database_salt,
92 get_key_from_password,
93 get_sqlcipher_version,
94 create_sqlcipher_connection,
95)
98@dataclass(frozen=True, slots=True)
99class _ConnectionCacheEntry:
100 engine: Engine
101 verifier: tuple[bytes, bytes]
102 epoch: str
105@dataclass(frozen=True, slots=True)
106class _ConnectionGeneration:
107 username: str
108 engine: Engine
109 verifier: tuple[bytes, bytes] | None
110 epoch: str | None
113@dataclass(frozen=True, slots=True)
114class _CurrentConnectionGeneration:
115 generation: _ConnectionGeneration
118@dataclass(frozen=True, slots=True)
119class _StaleConnectionGenerationDetached:
120 generation: _ConnectionGeneration
123@dataclass(frozen=True, slots=True)
124class _ConnectionGenerationChanged:
125 pass
128@dataclass(frozen=True, slots=True)
129class _ConnectionGenerationMissing:
130 pass
133type _ConnectionGenerationClaim = (
134 _CurrentConnectionGeneration
135 | _StaleConnectionGenerationDetached
136 | _ConnectionGenerationChanged
137 | _ConnectionGenerationMissing
138)
141@dataclass(frozen=True, slots=True)
142class _ConnectionGenerationUnverifiable:
143 """A generation is cached, but its epoch could not be checked.
145 The rekey sidecar could not be opened or locked, or the epoch read
146 failed once it was. Nothing was read, so nothing proves the generation
147 stale: it stays cached, and ``error`` is the typed fault for the
148 callers that ask for it (``raise_unverifiable``).
149 """
151 error: RekeyOperationalError
154@unique
155class UserConnectionStatus(StrEnum):
156 """Outcome of :meth:`DatabaseManager.probe_user_connection`."""
158 # A cached engine that the durable rekey epoch still vouches for.
159 CONNECTED = "connected"
160 # No usable engine: none is cached, or the cached one was stale and has
161 # been detached. Opening the database again needs the password.
162 NOT_CONNECTED = "not-connected"
163 # An engine is cached, but the rekey sidecar or epoch could not be read
164 # (fd exhaustion, EACCES, EIO, ...), or another holder kept the sidecar
165 # past the wait bound. It is kept, and the next successful probe
166 # re-checks it, so the condition is temporary, not a verdict.
167 UNAVAILABLE = "unavailable"
170class DatabaseInitializationError(Exception):
171 """Raised when a per-user database opens but its schema can't be initialised.
173 Distinct from credential / decryption failures (which return ``None``
174 from :py:meth:`DatabaseManager.open_user_database`) so callers — chiefly
175 the login route — can avoid penalising the user's lockout counter and
176 surface a different error message. The credentials are valid; the
177 database state isn't.
178 """
181# How long change_password retries its non-blocking sidecar flock: long
182# enough to ride out other processes' millisecond cache probes, short
183# enough that a genuine cross-process rekey owner still fails promptly.
184_REKEY_CONTENTION_RETRY_SECONDS = 1.0
186# Ceiling on each wait a request-path caller makes for the user's rekey
187# sidecar: a cache claim (lifecycle lock, sidecar gate and flock together),
188# a cold open and the epoch initialization at registration. A holder keeps
189# the sidecar for as long as its work runs (a rekey or cold open of a large
190# database: seconds to minutes) and a stopped process or a hung mount keeps
191# it indefinitely, so an unbounded wait parks every request for the user,
192# and the shared log-drain thread, behind it. Past the bound the caller gets
193# RekeyLockContentionError, which those paths answer as a retryable 503 with
194# Retry-After, like any other sidecar fault. Inside an HTTP request, a wait
195# that gives up also sets a flag (``sidecar_wait_budget``): each later claim
196# then tries once without waiting, until one of them gets the sidecar again.
197# The flag is not a running total of the time waited.
198_SIDECAR_WAIT_TIMEOUT_SECONDS = 5.0
201def _seconds_until(deadline: float) -> float:
202 return max(0.0, deadline - time.monotonic())
205class _SidecarWaitBudget:
206 """The state one ``sidecar_wait_budget`` scope shares among its waits."""
208 __slots__ = ("active", "spent")
210 def __init__(self) -> None:
211 self.active = True
212 # Set when one of the scope's sidecar waits ran out (another holder
213 # kept the sidecar past the bound); cleared when a later one gets it.
214 self.spent = False
217_sidecar_wait_budget: ContextVar[_SidecarWaitBudget | None] = ContextVar(
218 "ldr_sidecar_wait_budget", default=None
219)
222@contextmanager
223def sidecar_wait_budget() -> Generator[None, None, None]:
224 """Share a give-up flag among the rekey sidecar waits in this scope.
226 Entered for every HTTP request (``request_database_scope`` in
227 ``web/dependencies/auth.py``, from ``DatabaseMiddleware``). Each wait
228 is bounded by ``_SIDECAR_WAIT_TIMEOUT_SECONDS``, and one authenticated
229 request makes up to four of them before its route runs:
230 DatabaseMiddleware's probe and its reopen of the database, then
231 ``require_auth``'s probe and its classifying probe. Without the scope a
232 holder that keeps the sidecar would make each of them wait out the
233 bound, about 20 s before a 503 that says ``Retry-After: 5``, on the
234 shared threads that serve every user. In the scope, a wait that gives
235 up sets the ``spent`` flag, and each later claim then tries once
236 without waiting and raises (or reports) ``RekeyLockContentionError``
237 while the sidecar is still held, until one of them gets the sidecar
238 again, which clears the flag. So a request waits out the bound at most
239 once while the sidecar stays held, rather than once per claim.
241 The flag is not a running total of the time waited: a request whose
242 waits each get the sidecar just under the bound clears it every time
243 and can wait close to the full bound for each claim. Waits made
244 outside every scope (the log drain, for one) wait up to the bound each.
246 The flag is shared with every thread the request's context is copied
247 to (``run_db_sync`` and the AnyIO workers that run sync routes and
248 dependencies), so their waits set and clear the same one. When the
249 scope exits, copies retained by background research or indexing jobs
250 stop sharing it and each subsequent claim gets its own bounded wait.
251 """
252 budget = _SidecarWaitBudget()
253 token = _sidecar_wait_budget.set(budget)
254 try:
255 yield
256 finally:
257 # Resetting this ContextVar restores only the current context. A
258 # background worker keeps the same object in its copied context,
259 # so end its lifetime there too, including on request failure.
260 budget.active = False
261 _sidecar_wait_budget.reset(token)
264def _sidecar_wait_seconds() -> float:
265 """How long the current scope's next sidecar wait may last."""
266 budget = _sidecar_wait_budget.get()
267 if budget is not None and budget.active and budget.spent:
268 return 0.0
269 return _SIDECAR_WAIT_TIMEOUT_SECONDS
272def _record_sidecar_wait(*, ran_out: bool) -> None:
273 """Record that a sidecar wait ran out, or that one got the sidecar."""
274 budget = _sidecar_wait_budget.get()
275 if budget is not None and budget.active:
276 budget.spent = ran_out
279@contextmanager
280def _bounded_rekey_lock(db_path: Path) -> Generator[None, None, None]:
281 """Hold the sidecar like ``acquire_rekey_lock``, for a request-path caller.
283 Waits at most ``_sidecar_wait_seconds()`` and records how the wait
284 ended for the scope's later waits (``sidecar_wait_budget``). A
285 ``RekeyLockContentionError`` that the protected body raises is not this
286 wait's and is not recorded.
287 """
288 acquired = False
289 try:
290 with acquire_rekey_lock(db_path, timeout=_sidecar_wait_seconds()):
291 acquired = True
292 _record_sidecar_wait(ran_out=False)
293 yield
294 except RekeyLockContentionError:
295 if not acquired: 295 ↛ 297line 295 didn't jump to line 297 because the condition on line 295 was always true
296 _record_sidecar_wait(ran_out=True)
297 raise
300# Ceiling on the busy-handler wait of a WAL checkpoint that runs while this
301# process holds the user's rekey sidecar gate (the post-rekey TRUNCATE and
302# the TRUNCATE before an engine disposal, which applies the same bound on
303# its few ungated call sites). Every request for that user
304# (is_user_connected, get_session, cold opens) queues on the gate, so a
305# checkpoint that waits out the full connection busy_timeout
306# (``db_config.busy_timeout_ms``, 30 s by default) behind a reader that
307# pins the WAL would stall all of them for that long. Both checkpoints are
308# best-effort -- the committed frames are durable in the WAL either way --
309# so they give up after this bound instead.
310_GATED_CHECKPOINT_BUSY_TIMEOUT_MS = 1000
311# Fixed statement template; only an ``int`` is ever formatted into it.
312_GATED_CHECKPOINT_BUSY_TIMEOUT_PRAGMA = "PRAGMA busy_timeout = {:d}"
315def _gated_checkpoint_busy_timeout_ms() -> int:
316 return min(
317 get_sqlcipher_busy_timeout_ms(), _GATED_CHECKPOINT_BUSY_TIMEOUT_MS
318 )
321def _gated_checkpoint_busy_timeout_pragma() -> str:
322 """Return the ``PRAGMA busy_timeout`` statement for a gated checkpoint.
324 Built like ``sqlcipher_busy_timeout_pragma``: PRAGMA takes no bind
325 parameters, so the bound is coerced with ``int()`` and formatted into a
326 fixed template whose ``{:d}`` spec refuses anything that is not an
327 integer, so the statement can only ever carry an integer literal.
328 """
329 timeout_ms = int(_gated_checkpoint_busy_timeout_ms())
330 return _GATED_CHECKPOINT_BUSY_TIMEOUT_PRAGMA.format(timeout_ms)
333class _RekeyVerificationError(Exception):
334 check: str
336 def __init__(self, check: str) -> None:
337 self.check = check
338 super().__init__(f"rekey verification failed: {check}")
341def _raise_rekey_unavailable(db_path: Path, stage: RekeyStage) -> Never:
342 raise RekeyUnavailableError(db_path, stage) from None
345def _best_effort_chmod(path, mode: int, *, warn: bool = False) -> None:
346 """Tighten permissions on ``path`` to ``mode``, never raising.
348 Permission hardening must NOT be able to break database creation. On
349 filesystems that don't support POSIX chmod — some Docker bind mounts and
350 network/FUSE volumes, notably Docker Desktop on macOS/Windows — os.chmod
351 can raise OSError even for the file's owner. In that case we leave the
352 path at its default (umask) mode rather than failing registration/login.
354 ``warn=True`` surfaces the failure at warning level: a silent downgrade
355 leaves the path group/world-readable, which matters for DB files holding
356 user data (the unencrypted fallback is plaintext). The owner-only
357 directory chmod is defense-in-depth, so it stays at debug.
358 """
359 try:
360 os.chmod(str(path), mode)
361 except OSError:
362 log = logger.warning if warn else logger.debug
363 log(
364 f"Could not set permissions {oct(mode)} on {path}; "
365 "left at filesystem default",
366 exc_info=True,
367 )
370def _create_private_file(path: Path) -> tuple[int, int]:
371 """Create ``path`` as an empty, owner-only regular file, never via a link.
373 ``O_CREAT | O_EXCL`` fails when anything exists at ``path`` — including a
374 symlink, dangling or not, which is exactly the no-follow protection the
375 ``.salt`` file already gets in ``create_database_salt``. ``O_NOFOLLOW`` is
376 added where the platform has it. SQLite and SQLCipher treat the empty
377 file as a fresh database (#6354).
379 Returns the ``(st_dev, st_ino)`` of the file it created, read from the
380 descriptor rather than the name, so ``_assert_reserved_file`` can tell
381 afterwards whether the pathname still refers to this file.
382 """
383 flags = os.O_WRONLY | os.O_CREAT | os.O_EXCL | getattr(os, "O_NOFOLLOW", 0)
384 try:
385 fd = os.open(str(path), flags, 0o600)
386 except OSError as exc:
387 # A platform may report the symlink through O_NOFOLLOW (ELOOP)
388 # before O_EXCL gets to say EEXIST; both mean "something is already
389 # at this path", so callers see one exception type.
390 if exc.errno == errno.ELOOP:
391 raise FileExistsError(
392 errno.EEXIST, "Path is a symlink", str(path)
393 ) from exc
394 raise
395 try:
396 reserved = os.fstat(fd)
397 finally:
398 os.close(fd)
399 return reserved.st_dev, reserved.st_ino
402def _assert_reserved_file(path: Path, reserved: tuple[int, int]) -> None:
403 """Fail when ``path`` no longer names the file that was reserved.
405 This is partial hardening, not a guarantee. The reservation cannot be held
406 across the database open: SQLCipher and SQLite take a pathname, and
407 neither ``sqlcipher3`` nor the standard ``sqlite3`` module can ask for
408 ``SQLITE_OPEN_NOFOLLOW`` -- it is an open flag of the C API, not a URI
409 parameter, and a ``file:...?nofollow=1`` open still follows the link.
411 What the check does catch is a pathname that is still replaced when it
412 runs -- by a symlink, a hard link or a freshly created file -- and the
413 create then fails instead of adopting it (#6378). What it cannot catch is
414 a replacement that is undone again before it runs: a swap to another
415 file, open, and swap back leaves the connection on the other file while
416 the name looks untouched. The protection that does not depend on timing
417 is the refusal of a link that is already there and the exclusive,
418 no-follow creation in :func:`_create_private_file`.
419 """
420 try:
421 stat_result = os.lstat(path)
422 except OSError as exc:
423 raise ValueError(
424 f"Database path {path} disappeared while the database was "
425 "being created; refusing to use it"
426 ) from exc
428 if (
429 stat.S_ISLNK(stat_result.st_mode)
430 or (stat_result.st_dev, stat_result.st_ino) != reserved
431 ):
432 raise ValueError(
433 f"Database path {path} was replaced while the database was "
434 "being created; refusing to use it"
435 )
438def _db_path_is_symlinked(db_path: Path, username: str) -> bool:
439 """True when a user-database pathname is a symlink, which is refused.
441 SQLite and SQLCipher take a pathname and follow it, and neither
442 ``sqlcipher3`` nor the standard library's ``sqlite3`` can ask for
443 ``SQLITE_OPEN_NOFOLLOW`` -- it is an open flag of the C API, not a URI
444 parameter, and a ``file:...?nofollow=1`` open still follows the link. So
445 the link is refused here rather than declined at the open.
447 Replacing ``ldr_user_<hash>.db`` with a link pointing outside the data
448 directory otherwise makes the next open write a fresh multi-megabyte
449 encrypted database at the link's target: the same one-shot primitive the
450 create path refuses (#6354), on every path that opens an existing
451 database (#6493).
453 Called by each entry point that hands this pathname to SQLCipher, so a new
454 one inherits the check by being written next to the others. Only the LEAF
455 is checked: a data directory that is itself a symlink (another volume) is a
456 legitimate deployment and must keep working.
458 The log line carries the pathname and the link target because that is the
459 only actionable evidence an operator has; the directory is 0700, so the
460 disclosure is to someone who can already read it.
461 """
462 if not db_path.is_symlink():
463 return False
464 try:
465 target = os.readlink(db_path)
466 except OSError as exc:
467 target = f"<unreadable: {exc.strerror}>"
468 logger.error(
469 f"Database path for user {username!r} is a symlink: {db_path} -> "
470 f"{target}. Refusing to open it; replace it with the real database "
471 "file."
472 )
473 return True
476def _remove_partial_user_db_files(db_path: Path) -> None:
477 """Remove a half-created user database and all of its sidecar files.
479 Called from ``create_user_database``'s failure paths. The encrypted
480 branch writes a per-database ``.salt`` file (via ``create_database_salt``)
481 *before* the DB itself, and finalization publishes a ``.rekey.epoch``.
482 SQLCipher/WAL can also leave ``-wal``/``-shm`` sidecars. Removing only the
483 ``.db`` — as these cleanup paths used to —
484 orphans the ``.salt``: a retry then hits ``create_database_salt``'s
485 ``FileExistsError`` (it refuses to overwrite to prevent data loss), so the
486 username stays permanently un-registerable even after the orphaned auth
487 row is cleaned up. Delete every artifact so the on-disk state is atomic
488 and the username is reusable on a subsequent retry.
490 ``create_user_database`` calls this from five sites: its four
491 failure-cleanup blocks (structure creation, engine build, migration,
492 finalization) and its salt-creation recovery path (an
493 orphan left by a prior hard-killed create or by pre-#4934 cleanup that
494 removed only the ``.db``). In every case the ``db_path.exists()`` guard at
495 the top of ``create_user_database`` already ran and found no *pre-existing*
496 database, so any ``.db``/``.salt`` present now is this call's own partial
497 work — removing it cannot destroy live data, because a valid encrypted DB
498 always has both the ``.db`` and its ``.salt``.
500 Both the ``-wal``/``-shm`` (WAL mode, the default) and ``-journal``
501 (rollback-journal modes: DELETE/TRUNCATE/PERSIST) sidecars are removed so
502 the cleanup is correct regardless of the configured ``journal_mode``.
503 The stable ``.rekey.lock`` inode is deliberately preserved so processes
504 already waiting on it cannot split into a second lock domain.
506 Never raises — cleanup must not mask the original creation error.
507 """
508 # Derive every artifact path up front, guarded: the "Never raises"
509 # contract has to hold even if a path helper is handed something odd
510 # (e.g. a nameless path, where ``with_name``/``with_suffix`` raise), so a
511 # derivation failure is logged and swallowed rather than propagating and
512 # masking the original creation error.
513 try:
514 db_path = Path(db_path)
515 artifacts = (
516 db_path,
517 get_salt_file_path(db_path),
518 db_path.with_name(db_path.name + "-wal"),
519 db_path.with_name(db_path.name + "-shm"),
520 db_path.with_name(db_path.name + "-journal"),
521 db_path.with_name(f"{db_path.name}.rekey.epoch"),
522 db_path.with_name(f"{db_path.name}.rekey.pending"),
523 )
524 except Exception:
525 logger.warning(
526 f"Could not derive partial user DB artifact paths: {db_path!r}"
527 )
528 return
530 for path in artifacts:
531 try:
532 # nosemgrep: semgrep.rules.path-traversal-risk, reason: Creation failure cleanup receives the generated user DB path and derives only its fixed sidecar names
533 path.unlink(missing_ok=True)
534 except OSError:
535 # Best-effort: a leftover artifact is worth surfacing but must not
536 # mask the original creation error. The path is enough to
537 # diagnose; skip the exception detail per the sensitive-logging
538 # policy.
539 logger.warning(f"Could not remove partial user DB artifact {path}")
542# Upper bounds of ``backup.max_count`` / ``backup.max_age_days``: the
543# ``max_value`` of each in defaults/default_settings.json (a test pins
544# these to that file). The settings UI and import enforce them, but env
545# overrides (LDR_BACKUP_MAX_*) do not, so a value above them is not trusted
546# here and the pre-migration backup then removes nothing.
547_BACKUP_MAX_COUNT_LIMIT = 30
548_BACKUP_MAX_AGE_DAYS_LIMIT = 90
551def _read_backup_retention(engine: Engine) -> Optional[tuple[int, int]]:
552 """Read ``backup.max_count`` / ``backup.max_age_days`` for a user DB.
554 Reads them the way the login backup does (``SettingsManager``, env
555 overrides included), but read-only: nothing is seeded or written into
556 a database that has not been migrated yet. Returns ``None`` when the
557 values cannot be read, are not positive integers, or exceed the
558 settings' own ``max_value``, so the caller can keep every existing
559 backup instead of guessing a limit.
560 """
561 try:
562 from ..settings.manager import SettingsManager
564 with sessionmaker(bind=engine)() as session:
565 sm = SettingsManager(session, seed_defaults=False)
566 max_count = sm.get_setting("backup.max_count", None)
567 max_age_days = sm.get_setting("backup.max_age_days", None)
568 except Exception as e:
569 logger.warning(
570 "Could not read backup retention settings before migration "
571 f"({type(e).__name__}); keeping all existing backups"
572 )
573 return None
575 values = []
576 for value, limit in (
577 (max_count, _BACKUP_MAX_COUNT_LIMIT),
578 (max_age_days, _BACKUP_MAX_AGE_DAYS_LIMIT),
579 ):
580 if isinstance(value, float) and value.is_integer():
581 value = int(value)
582 if (
583 isinstance(value, bool)
584 or not isinstance(value, int)
585 or not 1 <= value <= limit
586 ):
587 logger.warning(
588 "Backup retention settings are not integers within their "
589 "allowed range; keeping all existing backups"
590 )
591 return None
592 values.append(value)
593 return values[0], values[1]
596class DatabaseManager:
597 """Manages encrypted SQLCipher databases for each user."""
599 def __init__(self):
600 self.connections: Dict[str, Engine] = {}
601 # username -> (salt, digest) proving which password opened the cached
602 # engine. `connections` is keyed by username ALONE, so a cache hit
603 # says nothing about the credential presented on THIS call; without a
604 # verifier, returning the cached engine silently accepts any password.
605 # See _password_matches_cached().
606 #
607 # A fast keyed digest is the right primitive here: this is an
608 # in-process equality check, not at-rest storage, and the plaintext
609 # password already lives in memory in session_password_store, so a
610 # slow KDF would add cost without reducing exposure.
611 self._password_verifiers: Dict[str, tuple[bytes, bytes]] = {}
612 self._connection_epochs: dict[str, str] = {}
613 self._verifier_key = secrets.token_bytes(32)
614 self._connections_lock = threading.RLock()
615 # Per-user locks serializing the cold-open (engine build + migration)
616 # so two concurrent first-opens of one user never run alembic against
617 # the same database file at once. Created lazily under
618 # _connections_lock; see open_user_database / _get_init_lock.
619 self._init_locks: Dict[str, threading.Lock] = {}
620 self._rekey_locks: dict[str, threading.Lock] = {}
621 # Per-user gate serializing change_password CALLS against each
622 # other. Deliberately separate from _rekey_locks (the lifecycle
623 # lock ordinary opens/claims take briefly): sharing one lock made
624 # every cache-hit open a potential false "rekey already in
625 # progress" for a concurrent password change. The cross-exclusion
626 # between a rekey and opens is provided by the on-disk sidecar,
627 # which change_password holds across its whole staged sequence.
628 self._password_change_locks: dict[str, threading.Lock] = {}
629 self._data_dir_override: Optional[Path] = None
630 self._initialized_data_dirs: set[Path] = set()
632 # Check SQLCipher availability
633 self.has_encryption = self._check_encryption_available()
635 # ----------------------------------------------------------------
636 # Pool class selection — see ADR-0004
637 #
638 # We use QueuePool (pool_size=20, max_overflow=40,
639 # pool_timeout=10) for production and StaticPool for tests.
640 #
641 # Why pool_size=20:
642 #
643 # 1. SQLCipher + WAL mode can leak file handles when connections
644 # close out of open-order. Fewer pooled connections = fewer
645 # opportunities for out-of-order closes during pool_recycle.
646 # See: https://github.com/sqlcipher/android-database-sqlcipher/issues/6
647 # See: https://github.com/dotnet/efcore/issues/35010
648 #
649 # 2. SQLite serializes all writes through a single file lock.
650 # Multiple pooled connections don't improve throughput — they
651 # just hold FDs (up to 3 per connection in WAL mode).
652 #
653 # 3. The cleanup scheduler periodically calls engine.dispose()
654 # to release all pooled connections, preventing long-lived
655 # handles from accumulating over days of idle operation.
656 #
657 # Why pool_size=20 and not 1: inject_current_user() creates a
658 # QueuePool session on every request via g.db_session. With the
659 # UI polling /api/research/<id>/status every 1-2s plus other
660 # API calls and before_request middleware, pool_size=1
661 # (max_overflow=2, so 3 total) is easily exhausted — causing
662 # 30-second timeouts and PendingRollbackError cascades.
663 # pool_size=20 + max_overflow=40 (60 total) provides ample
664 # headroom for concurrent requests and multiple browser tabs.
665 #
666 # Why not NullPool: SQLCipher's PRAGMA key adds ~0.2ms per
667 # connection open. With 20-30 queries per page load, NullPool
668 # adds a noticeable 4-6ms overhead vs QueuePool's ~1.5ms.
669 # ----------------------------------------------------------------
670 self._use_static_pool = bool(os.environ.get("TESTING"))
671 self._pool_class = StaticPool if self._use_static_pool else QueuePool
673 def _ensure_data_dir(self, path: Path) -> None:
674 """Create and harden one resolved encrypted-database directory."""
675 path = Path(path)
676 with self._connections_lock:
677 if path in self._initialized_data_dirs:
678 return
679 # Lazy import: avoid a security -> ... -> database import cycle at
680 # module load time (this can run during DatabaseManager
681 # construction, which happens early in app bootstrap).
682 from ..security.directory_creation import create_directory
684 create_directory(
685 path,
686 context="encrypted database storage",
687 )
688 # Restrict the per-user DB directory to the owner (0o700).
689 # This covers SQLite WAL/SHM sidecars and plaintext fallback temp
690 # files even when SQLite recreates them with umask permissions.
691 _best_effort_chmod(path, 0o700)
692 self._initialized_data_dirs.add(path)
694 @property
695 def data_dir(self) -> Path:
696 """Return the encrypted-DB directory for the live data root.
698 The default path is resolved on every access so a process that imports
699 ``db_manager`` before ``LDR_DATA_DIR`` is set does not stay bound to
700 the platform default. Explicit assignments use the setter below and
701 remain stable for tests and programmatic callers.
702 """
703 path = self._data_dir_override
704 if path is None:
705 path = get_data_directory() / "encrypted_databases"
706 self._ensure_data_dir(path)
707 return path
709 @data_dir.setter
710 def data_dir(self, value: Path) -> None:
711 """Set an explicit data-directory override for this manager."""
712 path = Path(value)
713 self._data_dir_override = path
714 self._ensure_data_dir(path)
716 def _get_pool_kwargs(self) -> Dict[str, Any]:
717 """Get pool configuration kwargs based on pool type.
719 StaticPool doesn't support pool_size or max_overflow.
720 QueuePool uses moderate sizing to handle concurrent web requests
721 while limiting FD usage. See ADR-0004 for rationale.
722 """
723 if self._use_static_pool:
724 return {}
725 return {
726 "pool_size": POOL_SIZE,
727 "max_overflow": MAX_OVERFLOW,
728 "pool_timeout": 10,
729 "pool_pre_ping": POOL_PRE_PING,
730 "pool_recycle": POOL_RECYCLE_SECONDS,
731 }
733 def _is_valid_encryption_key(self, password: str) -> bool:
734 """
735 Check if the provided password is valid (not None, empty, or whitespace-only).
737 Args:
738 password: The password to check
740 Returns:
741 True if the password is valid, False otherwise
742 """
743 return password is not None and password.strip() != ""
745 def is_user_connected(self, username: str) -> bool:
746 """Check if a user has an active database connection.
748 Thread-safe accessor for external callers.
750 Args:
751 username: The username to check
753 Returns:
754 True if the user has an active connection. False also covers a
755 cached engine that cannot be checked right now because the rekey
756 sidecar or epoch could not be read, or the sidecar stayed held
757 past the wait bound (``_SIDECAR_WAIT_TIMEOUT_SECONDS``; once a
758 request's wait has given up, its later claims try once without
759 waiting until one gets the sidecar again:
760 ``sidecar_wait_budget``); use
761 :meth:`probe_user_connection` to tell that temporary case apart.
762 """
763 with self._acquire_current_connection_generation(
764 username
765 ) as generation:
766 return generation is not None
768 def probe_user_connection(self, username: str) -> UserConnectionStatus:
769 """Classify ``username``'s cached connection, separating a fault.
771 ``is_user_connected`` answers False both when no usable engine is
772 cached and when a cached engine could not be checked because the
773 rekey sidecar could not be opened or locked, or the epoch read
774 failed (an fd-exhaustion spike, EACCES, EIO, another holder keeping
775 the sidecar past the wait bound). The engine is kept in
776 the second case and re-checked by the next probe, so it calls for a
777 retry, not a new login. Request authentication uses this to answer
778 such a fault with a retryable 503 instead of a 401.
780 Never raises a ``RekeyOperationalError``: a fault while releasing
781 the sidecar after the claim is reported as ``UNAVAILABLE`` too.
782 """
783 try:
784 with self._acquire_current_connection_generation(
785 username, raise_unverifiable=True
786 ) as generation:
787 connected = generation is not None
788 except RekeyOperationalError:
789 return UserConnectionStatus.UNAVAILABLE
790 if connected:
791 return UserConnectionStatus.CONNECTED
792 return UserConnectionStatus.NOT_CONNECTED
794 def _check_encryption_available(self) -> bool:
795 """Probe SQLCipher before considering the development fallback.
797 Working SQLCipher always returns True, even when
798 ``LDR_BOOTSTRAP_ALLOW_UNENCRYPTED`` is enabled. The flag is consulted
799 only if the probe fails; it permits plaintext SQLite instead of a
800 startup error. It neither forces plaintext storage nor converts an
801 existing encrypted database.
803 Returning False also removes password verification from database
804 opens: SQLite has no credential to check. This fallback is for
805 isolated local development, not authenticated shared deployments.
806 """
807 try:
808 import os as os_module
809 import tempfile
811 # Test if SQLCipher actually works, not just if it imports
812 with tempfile.NamedTemporaryFile(delete=False) as tmp:
813 tmp_path = tmp.name
815 try:
816 # Try to create a test encrypted database
817 sqlcipher_module = get_sqlcipher_module()
818 sqlcipher = sqlcipher_module.dbapi2
820 conn = connect_sqlcipher(sqlcipher, tmp_path)
821 try:
822 cursor = conn.cursor()
823 # Use creation_mode=True since we're creating a new test database
824 apply_cipher_defaults_before_key(cursor)
825 # Use centralized key setting
826 set_sqlcipher_key(cursor, "testpass")
827 # Apply post-key pragmas (kdf_iter for new DB)
828 apply_sqlcipher_pragmas(cursor, creation_mode=True)
829 apply_performance_pragmas(cursor)
831 # Check SQLCipher version
832 version = get_sqlcipher_version(cursor)
833 if version: 833 ↛ 845line 833 didn't jump to line 845 because the condition on line 833 was always true
834 major = (
835 version.split(".")[0]
836 if "." in version
837 else version[0]
838 )
839 if major.isdigit() and int(major) < 4: 839 ↛ 840line 839 didn't jump to line 840 because the condition on line 839 was never true
840 logger.warning(
841 f"SQLCipher version {version} detected. "
842 "Version 4.x+ is recommended for proper PRAGMA ordering."
843 )
845 cursor.close()
846 # Now use the connection for table operations
847 conn.execute("CREATE TABLE test (id INTEGER PRIMARY KEY)")
848 conn.execute("INSERT INTO test VALUES (1)")
849 result = conn.execute("SELECT * FROM test").fetchone()
851 if result != (1,): 851 ↛ 852line 851 didn't jump to line 852 because the condition on line 851 was never true
852 raise RuntimeError("SQLCipher encryption test failed")
853 logger.info(
854 "SQLCipher available and working - databases will be encrypted"
855 )
856 return True
857 finally:
858 from ..utilities.resource_utils import safe_close
860 safe_close(conn, "SQLCipher test connection")
861 except Exception:
862 logger.warning("SQLCipher module found but not working")
863 raise ImportError("SQLCipher not functional")
864 finally:
865 # Clean up test file
866 try:
867 os_module.unlink(tmp_path)
868 except OSError as e:
869 logger.debug(
870 f"Failed to clean up temp file {tmp_path}: {e}"
871 )
873 except ImportError:
874 # Check if user has explicitly allowed unencrypted databases.
875 # Registry handles deprecated LDR_ALLOW_UNENCRYPTED fallback automatically.
876 allow_unencrypted = get_env_setting(
877 "bootstrap.allow_unencrypted", False
878 )
880 if not allow_unencrypted:
881 # No ``password`` is in scope here (this runs at
882 # bootstrap, before any user authenticates), and the
883 # caught ``ImportError`` is purely about the SQLCipher
884 # package being missing. Still drop the traceback to
885 # keep this file's logging discipline uniform with the
886 # rest of the #4182 sweep: no exception traceback is
887 # written to the production log from this module. The
888 # install-hint message is the user-facing diagnostic;
889 # the traceback adds no value beyond it. ``warning``
890 # rather than ``error``/``exception`` so the custom
891 # "logger.exception in except blocks" lint passes
892 # without re-introducing a traceback render.
893 logger.warning(
894 "SECURITY ERROR: SQLCipher is not installed!\n"
895 "Your databases will NOT be encrypted.\n"
896 "To fix this:\n"
897 "1. Install SQLCipher: sudo apt install sqlcipher libsqlcipher-dev\n"
898 "2. Reinstall project: pdm install\n"
899 "Or use Docker with SQLCipher pre-installed.\n\n"
900 "To explicitly allow unencrypted databases (NOT RECOMMENDED):\n"
901 "export LDR_BOOTSTRAP_ALLOW_UNENCRYPTED=true"
902 )
903 raise RuntimeError(
904 "SQLCipher not available. Set LDR_BOOTSTRAP_ALLOW_UNENCRYPTED=true to proceed without encryption (NOT RECOMMENDED)"
905 )
906 logger.warning(
907 "WARNING: Running with UNENCRYPTED databases!\n"
908 "This means:\n"
909 "- Passwords don't protect data access\n"
910 "- API keys are stored in plain text\n"
911 "- Anyone with file access can read all data\n"
912 "Install SQLCipher for secure operation!"
913 )
914 return False
916 def _get_user_db_path(self, username: str) -> Path:
917 """Get the path for a user's encrypted database.
919 Raises:
920 ValueError: Propagated from ``get_user_database_filename`` if
921 ``username`` is empty/None -- that's the actual sha256
922 chokepoint, so this method fails closed the same way rather
923 than resolving to a phantom shared ``sha256("")`` filename.
924 Registration already enforces a non-empty username; this is
925 defense-in-depth at the path-resolution chokepoint.
926 """
927 return self.data_dir / get_user_database_filename(username)
929 def _apply_pragmas(self, connection, connection_record):
930 """Apply pragmas for optimal performance."""
931 # Check if this is SQLCipher or regular SQLite
932 is_encrypted = self.has_encryption
934 # Use centralized performance pragma application
936 apply_performance_pragmas(connection)
938 # SQLCipher-specific pragmas
939 if is_encrypted: 939 ↛ 940line 939 didn't jump to line 940 because the condition on line 939 was never true
940 from .sqlcipher_utils import get_sqlcipher_settings
942 settings = get_sqlcipher_settings()
943 pragmas = [
944 f"PRAGMA kdf_iter = {settings['kdf_iterations']}",
945 f"PRAGMA cipher_page_size = {settings['page_size']}",
946 ]
947 for pragma in pragmas:
948 try:
949 # nosemgrep: semgrep.rules.sql-string-concatenation, reason: Both PRAGMA values come from get_sqlcipher_settings numeric range and page-size validation
950 connection.execute(pragma)
951 except Exception as e:
952 logger.debug(f"Could not apply pragma '{pragma}': {e}")
953 else:
954 # Regular SQLite pragma
955 try:
956 connection.execute(
957 "PRAGMA mmap_size = 268435456"
958 ) # 256MB memory mapping
959 except Exception as e:
960 logger.debug(f"Could not apply mmap_size pragma: {e}")
962 @staticmethod
963 def _make_sqlcipher_connection(
964 db_path: Path,
965 password: str,
966 isolation_level: Optional[str] = "IMMEDIATE",
967 check_same_thread: bool = False,
968 ) -> Any:
969 """Create a properly initialized SQLCipher connection.
971 Follows the canonical SQLCipher initialization order: set key,
972 apply cipher pragmas, verify, then apply performance pragmas. The
973 busy timeout alone is set before verification, so a lock held
974 elsewhere is waited out instead of failing the key check.
975 Cipher pragmas (page size, HMAC algorithm, KDF iterations) must
976 be configured before the first query (verification) because that
977 query triggers page decryption with the active cipher settings.
979 Args:
980 db_path: Path to the database file
981 password: The database encryption passphrase
982 isolation_level: SQLite isolation level (``""`` for deferred
983 transactions, ``None`` for autocommit)
984 check_same_thread: SQLite check_same_thread flag
986 Returns:
987 A raw ``sqlcipher3`` connection ready for use.
989 Raises:
990 ValueError: If the database key cannot be verified.
991 """
992 sqlcipher3 = get_sqlcipher_module()
993 conn = connect_sqlcipher(
994 sqlcipher3,
995 str(db_path),
996 isolation_level=isolation_level,
997 check_same_thread=check_same_thread,
998 )
999 cursor = conn.cursor()
1001 try:
1002 set_sqlcipher_key(cursor, password, db_path=db_path)
1003 apply_sqlcipher_pragmas(cursor, creation_mode=False)
1004 # Wait out a lock held by another connection before the
1005 # verification read: with only the driver's shorter default
1006 # wait, a busy read failed verification and a CORRECT password
1007 # was reported as wrong (and, during pending-rekey recovery,
1008 # counted toward the login lockout).
1009 busy_timeout_sql = sqlcipher_busy_timeout_pragma()
1010 cursor.execute(busy_timeout_sql)
1012 if not check_sqlcipher_key(cursor):
1013 raise ValueError("Failed to verify database key") # noqa: TRY301 — cleanup in except before re-raise
1015 apply_performance_pragmas(cursor)
1016 except Exception as error:
1017 try:
1018 cursor.close()
1019 except Exception: # noqa: BLE001
1020 logger.warning("Failed to close cursor during cleanup")
1021 from ..utilities.resource_utils import safe_close
1023 safe_close(conn, "encrypted DB connection")
1024 # A lock held past the busy timeout (or an I/O error) says
1025 # nothing about the password: it must not become the ValueError
1026 # every caller reads as a wrong password.
1027 if is_operational_sqlite_error(error):
1028 raise DatabaseKeyCheckUnavailableError(
1029 db_path, type(error).__name__
1030 ) from None
1031 raise
1033 cursor.close()
1034 return conn
1036 def create_user_database(self, username: str, password: str) -> Engine:
1037 """Create a new database for a user (encrypted, or the unencrypted
1038 fallback when SQLCipher is unavailable).
1040 In the plaintext fallback, accepting a nonblank ``password`` does
1041 not establish a credential for later logins. Subsequent database
1042 opens do not verify it; see :meth:`open_user_database`.
1044 Serialized per user by the same init lock that guards cold opens:
1045 two concurrent creates for one username used to both pass the
1046 ``db_path.exists()`` guard, and the loser's orphaned-salt recovery
1047 then unlinked the winner's in-flight ``.db``/``.salt`` (#6355).
1048 """
1050 # Validate the encryption key
1051 if not self._is_valid_encryption_key(password):
1052 logger.error(
1053 f"Invalid encryption key for user {username}: password is None or empty"
1054 )
1055 raise ValueError(
1056 "Invalid encryption key: password cannot be None or empty"
1057 )
1059 # Everything up to and including the cache publish runs under the
1060 # lock, as in _open_user_database_cold: a concurrent first open of
1061 # this user then waits here instead of migrating the same file at
1062 # the same time as the create.
1063 with self._get_init_lock(username):
1064 db_path, engine, reserved = self._create_user_database_files(
1065 username, password
1066 )
1067 self._initialize_new_user_database(
1068 username, password, db_path, engine, reserved
1069 )
1071 logger.info(f"Created encrypted database for user {username}")
1072 return engine
1074 def _initialize_new_user_database(
1075 self,
1076 username: str,
1077 password: str,
1078 db_path: Path,
1079 engine: Engine,
1080 reserved: tuple[int, int],
1081 ) -> None:
1082 """Migrate a just-created user database and publish its engine.
1084 Called by ``create_user_database`` while holding this user's init
1085 lock. Removes the partial database when the migrations fail.
1087 ``reserved`` identifies the file ``_create_user_database_files``
1088 materialized, so the first real open below -- the unencrypted
1089 branch's first, since SQLAlchemy opens lazily -- is checked against it
1090 before the migrations write anything.
1091 """
1092 # Initialize database tables using centralized initialization
1093 from .initialize import initialize_database
1095 # Mirror of the fail-loud change #3635 made to open_user_database.
1096 # Previously this swallowed the exception with "tables exist but
1097 # schema version not stamped — migrations will be retried on next
1098 # process restart". That left a half-broken DB on disk: tables
1099 # present, no alembic_version row. The next login then re-ran
1100 # alembic, hit the same error, and (post-#3635) 503'd — so the
1101 # user could register but never log in again. Better to fail
1102 # registration loudly with the partial DB removed, so the real
1103 # cause (e.g. world-writable migrations dir) gets fixed instead
1104 # of producing a permanently-locked-out account.
1105 try:
1106 # SQLAlchemy opens the file lazily, so for the unencrypted branch
1107 # this is the first real open. It is checked before the migrations
1108 # write anything, and the pool hands the checked connection back
1109 # to the session below instead of opening the pathname again.
1110 with engine.connect():
1111 _assert_reserved_file(db_path, reserved)
1112 Session = sessionmaker(bind=engine)
1113 with Session() as session:
1114 initialize_database(engine, session)
1115 # Again afterwards, for a replacement made while they ran.
1116 _assert_reserved_file(db_path, reserved)
1117 except Exception as e:
1118 # ``password`` is in scope and was passed into the engine
1119 # creator closure above. Drop the traceback to avoid leaking
1120 # frame locals under ``diagnose=True`` and redact the
1121 # password from str(e) defensively.
1122 from ..security.log_sanitizer import redact_secrets
1124 safe_msg = redact_secrets(str(e), password)
1125 logger.warning(
1126 f"Database migration failed for {username} during creation"
1127 f" — removing partial DB: {safe_msg}"
1128 )
1129 engine.dispose()
1130 # Remove the partial DB *and* its salt/WAL/journal sidecars so a
1131 # retry of the same username isn't blocked by a leftover salt.
1132 _remove_partial_user_db_files(db_path)
1133 raise
1135 # Restrict DB file to owner-only (0o600). The encrypted branch
1136 # chmod's right after create_sqlcipher_connection, but the
1137 # unencrypted fallback creates the file lazily on first connect
1138 # (during initialize_database above), so it is only guaranteed to
1139 # exist here. This file holds PLAINTEXT user data — leaving it at
1140 # umask-default perms (commonly 0o644) would expose it to other
1141 # local accounts.
1142 if not self.has_encryption and db_path.exists():
1143 _best_effort_chmod(db_path, 0o600, warn=True)
1145 # Store connection AFTER migrations complete. The sidecar keeps epoch
1146 # initialization ordered with any cross-process cold-open or rekey.
1147 # Its wait is bounded (and, once a request's wait has given up, the
1148 # request's later claims try once without waiting until one gets the
1149 # sidecar again: sidecar_wait_budget): a holder past the bound fails
1150 # the creation below, which removes the partial database.
1151 try:
1152 with _bounded_rekey_lock(db_path):
1153 # A fresh epoch, never one left beside an earlier database
1154 # at this path (see publish_new_database_epoch).
1155 epoch = publish_new_database_epoch(db_path)
1156 entry = self._make_connection_cache_entry(
1157 engine, password, epoch
1158 )
1159 replaced = self._cache_connection(username, entry)
1160 self._dispose_detached_connection(username, replaced)
1161 except Exception:
1162 detached = self._detach_connection(username)
1163 self._dispose_detached_connection(username, detached)
1164 if detached is not engine: 1164 ↛ 1166line 1164 didn't jump to line 1166 because the condition on line 1164 was always true
1165 self._dispose_detached_connection(username, engine)
1166 _remove_partial_user_db_files(db_path)
1167 raise
1169 def _create_user_database_files(
1170 self, username: str, password: str
1171 ) -> tuple[Path, Engine, tuple[int, int]]:
1172 """Create the on-disk database and its engine; caller holds the init lock.
1174 Returns the database path, an engine whose tables exist but whose
1175 migrations have not run yet, and the identity of the file this method
1176 materialized — see :func:`_assert_reserved_file`.
1177 """
1178 db_path = self._get_user_db_path(username)
1180 # ``Path.exists()`` follows symlinks and is False for a dangling one,
1181 # so a planted ``ldr_user_<hash>.db -> /elsewhere`` link passed this
1182 # guard and the create wrote the database at the link target, outside
1183 # the data dir (#6354). A user database is always a regular file this
1184 # code created, so refuse any link, dangling or not.
1185 if _db_path_is_symlinked(db_path, username):
1186 raise ValueError(
1187 f"Database path for user {username} is a symlink; "
1188 "refusing to create"
1189 )
1191 if db_path.exists():
1192 raise ValueError(f"Database already exists for user {username}")
1194 # Create connection string - use regular SQLite when SQLCipher not available
1195 if self.has_encryption:
1196 # Create directory if it doesn't exist
1197 from ..security.directory_creation import create_directory
1199 create_directory(
1200 db_path.parent,
1201 context="per-user encrypted database directory",
1202 )
1204 # Create per-database salt for new databases (v2 security improvement).
1205 #
1206 # A pre-existing salt here is orphaned from its primary DB: the
1207 # ``db_path.exists()`` guard above already established that no
1208 # database file exists, and a valid encrypted DB always has BOTH
1209 # the ``.db`` and its ``.salt``. Such an orphan is left by a create
1210 # that died before completing — a hard process death (SIGKILL/OOM/
1211 # power loss) mid-creation, or the pre-#4934 cleanup code that
1212 # removed only the ``.db``. Without recovery, ``create_database_salt``
1213 # raises ``FileExistsError`` (it refuses to overwrite to protect
1214 # live data) on every retry, so the username stays permanently
1215 # un-registerable. Remove the stale artifacts and create fresh;
1216 # no live primary database at this path depends on the salt.
1217 #
1218 # New backups retain their own matching salt. Backups created
1219 # before that change can still depend on this source salt, so an
1220 # operator recovering an older backup must preserve it if the
1221 # source .db was removed out of band. The app does not remove a
1222 # live .db while retaining its salt (there is no in-app restore).
1223 #
1224 # SAFETY INVARIANT: this deletion is safe only while a salt-without-
1225 # a-.db always means garbage. That holds today because create_user_
1226 # database is the sole salt writer/caller and nothing restores or
1227 # imports a .db from a salt. If an in-app restore/import that stages
1228 # a .salt ahead of its .db is ever added, RE-EVALUATE this branch —
1229 # it could delete a salt whose database is legitimately incoming.
1230 try:
1231 create_database_salt(db_path)
1232 except FileExistsError:
1233 logger.warning(
1234 f"Removing orphaned salt (no matching database) for "
1235 f"{username} before creating a fresh one"
1236 )
1237 _remove_partial_user_db_files(db_path)
1238 create_database_salt(db_path)
1239 logger.info(f"Created per-database salt for {username}")
1241 # Create database structure using raw SQLCipher outside SQLAlchemy
1242 try:
1243 # Pre-derive key before closures to avoid capturing plaintext
1244 # password. Kept inside the try so a derivation failure here
1245 # also triggers the partial-file cleanup below, rather than
1246 # orphaning the just-created salt file.
1247 hex_key = get_key_from_password(password, db_path=db_path).hex()
1249 # Materialize the .db ourselves, no-follow and exclusive, so
1250 # a link planted between the guard above and this open cannot
1251 # redirect SQLCipher's create either.
1252 reserved = _create_private_file(db_path)
1254 conn = create_sqlcipher_connection(
1255 db_path,
1256 password=password,
1257 creation_mode=True,
1258 connect_kwargs={
1259 # DEFERRED (empty string) so pure-SELECT transactions
1260 # acquire only SQLite's SHARED lock, letting WAL-mode
1261 # concurrent readers proceed while a writer is active.
1262 # IMMEDIATE was previously set "defensively" and made
1263 # every transaction (even reads) take a RESERVED lock,
1264 # which was the single biggest contention source on
1265 # the login-hang path. Race-prone check-then-insert
1266 # call sites were made race-free at the application
1267 # layer in the preceding prerequisite PR.
1268 "isolation_level": "",
1269 "check_same_thread": False,
1270 },
1271 )
1272 # The open above is where a swapped pathname would have
1273 # taken effect, so this is the first point the reservation
1274 # can be checked against what SQLCipher actually opened.
1275 # The check and the chmod run INSIDE the try below: a
1276 # refusal must close the raw connection the open just
1277 # returned, not leak it past the block that owns it (#6608).
1278 try:
1279 _assert_reserved_file(db_path, reserved)
1280 _best_effort_chmod(db_path, 0o600, warn=True)
1282 # Get the CREATE TABLE statements from SQLAlchemy models
1283 from sqlalchemy.dialects import sqlite
1284 from sqlalchemy.schema import CreateIndex, CreateTable
1286 from .models import Base
1288 # Indexes must be emitted explicitly — SQLAlchemy compiles
1289 # `index=True`/`unique=True` and `Index(...)` to separate
1290 # CREATE [UNIQUE] INDEX statements, not inline.
1291 sqlite_dialect = sqlite.dialect()
1292 for table in Base.metadata.sorted_tables:
1293 if table.name == "users":
1294 continue
1295 create_sql = str(
1296 CreateTable(table, if_not_exists=True).compile(
1297 dialect=sqlite_dialect
1298 )
1299 )
1300 logger.debug(f"Creating table {table.name}")
1301 conn.execute(create_sql)
1302 for index in table.indexes:
1303 index_sql = str(
1304 CreateIndex(index, if_not_exists=True).compile(
1305 dialect=sqlite_dialect
1306 )
1307 )
1308 conn.execute(index_sql)
1310 conn.commit()
1311 finally:
1312 from ..utilities.resource_utils import safe_close
1314 safe_close(conn, "user DB setup connection")
1316 logger.info(
1317 f"Database structure created successfully for {username}"
1318 )
1320 except Exception as e:
1321 # ``password`` is in lexical scope (function parameter) and
1322 # is passed into ``create_sqlcipher_connection`` /
1323 # ``set_sqlcipher_key`` above. A traceback rendered with
1324 # loguru ``diagnose=True`` would dump frame locals — which
1325 # includes the plaintext SQLCipher master password. Use
1326 # ``logger.warning`` to drop the traceback chain and
1327 # redact the password from str(e) for defense in depth.
1328 # SQLCipher master passwords are unrecoverable (see
1329 # TRUST.md §5) so the impact of a leak is permanent.
1330 from ..security.log_sanitizer import redact_secrets
1332 safe_msg = redact_secrets(str(e), password)
1333 logger.warning(f"Error creating database structure: {safe_msg}")
1334 # Remove the partial DB *and* its salt/WAL/journal sidecars so
1335 # a retry of the same username isn't blocked by a leftover salt.
1336 _remove_partial_user_db_files(db_path)
1337 raise
1339 # Small delay to ensure file is fully written
1340 import time
1342 time.sleep(0.1)
1344 # Now create SQLAlchemy engine using custom connection creator
1345 def create_engine_connection():
1346 """Create a properly initialized SQLCipher connection."""
1347 return create_sqlcipher_connection(
1348 db_path,
1349 hex_key=hex_key,
1350 creation_mode=False,
1351 connect_kwargs={
1352 # DEFERRED (empty string) so pure-SELECT transactions
1353 # acquire only SQLite's SHARED lock, letting WAL-mode
1354 # concurrent readers proceed while a writer is active.
1355 # IMMEDIATE was previously set "defensively" and made
1356 # every transaction (even reads) take a RESERVED lock,
1357 # which was the single biggest contention source on
1358 # the login-hang path. Race-prone check-then-insert
1359 # call sites were made race-free at the application
1360 # layer in the preceding prerequisite PR.
1361 "isolation_level": "",
1362 "check_same_thread": False,
1363 },
1364 )
1366 # Create engine with custom creator function and optimized cache.
1367 # The .db and .salt already exist on disk (structure creation
1368 # succeeded above). This step sits between the structure-creation
1369 # and migration cleanup blocks; without its own cleanup a failure
1370 # here would orphan those files, and unlike every other failure
1371 # path it would NOT be self-healing — the retry trips the
1372 # db_path.exists() guard and bricks the username. So clean up on
1373 # any escape. (create_engine is lazy so this is near-unreachable,
1374 # but it closes the one gap in the otherwise-uniform coverage.)
1375 try:
1376 engine = create_engine(
1377 "sqlite://",
1378 creator=create_engine_connection,
1379 # Match the creator's DBAPI so SQLAlchemy can wrap errors.
1380 module=get_sqlcipher_module().dbapi2,
1381 poolclass=self._pool_class,
1382 echo=False,
1383 hide_parameters=True,
1384 query_cache_size=1000,
1385 **self._get_pool_kwargs(),
1386 )
1387 except Exception:
1388 _remove_partial_user_db_files(db_path)
1389 raise
1390 else:
1391 logger.warning(
1392 f"SQLCipher not available - creating UNENCRYPTED database for user {username}"
1393 )
1394 # Fall back to regular SQLite with query cache. Materialize the
1395 # file no-follow and exclusive first, as the encrypted branch
1396 # does, so SQLite's lazy create cannot follow a planted link.
1397 reserved = _create_private_file(db_path)
1398 # The file now exists, so a failure here must clean it up or the
1399 # retry trips the db_path.exists() guard (same reasoning as the
1400 # encrypted branch's engine cleanup above).
1401 try:
1402 engine = create_engine(
1403 f"sqlite:///{db_path}",
1404 connect_args={"check_same_thread": False, "timeout": 30},
1405 poolclass=self._pool_class,
1406 echo=False,
1407 hide_parameters=True,
1408 query_cache_size=1000,
1409 **self._get_pool_kwargs(),
1410 )
1411 # For unencrypted databases, just apply pragmas. Registered
1412 # here, next to the create_engine it belongs to, so the
1413 # engine never leaves this block unconfigured and the
1414 # branch-level guard in test_migration_chain_integrity can
1415 # see the listener for this call.
1416 event.listen(engine, "connect", self._apply_pragmas)
1417 except Exception:
1418 _remove_partial_user_db_files(db_path)
1419 raise
1421 # Tables have already been created using raw SQLCipher above
1422 # No need to create them again with SQLAlchemy
1424 return db_path, engine, reserved
1426 def _compute_verifier_digest(self, salt: bytes, password: str) -> bytes:
1427 """Keyed digest binding ``password`` to a per-entry ``salt``.
1429 A fast keyed HMAC, deliberately not a slow KDF: this is an in-process
1430 equality check, never persisted, and the plaintext password already
1431 lives in memory in session_password_store, so a KDF would add cost
1432 without reducing exposure.
1434 NOTE: CodeQL py/weak-sensitive-data-hashing flags the hash below as
1435 weak password hashing. It is a reviewed FALSE POSITIVE: this app stores
1436 no password hashes at rest (SQLCipher's PBKDF2 is the real KDF on the
1437 cold-open path); this keyed digest only proves, in memory, that the SAME
1438 password opened the cached engine, so a slow KDF here would add login
1439 latency without reducing exposure. The py/weak-sensitive-data-hashing
1440 alert on ``_compute_verifier_digest`` (previously #8140; it may reopen
1441 under a new number after merge) must be dismissed per-alert in code
1442 scanning as a false positive (an HMAC verifier, not password storage)
1443 once main is re-scanned, rather than excluded repo-wide (see
1444 .github/codeql/codeql-config.yml and #5621).
1445 """
1446 return hmac.new(
1447 self._verifier_key, salt + password.encode("utf-8"), "sha256"
1448 ).digest()
1450 def _make_verifier(self, password: str) -> tuple[bytes, bytes]:
1451 """Build a ``(salt, digest)`` verifier for ``password``. Takes no lock."""
1452 salt = secrets.token_bytes(16)
1453 return salt, self._compute_verifier_digest(salt, password)
1455 def _make_connection_cache_entry(
1456 self, engine: Engine, password: str, epoch: str
1457 ) -> _ConnectionCacheEntry:
1458 return _ConnectionCacheEntry(
1459 engine=engine,
1460 verifier=self._make_verifier(password),
1461 epoch=epoch,
1462 )
1464 def _cache_connection(
1465 self, username: str, entry: _ConnectionCacheEntry
1466 ) -> Engine | None:
1467 """Publish an engine, password verifier, and durable epoch atomically.
1469 Called only on paths that have ALREADY proved the password by opening
1470 (or creating) the SQLCipher database with it. Storing the engine and
1471 its verifier under a single lock keeps the invariant "a cached
1472 connection always has a matching verifier" true by construction, so a
1473 concurrent open can never observe a connection with a missing or stale
1474 verifier.
1475 """
1476 with self._connections_lock:
1477 replaced = self.connections.get(username)
1478 self.connections[username] = entry.engine
1479 self._password_verifiers[username] = entry.verifier
1480 self._connection_epochs[username] = entry.epoch
1481 return replaced if replaced is not entry.engine else None
1483 def _record_password_verifier(self, username: str, password: str) -> None:
1484 """Arm the verifier for a connection cached by other means.
1486 Prefer :meth:`_cache_connection` when publishing the engine too — it
1487 stores both atomically. This standalone form arms a verifier against an
1488 already-cached engine. Caller must not hold ``_connections_lock``.
1489 """
1490 verifier = self._make_verifier(password)
1491 epoch = read_rekey_epoch(self._get_user_db_path(username))
1492 with self._connections_lock:
1493 self._password_verifiers[username] = verifier
1494 if epoch is None:
1495 self._connection_epochs.pop(username, None)
1496 else:
1497 self._connection_epochs[username] = epoch
1499 def _connection_epoch_matches(
1500 self, username: str, current_epoch: str | None
1501 ) -> bool:
1502 """Compare a disk epoch while the caller holds ``_connections_lock``."""
1503 return (
1504 current_epoch is not None
1505 and self._connection_epochs.get(username) == current_epoch
1506 )
1508 def _detach_connection_locked(self, username: str) -> Engine | None:
1509 """Detach all cache state while the caller holds ``_connections_lock``."""
1510 engine = self.connections.pop(username, None)
1511 self._password_verifiers.pop(username, None)
1512 self._connection_epochs.pop(username, None)
1513 return engine
1515 def _detach_connection(self, username: str) -> Engine | None:
1516 with self._connections_lock:
1517 return self._detach_connection_locked(username)
1519 def _snapshot_connection_generation(
1520 self, username: str
1521 ) -> _ConnectionGeneration | None:
1522 with self._connections_lock:
1523 engine = self.connections.get(username)
1524 if engine is None:
1525 return None
1526 return _ConnectionGeneration(
1527 username,
1528 engine,
1529 self._password_verifiers.get(username),
1530 self._connection_epochs.get(username),
1531 )
1533 def _connection_generation_matches_locked(
1534 self, generation: _ConnectionGeneration
1535 ) -> bool:
1536 return (
1537 self.connections.get(generation.username) is generation.engine
1538 and self._password_verifiers.get(generation.username)
1539 is generation.verifier
1540 and self._connection_epochs.get(generation.username)
1541 == generation.epoch
1542 )
1544 def _detach_connection_generation(
1545 self, generation: _ConnectionGeneration
1546 ) -> bool:
1547 with self._connections_lock:
1548 if not self._connection_generation_matches_locked(generation): 1548 ↛ 1549line 1548 didn't jump to line 1549 because the condition on line 1548 was never true
1549 return False
1550 self._detach_connection_locked(generation.username)
1551 return True
1553 def _claim_connection_generation(
1554 self,
1555 generation: _ConnectionGeneration,
1556 state: RekeyEpochState,
1557 ) -> _ConnectionGenerationClaim:
1558 with self._connections_lock:
1559 if self.connections.get(generation.username) is None:
1560 return _ConnectionGenerationMissing()
1561 if not self._connection_generation_matches_locked(generation):
1562 return _ConnectionGenerationChanged()
1563 if (
1564 state.pending_epoch is not None
1565 or state.committed_epoch is None
1566 or generation.epoch != state.committed_epoch
1567 ):
1568 self._detach_connection_locked(generation.username)
1569 return _StaleConnectionGenerationDetached(generation)
1570 return _CurrentConnectionGeneration(generation)
1572 @contextmanager
1573 def _acquire_current_connection_generation(
1574 self, username: str, *, raise_unverifiable: bool = False
1575 ) -> Generator[_ConnectionGeneration | None, None, None]:
1576 """Yield the epoch-validated cached generation, or None.
1578 The body of the ``with`` runs while this thread holds the user's
1579 lifecycle lock, the in-process sidecar gate and the sidecar flock.
1580 Every other probe for the same user waits on them, including the
1581 single shared log-drain thread (``is_user_connected``), which
1582 persists log entries for ALL users, and the idle-connection sweep
1583 skips the user meanwhile. Keep the body to cache bookkeeping
1584 (snapshot the engine, build a sessionmaker, compare a verifier) and
1585 do database work after the context exits;
1586 ``check_database_integrity`` runs its scans that way.
1588 Other holders of the same sidecar are long by nature: a cold open
1589 (``_open_user_database_cold``, including the pre-migration backup
1590 and alembic migrations) and the rekey inside ``change_password``,
1591 in this process or another one, and a stopped process or a hung
1592 mount holds it indefinitely. So a claim waits for its locks at most
1593 ``_SIDECAR_WAIT_TIMEOUT_SECONDS`` in total, retries after a changed
1594 generation included, and not at all when an earlier wait of the
1595 same request ran out and none has got the sidecar since
1596 (``sidecar_wait_budget``). The lifecycle lock counts against the
1597 same budget, because the claim ahead of this one holds it while that
1598 claim waits for the sidecar. Past the bound the sidecar is one that
1599 could not be locked (``RekeyLockContentionError``).
1601 A cached generation whose sidecar cannot be opened or locked, or
1602 whose epoch read fails, is kept and yields None. With
1603 ``raise_unverifiable`` the typed fault (``RekeySidecarIOError``,
1604 ``RekeyLockContentionError`` or ``RekeyEpochUnreadableError``) is
1605 raised instead, after every lock is released, for the callers that
1606 must not treat it as a miss: ``open_user_database``,
1607 ``probe_user_connection`` and ``get_session``.
1609 Releasing the sidecar afterwards can fail too (unlock or close).
1610 When the body of the ``with`` finished cleanly, that raises
1611 ``RekeySidecarIOError`` for every caller, also when the probe runs
1612 inside a caller's ``except`` block. When the body raised, its own
1613 exception propagates and the release failure is added to it as a
1614 note instead of replacing it.
1615 """
1616 if not username:
1617 # Fail closed BEFORE touching the filesystem: an empty or None
1618 # username must not even resolve a database path
1619 # (test_db_connection_username_trust).
1620 yield None
1621 return
1622 db_path = self._get_user_db_path(username)
1623 deadline = time.monotonic() + _sidecar_wait_seconds()
1624 while True:
1625 generation = self._snapshot_connection_generation(username)
1626 if generation is None:
1627 yield None
1628 return
1630 lifecycle_lock = self._get_rekey_lock(username)
1631 if not lifecycle_lock.acquire(timeout=_seconds_until(deadline)):
1632 # Still held at the deadline, by the claim ahead of this one
1633 # while it waits for the sidecar, or by a disposal under it:
1634 # the sidecar could not be locked in time, the same
1635 # unverifiable outcome as the sidecar fault below.
1636 _record_sidecar_wait(ran_out=True)
1637 fault = RekeyLockContentionError(
1638 db_path.with_name(f"{db_path.name}.rekey.lock")
1639 )
1640 if raise_unverifiable: 1640 ↛ 1642line 1640 didn't jump to line 1642 because the condition on line 1640 was always true
1641 raise fault from None
1642 yield None
1643 return
1644 try:
1645 sidecar_lock = acquire_rekey_lock(
1646 db_path, timeout=_seconds_until(deadline)
1647 )
1648 try:
1649 sidecar_lock.__enter__()
1650 except (
1651 OSError,
1652 RekeyLockContentionError,
1653 RekeySidecarIOError,
1654 ) as error:
1655 # The sidecar cannot be opened or locked (fd exhaustion,
1656 # EROFS/EACCES/ENOSPC, a vanished data directory, or a
1657 # refused planted sidecar -- the lock module normalizes
1658 # all of these to RekeySidecarIOError), or another
1659 # holder kept it past the deadline above
1660 # (RekeyLockContentionError). Nothing can be
1661 # proved about the durable epoch, so THIS request fails
1662 # closed. The callers that treat None as "no usable
1663 # connection" (is_user_connected, which the log sink
1664 # gates on, _password_matches_cached, the metrics
1665 # session's cache hit and the integrity check) see None
1666 # and never have to catch the typed error. The three
1667 # that must not read it as a miss get it raised below
1668 # instead: open_user_database, whose cold path needs
1669 # this same sidecar (so it fails before deriving a
1670 # key); probe_user_connection, which reports it as
1671 # UNAVAILABLE so request authentication answers a
1672 # retryable 503 rather than a 401; and get_session,
1673 # whose None get_settings_manager reads as "this user
1674 # has no database" and answers from the defaults file,
1675 # so a stored strict egress scope would read as the
1676 # default. (The metrics session falls through to
1677 # open_user_database on that None, so it raises the
1678 # typed error too.)
1679 #
1680 # The cached engine is deliberately left in place, not
1681 # evicted: a transient fault (an EMFILE spike) would
1682 # otherwise dispose every user's healthy engine at once.
1683 # Leaving it is safe because the cache never hands an
1684 # engine out without a successful claim: the next probe
1685 # re-takes the sidecar and re-checks the epoch, and a
1686 # rekey committed meanwhile detaches it there.
1687 if isinstance(error, RekeyLockContentionError):
1688 _record_sidecar_wait(ran_out=True)
1689 claim = _ConnectionGenerationUnverifiable(
1690 error
1691 if isinstance(error, RekeyOperationalError)
1692 else RekeySidecarIOError(
1693 db_path, RekeySidecarOperation.ACQUIRE, error.errno
1694 )
1695 )
1696 else:
1697 _record_sidecar_wait(ran_out=False)
1698 # Whatever the body raises (the caller's with-block,
1699 # thrown in at the yield, or the disposal) is handed to
1700 # the lock as a with statement would hand it, so a
1701 # failure to release the sidecar is added to it as a
1702 # note instead of replacing it. Recorded here, not left
1703 # for the lock to infer: after a clean body it would
1704 # see whatever exception a caller is handling (a probe
1705 # made inside an except block) and attach the release
1706 # failure to that instead of raising it.
1707 body_failure: BaseException | None = None
1708 try:
1709 try:
1710 state = read_rekey_epoch_state(db_path)
1711 except (OSError, RekeyEpochUnreadableError) as error:
1712 # The sidecar opened but the epoch read itself
1713 # failed (EMFILE right after the sidecar took
1714 # the last fd, EIO, ...): nothing was read, so
1715 # treat it like the sidecar fault above. THIS
1716 # request sees "not connected" (or the typed
1717 # error, for the three callers above); the cached
1718 # engine is kept and the next successful probe
1719 # re-checks the epoch before any hand-out.
1720 claim = _ConnectionGenerationUnverifiable(
1721 error
1722 if isinstance(error, RekeyEpochUnreadableError)
1723 else RekeyEpochUnreadableError(
1724 db_path, error.errno
1725 )
1726 )
1727 except RekeyRecoveryRequiredError:
1728 # Read, and does not vouch (malformed token,
1729 # pending without committed): detach below.
1730 claim = self._claim_connection_generation(
1731 generation, RekeyEpochState(None, None)
1732 )
1733 else:
1734 claim = self._claim_connection_generation(
1735 generation, state
1736 )
1737 match claim:
1738 case _CurrentConnectionGeneration(
1739 generation=current
1740 ):
1741 yield current
1742 return
1743 case _StaleConnectionGenerationDetached(
1744 generation=stale
1745 ):
1746 self._dispose_detached_connection(
1747 stale.username,
1748 stale.engine,
1749 checkpoint=False,
1750 )
1751 case ( 1751 ↛ 1757line 1751 didn't jump to line 1757 because the pattern on line 1751 always matched
1752 _ConnectionGenerationChanged()
1753 | _ConnectionGenerationMissing()
1754 | _ConnectionGenerationUnverifiable()
1755 ):
1756 pass
1757 case unreachable:
1758 assert_never(unreachable)
1759 except BaseException as failure:
1760 body_failure = failure
1761 raise
1762 finally:
1763 if body_failure is None:
1764 sidecar_lock.__exit__(None, None, None)
1765 else:
1766 sidecar_lock.__exit__(
1767 type(body_failure),
1768 body_failure,
1769 body_failure.__traceback__,
1770 )
1771 finally:
1772 lifecycle_lock.release()
1774 match claim:
1775 case _ConnectionGenerationChanged():
1776 continue
1777 case _ConnectionGenerationUnverifiable(error=fault):
1778 if raise_unverifiable:
1779 raise fault from None
1780 yield None
1781 return
1782 case ( 1782 ↛ 1788line 1782 didn't jump to line 1788 because the pattern on line 1782 always matched
1783 _StaleConnectionGenerationDetached()
1784 | _ConnectionGenerationMissing()
1785 ):
1786 yield None
1787 return
1788 case _CurrentConnectionGeneration():
1789 raise AssertionError(
1790 "current generation escaped its ownership scope"
1791 )
1792 case unreachable:
1793 assert_never(unreachable)
1795 def _verifier_entry_matches(
1796 self, username: str, entry: object, password: str
1797 ) -> bool:
1798 """Compare ``password`` against one ``(salt, digest)`` verifier entry.
1800 Fails CLOSED: a missing entry returns False so the caller falls
1801 through to a real key-validating open. Returning True on a missing
1802 verifier would reopen exactly the hole the verifier closes.
1804 A malformed entry (anything that is not a ``(salt, expected)`` tuple
1805 of bytes, including a two-tuple with wrong-typed members such as
1806 ``("x", "y")`` or ``(b"salt", 123)``) also returns False rather than
1807 propagating. Every caller sits on a cache-hit or cold-open path that
1808 holds the per-user lifecycle locks: a ``TypeError`` / ``ValueError``
1809 escaping from here would surface as an unexplained login or session
1810 failure instead of the ordinary fall-through to a key-validating
1811 open. The invariants that produce well-formed entries
1812 (``_make_verifier`` via ``_make_connection_cache_entry``, and
1813 ``_record_password_verifier``) are unit-locked, so a corrupt entry
1814 can only arrive through external dict mutation; degrading to
1815 no-match is safer than letting that exception escape.
1816 """
1817 if entry is None:
1818 return False
1819 try:
1820 salt, expected = entry # type: ignore[misc]
1821 if not isinstance(salt, bytes) or not isinstance(expected, bytes):
1822 logger.warning(
1823 f"Malformed verifier entry for {username}; treating as no-match"
1824 )
1825 return False
1826 return hmac.compare_digest(
1827 self._compute_verifier_digest(salt, password), expected
1828 )
1829 except (TypeError, ValueError, AttributeError):
1830 logger.warning(
1831 f"Malformed verifier entry for {username}; treating as no-match"
1832 )
1833 return False
1835 def _verifier_matches(self, username: str, password: str) -> bool:
1836 """Lock-free verifier comparison. **Caller must hold _connections_lock.**
1838 Fails CLOSED on a missing or malformed verifier; see
1839 :meth:`_verifier_entry_matches`.
1840 """
1841 return self._verifier_entry_matches(
1842 username, self._password_verifiers.get(username), password
1843 )
1845 def _connection_generation_password_matches(
1846 self, generation: _ConnectionGeneration, password: str
1847 ) -> bool:
1848 """Check ``password`` against a claimed generation's verifier.
1850 Fails CLOSED on a missing or malformed verifier, exactly like
1851 :meth:`_verifier_matches`; see :meth:`_verifier_entry_matches`.
1852 """
1853 if not self.has_encryption:
1854 return True
1855 return self._verifier_entry_matches(
1856 generation.username, generation.verifier, password
1857 )
1859 def _cached_engine_trusted(
1860 self, username: str, password: str, current_epoch: str | None
1861 ) -> bool:
1862 """May the cached engine for ``username`` be returned to a caller
1863 presenting ``password``? **Caller must hold _connections_lock.**
1865 Unencrypted mode has no credential to verify — the cold path opens the
1866 plain-SQLite database with any password too — so the verifier is
1867 meaningless there and would only force needless cold opens against the
1868 placeholder passwords the app uses (session_context /
1869 database_middleware). Trust the cache directly. Encrypted mode requires
1870 a verifier match.
1871 """
1872 if not self._connection_epoch_matches(username, current_epoch):
1873 return False
1874 if not self.has_encryption:
1875 return True
1876 return self._verifier_matches(username, password)
1878 def _password_matches_cached(self, username: str, password: str) -> bool:
1879 """Check ``password`` against the exact current cache generation."""
1880 with self._acquire_current_connection_generation(
1881 username
1882 ) as generation:
1883 return generation is not None and (
1884 self._connection_generation_password_matches(
1885 generation, password
1886 )
1887 )
1889 def _get_init_lock(self, username: str) -> threading.Lock:
1890 """Return the per-user cold-open lock, creating it on first use.
1892 Guards the engine-build + migration in ``open_user_database`` so two
1893 concurrent first-opens of the same user serialize instead of running
1894 migrations against one database file simultaneously. Per-user (not the
1895 global ``_connections_lock``) so different users still open in
1896 parallel.
1897 """
1898 with self._connections_lock:
1899 lock = self._init_locks.get(username)
1900 if lock is None:
1901 lock = threading.Lock()
1902 self._init_locks[username] = lock
1903 return lock
1905 def _get_password_change_lock(self, username: str) -> threading.Lock:
1906 """Return the per-user change_password gate, creating it on first use.
1908 Serializes concurrent change_password CALLS for one user (the loser
1909 gets the typed 409 contention error). Must stay a separate registry
1910 from _rekey_locks: the lifecycle lock is taken briefly by ordinary
1911 opens/claims, so gating on it here would turn every racing open
1912 into a false contention.
1914 The backing dict is reached through ``getattr`` because several
1915 tests build a ``DatabaseManager`` via ``__new__`` and set only the
1916 attributes the method under test touches, so it can legitimately
1917 be absent.
1918 """
1919 with self._connections_lock:
1920 locks = getattr(self, "_password_change_locks", None)
1921 if locks is None:
1922 locks = {}
1923 self._password_change_locks = locks
1924 lock = locks.get(username)
1925 if lock is None:
1926 lock = threading.Lock()
1927 locks[username] = lock
1928 return lock
1930 def _get_rekey_lock(self, username: str) -> threading.Lock:
1931 """Return the dedicated per-user password-rekey lifecycle lock.
1933 Serializes the whole rekey sequence for one user in-process; the
1934 on-disk sidecar lock (``acquire_rekey_lock``) extends the same
1935 exclusion across processes. Deliberately a separate registry from
1936 ``_init_locks``: change-password-era code paths acquire both, and
1937 sharing one non-reentrant lock would self-deadlock.
1939 The backing dict is reached through ``getattr`` because several
1940 tests build a ``DatabaseManager`` via ``__new__`` and set only the
1941 attributes the method under test touches, so it can legitimately
1942 be absent.
1943 """
1944 with self._connections_lock:
1945 locks = getattr(self, "_rekey_locks", None)
1946 if locks is None: 1946 ↛ 1947line 1946 didn't jump to line 1947 because the condition on line 1946 was never true
1947 locks = {}
1948 self._rekey_locks = locks
1949 lock = locks.get(username)
1950 if lock is None:
1951 lock = threading.Lock()
1952 locks[username] = lock
1953 return lock
1955 def open_user_database(
1956 self, username: str, password: str
1957 ) -> Optional[Engine]:
1958 """Open a user's database, verifying the password only with SQLCipher.
1960 With encryption enabled, a cold open validates the derived key
1961 against the database; a cached open requires a matching in-memory
1962 password verifier. An already-open database alone is not proof of
1963 the caller's password.
1965 In the plaintext development fallback, neither cold nor cached
1966 opens verify the account password. The nonblank-input check below
1967 still applies, but a different password can open the same database.
1968 A returned engine in that mode is not evidence of authentication.
1969 Callers must not use this fallback for shared accounts that rely on
1970 LDR password verification without adding independent authentication.
1972 When an engine is cached but its rekey sidecar cannot be opened or
1973 locked, or its epoch cannot be read, the typed fault
1974 (``RekeySidecarIOError`` / ``RekeyLockContentionError`` /
1975 ``RekeyEpochUnreadableError``, all ``RekeyOperationalError``) is
1976 raised before any key is derived, and the engine stays cached. A
1977 cold open whose per-user init lock stays held past
1978 ``_SIDECAR_WAIT_TIMEOUT_SECONDS`` raises ``RekeyLockContentionError``
1979 before the derivation, and one whose sidecar stays held past it
1980 raises it after the derivation (without waiting, in a request whose earlier
1981 wait ran out: ``sidecar_wait_budget``). None stays reserved for a
1982 credential or missing-database verdict.
1983 """
1984 open_start = time.perf_counter()
1986 # Validate the encryption key
1987 if not self._is_valid_encryption_key(password):
1988 logger.error(
1989 f"Invalid encryption key when opening database for user {username}: password is None or empty"
1990 )
1991 # TODO: Fix the root cause - research threads are not getting the correct password
1992 logger.error(
1993 "TODO: This usually means the research thread is not receiving the user's "
1994 "password for database encryption. Need to ensure password is passed from "
1995 "the main thread to research threads."
1996 )
1997 raise ValueError(
1998 "Invalid encryption key: password cannot be None or empty"
1999 )
2001 db_path = self._get_user_db_path(username)
2003 # Check if already open.
2004 #
2005 # `connections` is keyed by username ALONE, so a hit proves only that
2006 # SOMEONE opened this user's database -- not that the caller supplied
2007 # the right password. Returning the engine on a bare hit made login
2008 # accept ANY password for any user with a live cached connection,
2009 # which is the normal state whenever they are logged in anywhere.
2010 # Verify the credential against the one that actually opened it, and
2011 # fall through to a real (key-validating) open when it does not match.
2012 # Claim the engine and check its verifier in one ownership scope so it
2013 # cannot be evicted between validation and return.
2014 #
2015 # raise_unverifiable: when an engine is cached but the rekey sidecar
2016 # cannot be opened or locked, or the epoch cannot be read, raise that
2017 # typed fault here, BEFORE the key derivation below. The cold path
2018 # needs the same sidecar, so it would only fail the same way after a
2019 # full PBKDF2 derivation, and every caller (each request's
2020 # DatabaseMiddleware, socket handshakes, background sessions) would
2021 # pay one per attempt for as long as an fd-exhaustion spike lasts.
2022 # The engine stays cached for the next successful probe. Only an
2023 # existing user can have a cached engine, and the login route
2024 # answers this fault with a 503 that an unknown username never
2025 # gets, so skipping the derivation reveals nothing the status code
2026 # does not already reveal.
2027 with self._acquire_current_connection_generation(
2028 username, raise_unverifiable=True
2029 ) as generation:
2030 if generation is not None and (
2031 self._connection_generation_password_matches(
2032 generation, password
2033 )
2034 ):
2035 return generation.engine
2037 # Serialize the cold-open (engine build + migration) per user so two
2038 # concurrent first-opens never run alembic against the same database
2039 # file at once -- alembic's module-level proxy and the version-row
2040 # UPDATE are not safe under concurrent migration of one DB.
2041 init_lock = self._get_init_lock(username)
2042 recovered_pending_rekey = False
2043 with self._bounded_init_lock(init_lock, db_path):
2044 # Derive the key UNCONDITIONALLY (an absent database must cost
2045 # the same as an existing one -- username-enumeration timing
2046 # parity, see _open_user_database_cold), then refuse symlinked
2047 # and absent databases BEFORE any sidecar file is created: an
2048 # unauthenticated caller probing usernames must not be able to
2049 # materialize on-disk state for a user that does not exist.
2050 prederived_hex_key = get_key_from_password(
2051 password, db_path=db_path
2052 ).hex()
2053 if _db_path_is_symlinked(db_path, username):
2054 return None
2055 if not db_path.exists():
2056 logger.error(f"No database found for user {username}")
2057 return None
2059 # The stable sidecar serializes cold opens with cross-process rekeys.
2060 # Epoch repair and publication happen only while this inode is locked.
2061 # The wait is bounded (once a request's wait has given up, its
2062 # later claims try once without waiting until one gets the
2063 # sidecar again: sidecar_wait_budget): a holder that keeps it
2064 # longer (a rekey of a large database, a stopped process) makes
2065 # this open raise RekeyLockContentionError, which a login
2066 # answers with 503 (not counted toward lockout), a signed-in
2067 # request's reopen (ensure_user_database) with 503 and the
2068 # session kept, and the other callers as any sidecar fault.
2069 with _bounded_rekey_lock(db_path):
2070 # Re-checked under the lock (cheap now): the pre-gate above
2071 # ran without any exclusion.
2072 if _db_path_is_symlinked(db_path, username): 2072 ↛ 2073line 2072 didn't jump to line 2073 because the condition on line 2072 was never true
2073 return None
2074 if not db_path.exists(): 2074 ↛ 2075line 2074 didn't jump to line 2075 because the condition on line 2074 was never true
2075 logger.error(f"No database found for user {username}")
2076 return None
2077 rekey_state = read_rekey_epoch_state(db_path)
2078 if rekey_state.pending_epoch is not None:
2079 pending_engine = self._detach_connection(username)
2080 if pending_engine is not None and not ( 2080 ↛ 2085line 2080 didn't jump to line 2085 because the condition on line 2080 was never true
2081 self._dispose_detached_connection(
2082 username, pending_engine, checkpoint=False
2083 )
2084 ):
2085 raise RekeyRecoveryRequiredError(db_path)
2086 if not self._recover_pending_rekey(
2087 db_path, password, rekey_state
2088 ):
2089 return None
2090 recovered_pending_rekey = True
2091 try:
2092 current_epoch = read_or_initialize_rekey_epoch(db_path)
2093 except OSError as error:
2094 # First open after upgrade publishes the epoch file;
2095 # an I/O failure there (ENOSPC, EIO, mkstemp EACCES)
2096 # is operational, not a credential verdict -- type it
2097 # so the login route answers 503 without counting it
2098 # toward lockout instead of escaping as a raw 500.
2099 raise RekeySidecarIOError(
2100 db_path, RekeySidecarOperation.PUBLISH, error.errno
2101 ) from None
2102 detached = None
2103 with self._connections_lock:
2104 cached = self.connections.get(username)
2105 if cached is not None and self._cached_engine_trusted(
2106 username, password, current_epoch
2107 ):
2108 return cached
2109 if ( 2109 ↛ 2115line 2109 didn't jump to line 2115 because the condition on line 2109 was never true
2110 cached is not None
2111 and not self._connection_epoch_matches(
2112 username, current_epoch
2113 )
2114 ):
2115 detached = self._detach_connection_locked(username)
2116 self._dispose_detached_connection(
2117 username, detached, checkpoint=False
2118 )
2120 engine = self._open_user_database_cold(
2121 username, password, open_start, prederived_hex_key
2122 )
2123 if engine is None:
2124 return None
2126 # Re-read the durable state the password was just proved
2127 # under. No outcome other than the same stable committed
2128 # epoch publishes the engine, and none of the others says
2129 # anything against the credential: a read that fails
2130 # (EMFILE, EIO, persistent EACCES:
2131 # RekeyEpochUnreadableError), durable state that does not
2132 # vouch (a malformed token, a missing committed epoch, or
2133 # a pending epoch), and a different committed epoch (none
2134 # of the last four can appear while this open holds the
2135 # sidecar unless a writer bypassed it). All of them
2136 # propagate as the RekeyOperationalError the reads above
2137 # already raise on this path, so the login route answers
2138 # 503 and leaves the lockout counter alone, instead of
2139 # returning the ``(None, None, False)`` that a wrong
2140 # password returns (401, counted toward lockout).
2141 try:
2142 rechecked_state = read_rekey_epoch_state(db_path)
2143 except RekeyRecoveryRequiredError:
2144 self._dispose_detached_connection(
2145 username, engine, checkpoint=False
2146 )
2147 raise
2148 if (
2149 rechecked_state.pending_epoch is not None
2150 or rechecked_state.committed_epoch is None
2151 ):
2152 self._dispose_detached_connection(
2153 username, engine, checkpoint=False
2154 )
2155 raise RekeyRecoveryRequiredError(db_path)
2156 if rechecked_state.committed_epoch != current_epoch:
2157 # A different committed epoch was read: the engine was
2158 # built under a superseded generation; discard it
2159 # unpublished. Every epoch writer runs under the
2160 # sidecar this open holds, so only a writer that
2161 # bypassed it (an operator editing the epoch file, a
2162 # foreign tool) gets here. The password DID open the
2163 # database, so this is not a wrong password either:
2164 # fail closed with the same RekeyRecoveryRequiredError
2165 # (login answers 503, lockout counter untouched)
2166 # rather than the (None, None, False) that reads as
2167 # invalid credentials (401, counted toward lockout).
2168 self._dispose_detached_connection(
2169 username, engine, checkpoint=False
2170 )
2171 raise RekeyRecoveryRequiredError(db_path)
2173 entry = self._make_connection_cache_entry(
2174 engine, password, current_epoch
2175 )
2176 replaced = self._cache_connection(username, entry)
2177 self._dispose_detached_connection(username, replaced)
2179 # Phase 2 of the RAG vector-store cutover runs OUTSIDE init_lock (only
2180 # on the path that actually did the cold-open — the early returns above
2181 # belong to a thread that already ran it). It re-enters
2182 # open_user_database() via get_user_db_session on a session-cache miss;
2183 # doing it while still holding the non-reentrant init_lock would
2184 # self-deadlock if a concurrent close_user_database() evicted the
2185 # freshly-cached connection between the cache write and that re-entry.
2186 if engine is not None: 2186 ↛ 2192line 2186 didn't jump to line 2192 because the condition on line 2186 was always true
2187 if recovered_pending_rekey:
2188 self._purge_superseded_key_backups(
2189 username, password, expected_epoch=current_epoch
2190 )
2191 self._run_phase2_rekey(username, password)
2192 return engine
2194 def _purge_superseded_key_backups(
2195 self, username: str, password: str, *, expected_epoch: str
2196 ) -> None:
2197 """Delete backups left under the old key by an interrupted rekey.
2199 Runs after an open resolved a pending password change with
2200 ``password``. A change that completed normally purges old-key
2201 backups in the change-password route; one whose outcome was
2202 reported as indeterminate never reached that step, so this is
2203 where the old-key backups go once the active key is proven.
2205 The cold open released its locks before reaching here. Reacquire
2206 the rekey sidecar and require the same stable epoch before deleting
2207 anything: a later password change may already have created backups
2208 under its newer key, which this password would mistake for a
2209 superseded one. Keep the sidecar through classification and deletion,
2210 acquiring the backup service's per-user lock inside it (the same
2211 order as pre-migration backups). A fault only skips this best-effort
2212 cleanup; it never fails the open.
2213 """
2214 try:
2215 from .backup.backup_service import BackupService
2217 db_path = self._get_user_db_path(username)
2218 with _bounded_rekey_lock(db_path):
2219 state = read_rekey_epoch_state(db_path)
2220 if state != RekeyEpochState(expected_epoch, None):
2221 return
2222 backup_service = BackupService(
2223 username=username, password=password
2224 )
2225 # A pinned manager data_dir can differ from the backup
2226 # service's configured directory. The fence must vouch for
2227 # the database whose backups would actually be deleted.
2228 if backup_service.db_path != db_path:
2229 return
2230 purged = backup_service.purge_backups_under_superseded_key()
2231 if purged:
2232 logger.info(
2233 f"Purged {purged} backup(s) under the superseded key "
2234 f"for {username} after pending rekey recovery"
2235 )
2236 except Exception as error:
2237 logger.warning(
2238 f"Backup purge after pending rekey recovery failed for "
2239 f"{username}: {type(error).__name__}"
2240 )
2242 @staticmethod
2243 @contextmanager
2244 def _bounded_init_lock(
2245 init_lock: threading.Lock, db_path: Path
2246 ) -> Generator[None, None, None]:
2247 """Hold a user's init lock, waiting no longer than a sidecar wait.
2249 The init lock's holder derives the key and then waits up to the
2250 sidecar bound itself, so while another holder (a rekey of a large
2251 database, a stopped process) keeps the sidecar, an unbounded wait
2252 here made N concurrent opens fail one after another, each about one
2253 derivation plus one bound later than the previous, while occupying
2254 the shared worker threads that serve every user's requests. Waiting
2255 at most the same bound (and recording a give-up for the request's
2256 later waits, like a sidecar wait) makes them all fail within about
2257 one bound instead. This is only a queue bound: the sidecar acquire
2258 inside stays the authoritative exclusion against a rekey. The same
2259 bound applies when the holder is a sibling cold open of this user
2260 that runs longer (a first-open migration of a large database): the
2261 waiting open answers with the retryable contention error instead of
2262 parking its thread until the migration ends.
2263 """
2264 if not init_lock.acquire(timeout=_sidecar_wait_seconds()):
2265 _record_sidecar_wait(ran_out=True)
2266 raise RekeyLockContentionError(
2267 db_path.with_name(f"{db_path.name}.rekey.lock")
2268 )
2269 try:
2270 yield
2271 finally:
2272 init_lock.release()
2274 def _run_phase2_rekey(self, username: str, password: str) -> None:
2275 """Re-key a user's legacy FAISS indices (uuid-keyed -> DocumentChunk.id
2276 -keyed), once, at login. See vector_stores/legacy_rekey.py.
2278 A fast no-op (one get_setting call) once the per-user marker
2279 (research_library.rag_index_rekeyed_v2) is set, or when the user has no
2280 legacy sidecars — the common case. Wrapped so a re-key problem never
2281 blocks login; it logs its own errors. Must be called AFTER the init_lock
2282 is released (see open_user_database) to avoid a re-entrant self-deadlock.
2283 """
2284 try:
2285 from ..vector_stores.legacy_rekey import rekey_user_indexes
2287 rekey_user_indexes(username, password)
2288 except Exception as e:
2289 # NO rendered traceback + redacted message — the password-redaction
2290 # invariant (tests/security/test_password_redaction_invariant.py).
2291 # `password` (the unrecoverable SQLCipher master key) is live in this
2292 # frame and every frame of the rekey chain, so ANY traceback render
2293 # leaks it under LDR_LOGURU_DIAGNOSE. logger.opt(exception=True) does
2294 # NOT suppress that (it renders identically to logger.exception) and
2295 # also evades the AST guardrail, so log a redacted message with no
2296 # stack, matching every other handler in this file.
2297 from ..security.log_sanitizer import redact_secrets
2299 safe_msg = redact_secrets(str(e), password)
2300 logger.warning(
2301 f"RAG index re-key failed for user {username}: {safe_msg}"
2302 )
2304 def _open_user_database_cold(
2305 self,
2306 username: str,
2307 password: str,
2308 open_start: float,
2309 prederived_hex_key: str | None = None,
2310 ) -> Optional[Engine]:
2311 """Build the engine and run migrations for a not-yet-cached user DB.
2313 Must be called while holding this user's init lock (acquired in
2314 ``open_user_database``) so concurrent first-opens of the same user do
2315 not migrate one database file simultaneously.
2317 ``prederived_hex_key`` lets the caller (which derives the key
2318 unconditionally for username-enumeration timing parity before its
2319 cheap pre-gates) avoid paying the derivation twice.
2320 """
2321 db_path = self._get_user_db_path(username)
2323 # Prevent timing attacks: always derive key before checking file existence
2324 # This ensures both existing and non-existent users take the same amount of time,
2325 # preventing username enumeration via timing analysis.
2326 # Pre-derive key before closures to avoid capturing plaintext password
2327 hex_key = (
2328 prederived_hex_key
2329 if prederived_hex_key is not None
2330 else get_key_from_password(password, db_path=db_path).hex()
2331 )
2333 # After the key derivation, not before: the derivation is deliberately
2334 # unconditional so a non-existent user costs the same as an existing
2335 # one, and a check that returned early would time the difference.
2336 # Before the exists() check below, which follows the link and would
2337 # report a tampered pathname as a missing account.
2338 if _db_path_is_symlinked(db_path, username): 2338 ↛ 2339line 2338 didn't jump to line 2339 because the condition on line 2338 was never true
2339 return None
2341 if not db_path.exists(): 2341 ↛ 2342line 2341 didn't jump to line 2342 because the condition on line 2341 was never true
2342 logger.error(f"No database found for user {username}")
2343 return None
2345 # Warn if this is a legacy database without per-database salt
2346 if self.has_encryption and not has_per_database_salt(db_path):
2347 logger.warning(
2348 f"Database for user '{username}' uses the legacy shared salt "
2349 f"(deprecated). For improved security, consider creating a new "
2350 f"account to get a per-database salt. Legacy databases remain "
2351 f"fully functional but are less resistant to multi-target attacks."
2352 )
2354 # Create connection string - use regular SQLite when SQLCipher not available
2355 if self.has_encryption:
2357 def create_open_connection():
2358 """Create a properly initialized SQLCipher connection."""
2359 return create_sqlcipher_connection(
2360 db_path,
2361 hex_key=hex_key,
2362 creation_mode=False,
2363 connect_kwargs={
2364 # DEFERRED (empty string) so pure-SELECT transactions
2365 # acquire only SQLite's SHARED lock, letting WAL-mode
2366 # concurrent readers proceed while a writer is active.
2367 # IMMEDIATE was previously set "defensively" and made
2368 # every transaction (even reads) take a RESERVED lock,
2369 # which was the single biggest contention source on
2370 # the login-hang path. Race-prone check-then-insert
2371 # call sites were made race-free at the application
2372 # layer in the preceding prerequisite PR.
2373 "isolation_level": "",
2374 "check_same_thread": False,
2375 },
2376 )
2378 # Create engine with custom creator function and optimized cache
2379 engine = create_engine(
2380 "sqlite://",
2381 creator=create_open_connection,
2382 module=get_sqlcipher_module().dbapi2,
2383 poolclass=self._pool_class,
2384 echo=False,
2385 hide_parameters=True,
2386 query_cache_size=1000,
2387 **self._get_pool_kwargs(),
2388 )
2389 else:
2390 logger.warning(
2391 f"SQLCipher not available - opening UNENCRYPTED database for user {username}"
2392 )
2393 # SQLite does not use hex_key. Deriving it above is not password
2394 # verification: this fallback opens the database independently
2395 # of the supplied nonblank password.
2396 engine = create_engine(
2397 f"sqlite:///{db_path}",
2398 connect_args={"check_same_thread": False, "timeout": 30},
2399 poolclass=self._pool_class,
2400 echo=False,
2401 hide_parameters=True,
2402 query_cache_size=1000,
2403 **self._get_pool_kwargs(),
2404 )
2406 # For unencrypted databases, just apply pragmas
2407 event.listen(engine, "connect", self._apply_pragmas)
2409 try:
2410 # Test connection by running a simple query
2411 with engine.connect() as conn:
2412 conn.execute(text("SELECT 1"))
2414 # Run database initialization (creates missing tables and runs migrations)
2415 from .initialize import initialize_database
2417 # Create backup before migration to protect against schema change failures
2418 from .alembic_runner import needs_migration
2420 if needs_migration(engine):
2421 try:
2422 from .backup.backup_service import BackupService
2424 # Apply the user's own retention settings, the same
2425 # ones the login backup uses. The library defaults
2426 # (keep 1, max 7 days) would delete backups the user
2427 # chose to keep. If the settings cannot be read from
2428 # the not-yet-migrated schema, or are not positive
2429 # integers within the settings' maximums, delete
2430 # nothing.
2431 retention = _read_backup_retention(engine)
2432 if retention is None:
2433 result = BackupService(
2434 username=username, password=password
2435 ).create_backup(force=True, prune=False)
2436 else:
2437 max_backups, max_age_days = retention
2438 result = BackupService(
2439 username=username,
2440 password=password,
2441 max_backups=max_backups,
2442 max_age_days=max_age_days,
2443 ).create_backup(force=True)
2444 if result.success:
2445 logger.info(
2446 f"Pre-migration backup created: {result.backup_path}"
2447 )
2448 else:
2449 logger.error(
2450 f"Pre-migration backup failed: {result.error}"
2451 )
2452 except Exception as e:
2453 # ``password`` is in scope and was passed into
2454 # ``BackupService`` above. Drop traceback + redact
2455 # so the upstream error (which can carry the
2456 # password through ``set_sqlcipher_key`` frames) is
2457 # not written to log sinks.
2458 from ..security.log_sanitizer import redact_secrets
2460 safe_msg = redact_secrets(str(e), password)
2461 logger.warning(
2462 f"Pre-migration backup failed — proceeding with migration: {safe_msg}"
2463 )
2465 # Init failures need to be distinguishable from credential
2466 # failures at the call site: the credentials worked (we
2467 # decrypted and ran SELECT 1), but the schema couldn't be
2468 # brought up. Re-raise as a typed error so the login route
2469 # can skip the lockout counter and surface a server-error
2470 # message instead of "Invalid username or password".
2471 try:
2472 initialize_database(engine)
2473 except Exception as init_err:
2474 elapsed_ms = (time.perf_counter() - open_start) * 1000
2475 # ``password`` is in scope (function parameter). The
2476 # initializer ran on an engine whose creator captured
2477 # ``hex_key`` derived from the password — a SQLAlchemy
2478 # ``OperationalError`` traceback could surface frames
2479 # holding the plaintext password under ``diagnose=True``.
2480 from ..security.log_sanitizer import redact_secrets
2482 safe_msg = redact_secrets(str(init_err), password)
2483 logger.warning(
2484 f"Database migration failed for {username} "
2485 f"after {elapsed_ms:.0f}ms — refusing login: {safe_msg}"
2486 )
2487 engine.dispose()
2488 # Sanitised message + ``from None``: ``init_err`` (its
2489 # str() and its chained traceback frames) can carry the
2490 # plaintext password, and the caller that catches this
2491 # typed error logs it with ``logger.exception``
2492 # (thread_local_session) — which would render the chain
2493 # and defeat the redaction applied just above. Break the
2494 # chain per ADR-0003; the redacted detail is already
2495 # logged at this site.
2496 raise DatabaseInitializationError(
2497 f"Database initialisation failed for {username}: {safe_msg}"
2498 ) from None
2500 # NOTE: the phase-2 RAG index re-key does NOT run here. It re-enters
2501 # open_user_database() (via get_user_db_session on a session-cache
2502 # miss), and this method runs while holding the non-reentrant
2503 # per-user init_lock — so a concurrent close_user_database() evicting
2504 # the just-cached connection would make that re-entrant open miss the
2505 # fast-path and self-deadlock on the lock this thread already holds.
2506 # open_user_database() runs it AFTER releasing the lock instead.
2508 elapsed_ms = (time.perf_counter() - open_start) * 1000
2509 if elapsed_ms > 100:
2510 logger.info(
2511 f"Opened encrypted database for user {username} "
2512 f"(cold-open wall clock: {elapsed_ms:.0f}ms)"
2513 )
2514 else:
2515 logger.info(
2516 f"Opened encrypted database for user {username} "
2517 f"({elapsed_ms:.0f}ms)"
2518 )
2519 return engine
2521 except DatabaseInitializationError:
2522 # Already logged + engine disposed at the raise site. Re-raise
2523 # past the catch-all below so callers see the typed error.
2524 raise
2525 except Exception as e:
2526 elapsed_ms = (time.perf_counter() - open_start) * 1000
2527 # Catches connection errors during ``engine.connect()`` —
2528 # the connection creator passes the password into
2529 # ``set_sqlcipher_key`` so a failure there can carry the
2530 # password in the traceback's frame locals. Drop the
2531 # traceback and redact str(e).
2532 from ..security.log_sanitizer import redact_secrets
2534 safe_msg = redact_secrets(str(e), password)
2535 logger.warning(
2536 f"Failed to open database for user {username} "
2537 f"after {elapsed_ms:.0f}ms: {safe_msg}"
2538 )
2539 engine.dispose()
2540 if is_operational_sqlite_error(e):
2541 # Locked or busy past the busy timeout, an I/O error: the
2542 # password was not checked, so this is not the None that
2543 # callers (the login lockout counter) read as a wrong one.
2544 raise DatabaseKeyCheckUnavailableError(
2545 db_path, type(e).__name__
2546 ) from None
2547 return None
2549 def get_session(self, username: str) -> Optional[Session]:
2550 """Bind a Session to ``username``'s ALREADY-open engine. Post-auth accessor.
2552 SECURITY INVARIANT -- this intentionally takes NO password and performs
2553 NO credential check, unlike ``open_user_database`` /
2554 ``create_thread_safe_session_for_metrics`` (which gate their cache hit
2555 through ``_cached_engine_trusted``). That is correct ONLY because this
2556 is a post-authentication accessor, never an authentication entry point:
2558 * It NEVER opens a database -- a cache miss returns ``None``. The
2559 engine it hands back was published to ``connections`` solely by a
2560 credential-verified create/open, so no unverified password can ever
2561 reach the cache via this method.
2562 * Every caller supplies a server-trusted ``username`` -- the signed
2563 Flask-session identity (``session["username"]`` / ``g.current_user``)
2564 or an internal server value (settings-snapshot thread, log drain)
2565 -- and most also gate on ``is_user_connected`` (the never-open-on-
2566 miss property above is the load-bearing guarantee, not that gate).
2567 The username is never an attacker-supplied login attempt paired with
2568 an unverified password, which is the shape that made the cached-
2569 connection bypass exploitable (see ``open_user_database``).
2571 DO NOT add a lazy open-on-miss here, and DO NOT call this from an
2572 auth-decision path: either turns this accessor into an unauthenticated
2573 bypass. Credential-taking entry points must go through
2574 ``open_user_database`` instead.
2576 ``None`` means only that no usable engine is cached: none is, or
2577 the cached one was stale and has just been detached. A cached
2578 engine whose rekey sidecar cannot be opened or locked (within the
2579 wait bound), or whose epoch cannot be read, is not a miss: the typed
2580 ``RekeySidecarIOError`` / ``RekeyLockContentionError`` /
2581 ``RekeyEpochUnreadableError`` is raised
2582 instead (the engine stays cached), as is a failure to release the
2583 sidecar afterwards. ``None`` would fail open here:
2584 ``get_settings_manager`` (``utilities/db_utils.py``) reads it as
2585 "this user has no database" and answers every setting from the
2586 defaults file, so during the fault a stored ``strict`` egress scope
2587 or a custom library ``storage_path`` would read as its default. A
2588 route that leaves the fault unhandled gets the app's retryable 503.
2589 """
2590 with self._acquire_current_connection_generation(
2591 username, raise_unverifiable=True
2592 ) as generation:
2593 if generation is not None:
2594 SessionLocal = sessionmaker(bind=generation.engine)
2595 return SessionLocal()
2596 logger.debug(f"No open database for user {username}")
2597 return None
2599 def get_connected_usernames(self) -> set:
2600 """Return a snapshot of usernames with open connections."""
2601 with self._connections_lock:
2602 return set(self.connections.keys())
2604 def _checkpoint_wal(self, engine, context: str = ""):
2605 """Checkpoint WAL before disposing engine to flush pending writes.
2607 Most callers hold the user's rekey sidecar gate (fenced disposal of
2608 a current generation, cache replacement during a cold open), so the
2609 TRUNCATE's busy wait is bounded by
2610 ``_GATED_CHECKPOINT_BUSY_TIMEOUT_MS`` (see there) for every caller;
2611 the checkpoint is best-effort either way. The pooled connection's
2612 own busy_timeout is restored before it goes back to the pool.
2613 """
2614 try:
2615 with engine.connect() as conn:
2616 gated_timeout_sql = _gated_checkpoint_busy_timeout_pragma()
2617 conn.execute(text(gated_timeout_sql))
2618 try:
2619 # TRUNCATE returns (busy, log_pages, checkpointed_pages).
2620 # busy=1 means a reader/writer held a WAL lock and the
2621 # WAL was NOT truncated — surface that so we can tell
2622 # from logs whether the helper actually shrank the file.
2623 result = conn.execute(
2624 text("PRAGMA wal_checkpoint(TRUNCATE)")
2625 ).fetchone()
2626 finally:
2627 restore_timeout_sql = sqlcipher_busy_timeout_pragma()
2628 conn.execute(text(restore_timeout_sql))
2629 if result and result[0] == 1: 2629 ↛ 2630line 2629 didn't jump to line 2630 because the condition on line 2629 was never true
2630 logger.debug(
2631 f"WAL checkpoint busy {context} — WAL not truncated"
2632 )
2633 except Exception:
2634 logger.debug(f"WAL checkpoint failed {context}", exc_info=True)
2636 @staticmethod
2637 def _require_full_synchronous(connection) -> None:
2638 connection.execute("PRAGMA synchronous = FULL")
2639 result = connection.execute("PRAGMA synchronous").fetchone()
2640 if result != (2,):
2641 raise _RekeyVerificationError("synchronous mode")
2643 @staticmethod
2644 def _pre_rekey_checkpoint(connection) -> None:
2645 """Fold committed WAL frames into the main file; best-effort.
2647 PASSIVE never invokes the busy handler and never waits for a
2648 reader, so a reader pinning the WAL (a research thread's open read
2649 transaction, another worker's request) cannot stall the rekey here
2650 while the sidecar gate is held. Completeness is not required for
2651 correctness: ``PRAGMA rekey`` reads every page through the pager,
2652 WAL included, and the new key is proven afterwards by
2653 ``_verify_rekey_candidate``. A SQLite error still propagates and is
2654 an operational preparation failure.
2655 """
2656 connection.execute("PRAGMA wal_checkpoint(PASSIVE)").fetchone()
2658 @staticmethod
2659 def _best_effort_rekey_checkpoint(connection) -> bool:
2660 """Checkpoint after the new key is proven; True only when complete.
2662 A concurrent reader (another process's open read transaction, a
2663 backup connection) legitimately keeps a TRUNCATE checkpoint from
2664 completing. That is not an ambiguity about which key is active:
2665 the rekey transaction already committed under synchronous=FULL,
2666 so its WAL frames are durable and the next complete checkpoint
2667 folds them in (see ``_verify_rekey_candidate`` for what that
2668 leaves on disk meanwhile). The busy wait is bounded by
2669 ``_GATED_CHECKPOINT_BUSY_TIMEOUT_MS`` because the sidecar gate is
2670 held; the connection is closed right after verification, so its
2671 busy_timeout is not restored.
2672 """
2673 gated_timeout_sql = _gated_checkpoint_busy_timeout_pragma()
2674 connection.execute(gated_timeout_sql)
2675 result = connection.execute(
2676 "PRAGMA wal_checkpoint(TRUNCATE)"
2677 ).fetchone()
2678 return (
2679 result is not None
2680 and len(result) == 3
2681 and result[0] == 0
2682 and result[1] == result[2]
2683 )
2685 @staticmethod
2686 def _warn_rekey_integrity(db_path: Path, check: str, rows) -> None:
2687 """Log an integrity finding loudly without failing the rekey.
2689 Only the number of findings and the first one (SQLite's own
2690 message: page numbers, table and index names, never row data or
2691 key material), truncated, are logged.
2692 """
2693 first = repr(rows[0])[:200] if rows else "no result"
2694 logger.warning(
2695 f"{check} reported {len(rows)} problem(s) for {db_path.name} "
2696 f"after the active key was proven (first: {first}); the key "
2697 "verdict stands, but the database needs an integrity review "
2698 "(see docs/security/rekey-recovery.md)"
2699 )
2701 def _verify_rekey_candidate(self, connection, db_path: Path) -> None:
2702 """Prove that ``connection``'s key is the database's active key.
2704 The proof is that the key decrypts the schema page: SQLCipher
2705 authenticates every page it reads (HMAC), and page 1 is written
2706 under exactly one key, so a database state never opens under both
2707 the old and the new key. Which key is active is decided here, and
2708 only a failure of that proof raises.
2710 The integrity scans after it (``quick_check`` and
2711 ``cipher_integrity_check``) say whether the file is healthy, not
2712 which key it is under. A database that was already damaged (a bad
2713 page elsewhere, an inconsistent schema) fails them under whichever
2714 key is active, so failing the rekey or its recovery on them left
2715 the pending state in place for good and fenced the user out of a
2716 database that opens. They are logged loudly instead.
2717 """
2718 self._require_full_synchronous(connection)
2719 try:
2720 schema = connection.execute(
2721 "SELECT count(*) FROM sqlite_master"
2722 ).fetchone()
2723 except Exception:
2724 raise _RekeyVerificationError("database key") from None
2725 if schema is None: 2725 ↛ 2726line 2725 didn't jump to line 2726 because the condition on line 2725 was never true
2726 raise _RekeyVerificationError("database key")
2727 try:
2728 quick_rows = list(connection.execute("PRAGMA quick_check"))
2729 except Exception as error:
2730 quick_rows = [(type(error).__name__,)]
2731 if not quick_rows or any(row != ("ok",) for row in quick_rows):
2732 self._warn_rekey_integrity(
2733 db_path, "PRAGMA quick_check", quick_rows
2734 )
2735 # The new key is now proven against the committed, durable file, so
2736 # a checkpoint that cannot complete (busy reader) must not make the
2737 # outcome indeterminate -- that stranded users behind a pending
2738 # epoch whose key had in fact changed.
2739 #
2740 # Checkpoint BEFORE the cipher integrity scan: after the rekey
2741 # commit the WAL holds the pages re-encrypted under the NEW key,
2742 # while the main database file still holds them under the OLD key
2743 # until a checkpoint copies the frames back. cipher_integrity_check
2744 # reads the main file directly (not the WAL), so a new-key scan
2745 # reports phantom "HMAC verification failed" rows for every page
2746 # not yet checkpointed. Pinning the canary with a real-SQLCipher
2747 # duel showed the pre-checkpoint scan failing and the
2748 # post-checkpoint scan passing on the identical file. The scan is
2749 # therefore only meaningful (and only run) after a complete
2750 # checkpoint.
2751 try:
2752 checkpointed = self._best_effort_rekey_checkpoint(connection)
2753 except Exception as error:
2754 # A checkpoint that fails (SQLITE_CORRUPT on a damaged page, an
2755 # I/O error) leaves the committed WAL frames in place exactly
2756 # like a busy one; the key verdict above stands.
2757 logger.warning(
2758 f"Post-rekey WAL checkpoint for {db_path.name} failed: "
2759 f"{type(error).__name__}"
2760 )
2761 checkpointed = False
2762 if checkpointed:
2763 try:
2764 cipher_rows = list(
2765 connection.execute("PRAGMA cipher_integrity_check")
2766 )
2767 except Exception as error:
2768 cipher_rows = [(type(error).__name__,)]
2769 if cipher_rows:
2770 self._warn_rekey_integrity(
2771 db_path, "PRAGMA cipher_integrity_check", cipher_rows
2772 )
2773 else:
2774 # Until a later checkpoint completes, the re-encrypted pages
2775 # live only in the WAL and the main database file still holds
2776 # every page under the OLD key (a copy of the main file alone
2777 # opens with the old password). The live database (main file
2778 # plus WAL) already rejects the old key. The window closes at
2779 # the first complete checkpoint after the pinning reader ends:
2780 # SQLite's own checkpoint when the last connection to the file
2781 # closes, the TRUNCATE before any engine disposal (logout, idle
2782 # cleanup, shutdown), or an automatic checkpoint after later
2783 # writes.
2784 logger.warning(
2785 f"Post-rekey WAL checkpoint for {db_path.name} did not "
2786 "complete (a concurrent reader holds the WAL); the new key "
2787 "is verified, but the main database file keeps old-key "
2788 "pages until a later checkpoint folds the WAL in"
2789 )
2790 # No raw fsync of the database or its WAL here, and nowhere else
2791 # while this process may hold a connection to the file: closing ANY
2792 # descriptor to it releases every POSIX advisory (fcntl) lock this
2793 # process holds on that inode, including the SHARED lock SQLite's
2794 # WAL mode relies on (sqlite.org/howtocorrupt.html, section 2.2).
2795 # SQLite's per-inode lock bookkeeping would not notice, so another
2796 # process could checkpoint and delete the WAL underneath a live
2797 # connection here and its later commits would be lost. Durability
2798 # comes from SQLite itself instead: the rekey transaction committed
2799 # under synchronous=FULL (enforced by ``_require_full_synchronous``
2800 # on the old-key connection), which fsyncs the WAL at commit, and a
2801 # complete checkpoint on this synchronous=FULL connection fsyncs
2802 # the main database file.
2804 @staticmethod
2805 def _classify_rekey_failure(
2806 db_path: Path, stage: RekeyStage
2807 ) -> RekeyUnavailableError | RekeyIndeterminateError:
2808 try:
2809 state = read_rekey_epoch_state(db_path)
2810 except Exception:
2811 state = None
2812 if (
2813 stage is RekeyStage.PENDING_PUBLICATION
2814 and state is not None
2815 and state.pending_epoch is None
2816 ):
2817 return RekeyUnavailableError(db_path, stage)
2818 return RekeyIndeterminateError(db_path, stage, state)
2820 @staticmethod
2821 def _clear_committed_rekey_pending(
2822 db_path: Path, pending_epoch: str, username: str
2823 ) -> None:
2824 try:
2825 clear_pending_rekey_epoch(db_path, pending_epoch)
2826 except Exception as error:
2827 logger.warning(
2828 f"Pending rekey cleanup failed for {username}: "
2829 f"{type(error).__name__}"
2830 )
2832 @staticmethod
2833 def _close_rekey_connection(connection, username: str, phase: str) -> None:
2834 try:
2835 connection.close()
2836 except Exception as error:
2837 logger.warning(
2838 f"Rekey {phase} connection close failed for {username}: "
2839 f"{type(error).__name__}"
2840 )
2842 def _recover_pending_rekey(
2843 self,
2844 db_path: Path,
2845 password: str,
2846 state: RekeyEpochState,
2847 ) -> bool:
2848 """Verify the surviving SQLCipher key before finalizing pending state.
2850 Returns False when ``password`` does not open the surviving key: an
2851 ordinary wrong password, which callers must treat exactly like any
2852 other failed credential (the login route counts it toward the
2853 lockout). Only a password that opens the database but whose
2854 verification or epoch publication fails raises
2855 ``RekeyRecoveryRequiredError``.
2856 """
2857 pending_epoch = state.pending_epoch
2858 if pending_epoch is None: 2858 ↛ 2859line 2858 didn't jump to line 2859 because the condition on line 2858 was never true
2859 return True
2860 connection = None
2861 try:
2862 try:
2863 connection = self._make_sqlcipher_connection(db_path, password)
2864 except ValueError:
2865 # Neither surviving key accepted this password; disk and
2866 # pending metadata stay untouched.
2867 return False
2868 self._verify_rekey_candidate(connection, db_path)
2869 if state.committed_epoch != pending_epoch:
2870 commit_rekey_epoch(db_path, pending_epoch)
2871 clear_pending_rekey_epoch(db_path, pending_epoch)
2872 except Exception:
2873 raise RekeyRecoveryRequiredError(db_path) from None
2874 finally:
2875 if connection is not None: 2875 ↛ 2879line 2875 didn't jump to line 2879 because the condition on line 2875 was always true
2876 self._close_rekey_connection(
2877 connection, db_path.name, "recovery"
2878 )
2879 return True
2881 def _resolve_unapplied_rekey(
2882 self, db_path: Path, old_password: str, pending_epoch: str
2883 ) -> Never:
2884 """Classify a rekey whose new key does not open the database.
2886 SQLCipher's ``PRAGMA rekey`` ignores the result of the underlying
2887 rekey: when another connection's write transaction outlasts the
2888 busy timeout it answers ``ok``, raises nothing, and leaves the file
2889 under the OLD key. The new-key open in ``change_password`` is what
2890 catches that (a key that does not match the file fails SQLCipher's
2891 page HMAC), so no epoch is ever committed for a key that did not
2892 take effect.
2894 If the old password still opens and verifies the database, the
2895 rekey provably did not apply: the pending generation is resolved
2896 exactly as a login with the surviving password resolves it
2897 (``_recover_pending_rekey``), and the change fails as unavailable
2898 (retryable; the old password stays valid and no pending state is
2899 left behind). If neither password opens it, the pending generation
2900 is no longer the one this call published, or the recovery itself
2901 fails, the outcome stays indeterminate as before.
2902 """
2903 try:
2904 state = read_rekey_epoch_state(db_path)
2905 old_key_survived = (
2906 state.pending_epoch == pending_epoch
2907 and self._recover_pending_rekey(db_path, old_password, state)
2908 )
2909 except RekeyRecoveryRequiredError:
2910 old_key_survived = False
2911 if old_key_survived: 2911 ↛ 2919line 2911 didn't jump to line 2919 because the condition on line 2911 was always true
2912 logger.warning(
2913 f"PRAGMA rekey did not change the key of {db_path.name} "
2914 "(another connection likely held a write transaction past "
2915 "the busy timeout, or a damaged page stopped it); the old "
2916 "password remains active"
2917 )
2918 raise RekeyUnavailableError(db_path, RekeyStage.PHYSICAL_REKEY)
2919 raise self._classify_rekey_failure(
2920 db_path, RekeyStage.CANDIDATE_VERIFICATION
2921 )
2923 def _dispose_detached_connection(
2924 self,
2925 username: str,
2926 engine: Engine | None,
2927 *,
2928 checkpoint: bool = True,
2929 ) -> bool:
2930 """Dispose an engine outside cache locks, checkpointing when still current."""
2931 if engine is None:
2932 return False
2933 if checkpoint:
2934 self._checkpoint_wal(engine, f"for {username}")
2935 try:
2936 engine.dispose()
2937 except Exception as error:
2938 logger.warning(
2939 f"Failed to dispose engine for {username}: "
2940 f"{type(error).__name__}"
2941 )
2942 return False
2943 return True
2945 def _dispose_connection_generation(
2946 self,
2947 generation: _ConnectionGeneration,
2948 *,
2949 detach: bool,
2950 ) -> bool:
2951 """Dispose one cache generation with epoch and identity fencing."""
2952 db_path = self._get_user_db_path(generation.username)
2953 lifecycle_lock = self._get_rekey_lock(generation.username)
2954 with lifecycle_lock:
2955 try:
2956 with acquire_rekey_lock(db_path):
2957 return (
2958 self._dispose_connection_generation_under_sidecar_lock(
2959 generation, detach=detach
2960 )
2961 )
2962 except (OSError, RekeyLockContentionError, RekeyOperationalError):
2963 # Contention, or the sidecar cannot even be created (the
2964 # user's data directory was removed out from under a still
2965 # cached engine -- test teardown, operator cleanup). Either
2966 # way there is nothing durable left to fence: fall back to
2967 # disposing the engine directly, without a WAL checkpoint
2968 # (checkpointing a vanished database is moot).
2969 if not detach or not self._detach_connection_generation( 2969 ↛ 2972line 2969 didn't jump to line 2972 because the condition on line 2969 was never true
2970 generation
2971 ):
2972 return False
2973 return self._dispose_detached_connection(
2974 generation.username,
2975 generation.engine,
2976 checkpoint=False,
2977 )
2979 def _dispose_connection_generation_under_sidecar_lock(
2980 self,
2981 generation: _ConnectionGeneration,
2982 *,
2983 detach: bool,
2984 ) -> bool:
2985 """Dispose one generation while its database sidecar lock is held."""
2986 db_path = self._get_user_db_path(generation.username)
2987 try:
2988 state = read_rekey_epoch_state(db_path)
2989 except RekeyEpochUnreadableError:
2990 # The sidecar opened but the epoch read itself failed (EMFILE
2991 # right after the sidecar took the last fd, EIO, persistent
2992 # EACCES): nothing was read, so nothing proves this generation
2993 # stale. Propagate it like a sidecar fault, exactly as the
2994 # probe path keeps the engine on the same error: the periodic
2995 # reset skips this generation for this sweep (engine kept,
2996 # nothing disposed), and an explicit close still detaches and
2997 # disposes it, without a WAL checkpoint. Substituting an empty
2998 # state here would detach and dispose every signed-in user's
2999 # engine on one transient read fault during the sweep.
3000 raise
3001 except RekeyRecoveryRequiredError:
3002 # Read, and does not vouch (malformed token, pending without
3003 # committed): the generation is stale, detach it below.
3004 state = RekeyEpochState(None, None)
3005 claim = self._claim_connection_generation(generation, state)
3006 match claim:
3007 case _CurrentConnectionGeneration(generation=current):
3008 if detach and not self._detach_connection_generation(current): 3008 ↛ 3009line 3008 didn't jump to line 3009 because the condition on line 3008 was never true
3009 return False
3010 return self._dispose_detached_connection(
3011 current.username,
3012 current.engine,
3013 checkpoint=True,
3014 )
3015 case _StaleConnectionGenerationDetached(generation=stale):
3016 return self._dispose_detached_connection(
3017 stale.username,
3018 stale.engine,
3019 checkpoint=False,
3020 )
3021 case ( 3021 ↛ 3025line 3021 didn't jump to line 3025 because the pattern on line 3021 always matched
3022 _ConnectionGenerationChanged() | _ConnectionGenerationMissing()
3023 ):
3024 return False
3025 case unreachable:
3026 assert_never(unreachable)
3028 def close_user_database(self, username: str):
3029 """Close a user's database connection."""
3030 generation = self._snapshot_connection_generation(username)
3031 if generation is not None:
3032 if self._dispose_connection_generation(generation, detach=True):
3033 # Per-user init/rekey locks intentionally retain stable identities.
3034 logger.info(f"Closed database for user {username}")
3036 def close_all_databases(self):
3037 """Close all open user database connections and release file locks."""
3038 with self._connections_lock:
3039 detached = [
3040 _ConnectionGeneration(
3041 username,
3042 engine,
3043 self._password_verifiers.get(username),
3044 self._connection_epochs.get(username),
3045 )
3046 for username, engine in self.connections.items()
3047 ]
3048 for generation in detached:
3049 self._dispose_connection_generation(generation, detach=True)
3050 with self._connections_lock:
3051 connected = set(self.connections)
3052 for username in set(self._password_verifiers) - connected:
3053 self._password_verifiers.pop(username, None)
3054 for username in set(self._connection_epochs) - connected: 3054 ↛ 3055line 3054 didn't jump to line 3055 because the loop on line 3054 never started
3055 self._connection_epochs.pop(username, None)
3057 def reset_connection_pools(self) -> int:
3058 """Reset cached pools safely and return the number successfully disposed."""
3059 # _init_locks is deliberately NOT cleared above: a concurrent
3060 # open_user_database may already hold a reference to a user's
3061 # cold-open lock (fetched via _get_init_lock) and be about to
3062 # enter it; dropping the entry would let a later open create a
3063 # SECOND lock for that user, so two cold-opens could migrate one
3064 # DB file at once -- the exact race the lock exists to stop.
3065 # _rekey_locks is likewise retained wholesale -- an entry may be
3066 # held by a change_password still in flight, and dropping it would
3067 # let the next caller build a SECOND gate for that user and rekey
3068 # the same file concurrently. Both registries are bounded by the
3069 # number of distinct usernames, one small Lock each.
3070 with self._connections_lock:
3071 generations = [
3072 _ConnectionGeneration(
3073 username,
3074 engine,
3075 self._password_verifiers.get(username),
3076 self._connection_epochs.get(username),
3077 )
3078 for username, engine in self.connections.items()
3079 ]
3080 return sum(
3081 self._reset_connection_generation(generation)
3082 for generation in generations
3083 )
3085 def _reset_connection_generation(
3086 self, generation: _ConnectionGeneration
3087 ) -> bool:
3088 lifecycle_lock = self._get_rekey_lock(generation.username)
3089 if not lifecycle_lock.acquire(blocking=False):
3090 return False
3091 try:
3092 try:
3093 with acquire_rekey_lock(
3094 self._get_user_db_path(generation.username),
3095 blocking=False,
3096 ):
3097 return (
3098 self._dispose_connection_generation_under_sidecar_lock(
3099 generation, detach=False
3100 )
3101 )
3102 except (OSError, RekeyLockContentionError, RekeyOperationalError):
3103 # Includes a vanished data directory: skip this generation
3104 # this sweep; nothing durable remains to fence.
3105 return False
3106 finally:
3107 lifecycle_lock.release()
3109 @staticmethod
3110 def _wal_fully_checkpointed(conn, username: str) -> bool:
3111 """Run a PASSIVE checkpoint; True when no WAL frame is left behind.
3113 A database not in WAL mode answers ``(0, -1, -1)`` and counts as
3114 checkpointed. A checkpoint that cannot run, or leaves frames that a
3115 reader still needs, returns False so the caller skips scans that
3116 read only the main database file.
3117 """
3118 try:
3119 row = conn.execute(
3120 text("PRAGMA wal_checkpoint(PASSIVE)")
3121 ).fetchone()
3122 except Exception as error:
3123 logger.warning(
3124 f"WAL checkpoint before the integrity scan failed for "
3125 f"{username}: {type(error).__name__}"
3126 )
3127 return False
3128 if (
3129 row is not None
3130 and len(row) == 3
3131 and row[0] == 0
3132 and row[1] == row[2]
3133 ):
3134 return True
3135 logger.warning(
3136 f"Skipping the cipher integrity scan for {username}: WAL frames "
3137 "are not yet checkpointed into the main database file"
3138 )
3139 return False
3141 def check_database_integrity(self, username: str) -> bool:
3142 """Check database structure and encrypted page authentication.
3144 Plain SQLite uses the full integrity check to verify that indexes
3145 match table contents. SQLCipher retains the quick structural check
3146 and its independent page-HMAC verification; the latter reads the
3147 main database file only, so it runs only once a PASSIVE checkpoint
3148 has folded every WAL frame into that file.
3150 Runs only against the current, epoch-validated cached engine
3151 (``_acquire_current_connection_generation``); with no trusted open
3152 connection it returns False rather than opening one.
3154 Only the engine snapshot happens inside that claim; the scans run
3155 after it is released. The claim holds the user's lifecycle lock,
3156 in-process sidecar gate and sidecar flock, and every other probe for
3157 this user (the shared log-drain thread, the idle-connection sweep,
3158 each request's session lookup) waits on them, so a full-file
3159 ``cipher_integrity_check`` under the claim stalled them all for the
3160 length of the scan. A disposal that runs during the scan (a close,
3161 the idle-connection sweep) still holds those locks while its
3162 TRUNCATE checkpoint waits for the scan's read transaction, bounded
3163 by ``_GATED_CHECKPOINT_BUSY_TIMEOUT_MS``. Accepted residual, the same as for any session
3164 handed out by ``get_session``: a password change that commits after
3165 the snapshot detaches and disposes the engine but does not stop this
3166 check, whose connection keeps the old key. It can then return False
3167 (an error or HMAC failures caused by the key change, not by
3168 corruption); the engine itself is never handed to a caller.
3169 """
3170 try:
3171 with self._acquire_current_connection_generation(
3172 username
3173 ) as generation:
3174 if generation is None:
3175 return False
3176 engine = generation.engine
3178 with engine.connect() as conn:
3179 # quick_check omits index/table consistency and UNIQUE
3180 # checks. Check those too for the unencrypted fallback: the
3181 # page HMAC that SQLCipher verifies below catches page-level
3182 # bit damage, but not a cryptographically valid page whose
3183 # index and table contents have become logically
3184 # inconsistent with each other — quick_check's cheaper
3185 # structural check misses that class regardless of
3186 # encryption, so the SQLCipher path deliberately keeps it
3187 # rather than paying for the full scan on every check.
3188 integrity_pragma = (
3189 "PRAGMA quick_check"
3190 if self.has_encryption
3191 else "PRAGMA integrity_check"
3192 )
3193 result = conn.execute(text(integrity_pragma))
3194 if result.fetchone()[0] != "ok":
3195 return False
3197 # SQLCipher integrity check. Plain SQLite (the unencrypted
3198 # fallback) has no cipher pages to verify and answers the
3199 # pragma with a closed result whose iteration raises; the
3200 # except below turned that into "integrity check failed"
3201 # for every healthy fallback database (#6357).
3202 #
3203 # cipher_integrity_check reads the main database file,
3204 # not the WAL. After a password change whose checkpoint
3205 # a reader kept busy, the WAL holds new-key frames while
3206 # the main file still holds those pages under the old
3207 # key, so the scan reported every such page as an HMAC
3208 # failure. Fold the WAL in first (PASSIVE: never waits
3209 # on readers or writers) and scan only when every frame
3210 # reached the main file; otherwise quick_check above,
3211 # which reads through the WAL, is the verdict.
3212 if self.has_encryption and self._wal_fully_checkpointed(
3213 conn, username
3214 ):
3215 result = conn.execute(text("PRAGMA cipher_integrity_check"))
3216 # If this returns any rows, there are HMAC failures
3217 failures = list(result)
3218 if failures:
3219 logger.error(
3220 f"Integrity check failed for {username}: "
3221 f"{len(failures)} HMAC failures"
3222 )
3223 return False
3225 return True
3227 except Exception as e:
3228 # No ``password`` parameter on this method and the engine
3229 # was created via a hex-key closure (the plaintext password
3230 # is not retained on the engine itself), so the traceback
3231 # frames in this method do not hold a credential. Still
3232 # drop the traceback to stay uniform with the rest of the
3233 # #4182 sweep and to guard against a caller frame that
3234 # happens to hold one (e.g., a route handler that
3235 # retrieved the password from the session store).
3236 from ..security.log_sanitizer import redact_secrets
3238 safe_msg = redact_secrets(str(e), None)
3239 logger.warning(
3240 f"Integrity check error for user: {username}: {safe_msg}"
3241 )
3242 return False
3244 def change_password(
3245 self, username: str, old_password: str, new_password: str
3246 ) -> bool:
3247 """Change the encryption password for a user's database.
3249 This rekeys the SQLCipher database — no separate auth-DB
3250 password-hash update is needed because passwords are never
3251 stored. Login verification is done by attempting decryption.
3252 """
3253 db_path = self._get_user_db_path(username)
3254 if not self.has_encryption:
3255 logger.warning(
3256 "Cannot change password - SQLCipher not available (databases are unencrypted)"
3257 )
3258 # Unencrypted mode is a static deployment mode, not an
3259 # operational failure: keep the documented boolean contract
3260 # (tests/security/test_db_connection_unencrypted_mode.py) for
3261 # the rekey path rather than mapping it to 503.
3262 return False
3264 # Validate BOTH keys before anything touches the database, mirroring
3265 # the guards on create_user_database / open_user_database. Rekeying to
3266 # an empty or whitespace-only password used to succeed and return
3267 # True, after which the open-path guard rejected that same password
3268 # and the database was permanently unopenable (#6353).
3269 if not self._is_valid_encryption_key(old_password):
3270 logger.error(
3271 f"Invalid encryption key when changing password for user {username}: "
3272 "old password is None or empty"
3273 )
3274 raise ValueError(
3275 "Invalid encryption key: old password cannot be None or empty"
3276 )
3277 if not self._is_valid_encryption_key(new_password):
3278 logger.error(
3279 f"Invalid encryption key when changing password for user {username}: "
3280 "new password is None or empty"
3281 )
3282 raise ValueError(
3283 "Invalid encryption key: new password cannot be None or empty"
3284 )
3286 rekey_lock = self._get_password_change_lock(username)
3287 if not rekey_lock.acquire(blocking=False):
3288 lock_path = db_path.with_name(f"{db_path.name}.rekey.lock")
3289 raise RekeyLockContentionError(lock_path)
3291 stage = RekeyStage.PREPARATION
3292 pending_may_exist = False
3293 committed = False
3294 old_connection = None
3295 new_connection = None
3296 try:
3297 # Same refusal as every other path that hands this pathname to
3298 # SQLCipher (#6493): the rekey must not follow a planted link
3299 # and write the rekeyed database outside the data directory.
3300 # _db_path_is_symlinked logs the actionable evidence (path ->
3301 # target) itself; nothing has been touched at this point.
3302 if _db_path_is_symlinked(db_path, username):
3303 return False
3304 if not db_path.exists():
3305 _raise_rekey_unavailable(db_path, stage)
3307 try:
3308 # Bounded wait for in-process holders on the sidecar gate
3309 # (cache probes, but also cold opens and disposals, which
3310 # can hold it for seconds), then a non-blocking flock
3311 # retried for a short window so another process's
3312 # millisecond cache probe is not reported as contention
3313 # while a genuine cross-process owner still fails with
3314 # the typed contention error. The dedicated
3315 # change-password gate above already fast-fails
3316 # concurrent rekeys in-process.
3317 with acquire_rekey_lock(
3318 db_path,
3319 local_wait=10.0,
3320 contention_retry=_REKEY_CONTENTION_RETRY_SECONDS,
3321 ):
3322 try:
3323 state = read_rekey_epoch_state(db_path)
3324 if (
3325 state.pending_epoch is not None
3326 and not self._recover_pending_rekey(
3327 db_path, old_password, state
3328 )
3329 ):
3330 return False
3331 read_or_initialize_rekey_epoch(db_path)
3333 cached_engine = self._detach_connection(username)
3334 if cached_engine is not None and not (
3335 self._dispose_detached_connection(
3336 username, cached_engine, checkpoint=False
3337 )
3338 ):
3339 _raise_rekey_unavailable(db_path, stage)
3341 try:
3342 old_connection = self._make_sqlcipher_connection(
3343 db_path, old_password
3344 )
3345 except ValueError:
3346 return False
3347 self._require_full_synchronous(old_connection)
3348 self._pre_rekey_checkpoint(old_connection)
3350 stage = RekeyStage.PENDING_PUBLICATION
3351 pending_may_exist = True
3352 pending_epoch = begin_rekey_epoch(db_path)
3354 stage = RekeyStage.PHYSICAL_REKEY
3355 set_sqlcipher_rekey(
3356 old_connection, new_password, db_path=db_path
3357 )
3358 old_connection.commit()
3359 # Never write through old_connection after the
3360 # rekey: when PRAGMA rekey did not apply (see
3361 # _resolve_unapplied_rekey) its write context is
3362 # already on the NEW key while the file stays under
3363 # the old one, and one INSERT there leaves pages
3364 # that neither key opens. It is closed unused.
3365 self._close_rekey_connection(
3366 old_connection, username, "old-key"
3367 )
3368 old_connection = None
3370 stage = RekeyStage.CANDIDATE_VERIFICATION
3371 try:
3372 new_connection = self._make_sqlcipher_connection(
3373 db_path, new_password
3374 )
3375 except ValueError:
3376 self._resolve_unapplied_rekey(
3377 db_path, old_password, pending_epoch
3378 )
3379 self._verify_rekey_candidate(new_connection, db_path)
3381 stage = RekeyStage.COMMIT_PUBLICATION
3382 # The new key is proven at this point: it opened
3383 # and decrypted the database, and a database state
3384 # never opens under both keys. The password change
3385 # has therefore happened, whatever the epoch files
3386 # say, and reporting it as not completed would send
3387 # the user back to an old password that no longer
3388 # works while skipping the caller's revocation of
3389 # every old-password session and credential.
3390 epoch_published = True
3391 try:
3392 commit_rekey_epoch(db_path, pending_epoch)
3393 except Exception as error:
3394 try:
3395 committed_state = read_rekey_epoch_state(
3396 db_path
3397 )
3398 except Exception:
3399 committed_state = None
3400 if committed_state != RekeyEpochState(
3401 committed_epoch=pending_epoch,
3402 pending_epoch=pending_epoch,
3403 ):
3404 # The pending file stays on disk (only this
3405 # call removes it, after the commit), so the
3406 # state remains fenced: read_rekey_epoch
3407 # reports no stable epoch while it exists,
3408 # which invalidates every cached engine, and
3409 # every cold open runs _recover_pending_rekey
3410 # first. That recovery rejects the old
3411 # password (it no longer opens the file) and,
3412 # for the new one, re-proves the key and
3413 # publishes this same pending epoch.
3414 epoch_published = False
3415 logger.warning(
3416 f"Rekey epoch publication failed for "
3417 f"{username} after the new key was "
3418 f"verified ({type(error).__name__}); "
3419 "the password change is complete and "
3420 "the pending generation will be "
3421 "committed by the next successful open"
3422 )
3424 committed = True
3425 if epoch_published:
3426 self._clear_committed_rekey_pending(
3427 db_path, pending_epoch, username
3428 )
3429 self._close_rekey_connection(
3430 new_connection, username, "new-key"
3431 )
3432 new_connection = None
3433 except (
3434 RekeyIndeterminateError,
3435 RekeyUnavailableError,
3436 ):
3437 raise
3438 except RekeyRecoveryRequiredError:
3439 if pending_may_exist:
3440 raise self._classify_rekey_failure(
3441 db_path, stage
3442 ) from None
3443 raise
3444 except Exception:
3445 if pending_may_exist:
3446 raise self._classify_rekey_failure(
3447 db_path, stage
3448 ) from None
3449 _raise_rekey_unavailable(db_path, stage)
3451 logger.info(f"Password changed for user {username}")
3452 return True
3453 except RekeyLockContentionError:
3454 raise
3455 except (
3456 RekeyIndeterminateError,
3457 RekeyRecoveryRequiredError,
3458 RekeyUnavailableError,
3459 ):
3460 raise
3461 except Exception as error:
3462 if committed:
3463 logger.warning(
3464 f"Post-commit rekey cleanup failed for {username}: "
3465 f"{type(error).__name__}"
3466 )
3467 logger.info(f"Password changed for user {username}")
3468 return True
3470 logger.warning(
3471 f"Failed to change password for user {username} during "
3472 f"{stage.value}: {type(error).__name__}"
3473 )
3474 if pending_may_exist: 3474 ↛ 3475line 3474 didn't jump to line 3475 because the condition on line 3474 was never true
3475 raise self._classify_rekey_failure(db_path, stage) from None
3476 _raise_rekey_unavailable(db_path, stage)
3477 finally:
3478 if old_connection is not None:
3479 self._close_rekey_connection(
3480 old_connection, username, "old-key"
3481 )
3482 if new_connection is not None:
3483 self._close_rekey_connection(
3484 new_connection, username, "new-key"
3485 )
3486 finally:
3487 rekey_lock.release()
3489 def user_exists(self, username: str) -> bool:
3490 """Check if a user exists in the auth database."""
3491 from .auth_db import auth_db_session
3492 from .models.auth import User
3494 with auth_db_session() as session:
3495 user = session.query(User).filter_by(username=username).first()
3496 return user is not None
3498 def get_memory_usage(self) -> Dict[str, Any]:
3499 """Get memory usage statistics."""
3500 with self._connections_lock:
3501 num_connections = len(self.connections)
3502 return {
3503 "active_connections": num_connections,
3504 "active_sessions": 0, # Sessions are created on-demand, not tracked
3505 "estimated_memory_mb": num_connections
3506 * 3.5, # ~3.5MB per connection
3507 }
3509 def create_thread_safe_session_for_metrics(
3510 self, username: str, password: str
3511 ):
3512 """
3513 Create a new database session safe for use in background threads.
3515 Previously this method created a dedicated NullPool engine per
3516 (username, thread_id) pair, which leaked file descriptors under
3517 load (SQLCipher + WAL holds 3 FDs per active connection and
3518 orphaned engines accumulated when @thread_cleanup did not fire).
3520 It now routes through the shared per-user QueuePool engine at
3521 ``self.connections[username]``. That engine is already created
3522 with ``check_same_thread=False`` (so background threads are
3523 safe), is bounded by ``pool_size + max_overflow``, and is
3524 subject to the periodic ``dispose()`` workaround in
3525 ``connection_cleanup.py`` that mitigates the SQLCipher+WAL
3526 out-of-order-close FD leak.
3528 Args:
3529 username: The username
3530 password: The user's password (encryption key). Verified against
3531 the cached connection's recorded verifier on a cache hit, and
3532 used to open the user database on a cache miss or mismatch.
3534 Returns:
3535 A SQLAlchemy Session bound to the per-user QueuePool engine.
3537 Raises:
3538 RekeyOperationalError: The database is unavailable for an
3539 operational reason (the rekey sidecar or an epoch file cannot
3540 be read, or the durable rekey state needs recovery), raised
3541 the way ``open_user_database`` raises it: before any key is
3542 derived when a cached engine cannot be checked. Not a
3543 credential verdict, so it is not folded into a None that
3544 reads like one. ``ThreadSafeMetricsWriter.get_session``
3545 reports it as its own "no session" failure;
3546 ``ThreadLocalSessionManager.get_session`` lets it reach
3547 ``get_user_db_session``, which raises
3548 ``DatabaseSessionUnavailableError``.
3549 ValueError: The database path is a symlink or missing, or
3550 ``password`` does not open the database.
3551 """
3552 db_path = self._get_user_db_path(username)
3554 if _db_path_is_symlinked(db_path, username):
3555 raise ValueError(
3556 f"Database path for user {username!r} is a symlink; refusing "
3557 "to open it"
3558 )
3560 if not db_path.exists():
3561 raise ValueError(f"No database found for user {username}")
3563 # Trust the cached engine only when the presented password matches
3564 # the one that opened it -- the same fail-closed rule as
3565 # open_user_database. Returning a session on a bare cache hit here
3566 # would be a second instance of exactly the cached-connection
3567 # password bypass this change closes; falling through to
3568 # open_user_database on a mismatch re-validates the key against
3569 # SQLCipher.
3570 with self._acquire_current_connection_generation(
3571 username
3572 ) as generation:
3573 if generation is not None and (
3574 self._connection_generation_password_matches(
3575 generation, password
3576 )
3577 ):
3578 Session = sessionmaker(bind=generation.engine)
3579 return Session()
3581 # A cached engine that the probe above could not check (a rekey
3582 # sidecar or epoch-read fault) makes this raise that typed fault
3583 # without deriving a key. It propagates (see Raises): each caller
3584 # decides what an operational fault means for it.
3585 engine = self.open_user_database(username, password)
3586 if engine is None:
3587 raise ValueError(f"Failed to open database for user {username}")
3589 Session = sessionmaker(bind=engine)
3590 return Session()
3593# Global instance
3594db_manager = DatabaseManager()