Coverage for src/local_deep_research/database/auth_db.py: 93%
98 statements
« prev ^ index » next coverage.py v7.16.2, created at 2026-10-22 21:20 +0000
1"""
2Authentication database initialization and management.
3This manages the central ldr_auth.db which only stores usernames.
4"""
6import errno
7import os
8import threading
9from contextlib import contextmanager
10from pathlib import Path
11from typing import Optional
13from loguru import logger
14from sqlalchemy import create_engine, event
15from sqlalchemy.engine import Engine
16from sqlalchemy.orm import sessionmaker, Session
17from sqlalchemy.pool import QueuePool
18from sqlalchemy.schema import CreateIndex, CreateTable
20from ..config.paths import get_data_directory
21from .models.auth import User
22from .pool_config import POOL_PRE_PING, POOL_RECYCLE_SECONDS
25# Global cached engine for auth database to prevent file descriptor leaks
26_auth_engine: Optional[Engine] = None
27_auth_engine_path: Optional[Path] = (
28 None # Track the path the engine was created for
29)
30_auth_engine_lock = threading.Lock()
33def get_auth_db_path() -> Path:
34 """Get the path to the authentication database."""
35 return get_data_directory() / "ldr_auth.db"
38def _best_effort_chmod(path: Path, mode: int) -> None:
39 """Tighten permissions on the auth DB file to ``mode``, never raising.
41 Mirrors ``encrypted_db._best_effort_chmod``: permission hardening
42 must NOT be able to break auth database creation. On filesystems
43 where chmod raises even for the owner (some Docker bind mounts and
44 network/FUSE volumes), the file stays at its umask default and the
45 downgrade is surfaced at warning level — ``ldr_auth.db`` holds
46 usernames and login metadata, so a silent group/world-readable
47 state deserves visibility.
48 """
49 try:
50 os.chmod(str(path), mode)
51 except OSError as exc:
52 # Copy out before logging: the check-sensitive-logging pre-commit
53 # hook flags any reference to the ``except`` variable itself inside
54 # a non-debug logger call (including its attributes, since e.g.
55 # e.strerror carries the same detail as str(e)) as a potential
56 # secret leak. errno/strerror are plain OS error codes/text, not
57 # sensitive, but the hook can't tell that apart -- so, like
58 # encrypted_db._db_path_is_symlinked's ``target = f"...{exc
59 # .strerror}>"``, the detail is copied into ordinary local
60 # variables first and only those are interpolated below.
61 chmod_errno = exc.errno
62 chmod_strerror = exc.strerror
63 logger.warning(
64 f"Could not set permissions {oct(mode)} on auth database "
65 f"{path}; left at filesystem default "
66 f"(errno={chmod_errno}, {chmod_strerror})"
67 )
70def _create_private_file(path: Path) -> None:
71 """Create ``path`` as an empty, owner-only regular file, never via a link.
73 Mirrors ``encrypted_db._create_private_file``: ``O_CREAT | O_EXCL``
74 fails when anything exists at ``path`` — including a symlink, dangling
75 or not — and ``O_NOFOLLOW`` is added where the platform has it, so a
76 planted symlink is refused rather than followed. This closes the
77 creation-race window where SQLite would otherwise create the file at
78 umask-default permissions (typically 0644) before the post-DDL chmod
79 ever runs. Callers tolerate ``FileExistsError`` — the file may already
80 exist from a previous run, in which case SQLite simply opens it and the
81 existing-file chmod paths (here and in ``_get_auth_engine``) tighten it.
82 """
83 flags = os.O_WRONLY | os.O_CREAT | os.O_EXCL | getattr(os, "O_NOFOLLOW", 0)
84 try:
85 fd = os.open(str(path), flags, 0o600)
86 except OSError as exc:
87 # A platform may report the symlink through O_NOFOLLOW (ELOOP)
88 # before O_EXCL gets to say EEXIST; both mean "something is
89 # already at this path", so callers see one exception type.
90 if exc.errno == errno.ELOOP: 90 ↛ 91line 90 didn't jump to line 91 because the condition on line 90 was never true
91 raise FileExistsError(
92 errno.EEXIST, "Path is a symlink", str(path)
93 ) from exc
94 raise
95 os.close(fd)
98def _get_auth_engine() -> Engine:
99 """
100 Get or create a cached engine for the auth database.
102 This prevents file descriptor leaks by reusing a single engine
103 instead of creating a new one for every session.
105 The engine is invalidated if the data directory path changes
106 (e.g., during testing when LDR_DATA_DIR is set to a temp directory).
107 """
108 global _auth_engine, _auth_engine_path
110 auth_db_path = get_auth_db_path()
112 # Check if we have a cached engine for the current path
113 if _auth_engine is not None and _auth_engine_path == auth_db_path:
114 return _auth_engine
116 with _auth_engine_lock:
117 # Double-check after acquiring lock
118 if _auth_engine is not None and _auth_engine_path == auth_db_path: 118 ↛ 119line 118 didn't jump to line 119 because the condition on line 118 was never true
119 return _auth_engine
121 # If path changed, dispose old engine first
122 if _auth_engine is not None and _auth_engine_path != auth_db_path:
123 try:
124 _auth_engine.dispose()
125 logger.debug(
126 "Disposed auth engine due to data directory change"
127 )
128 except Exception:
129 logger.warning("Error disposing old auth engine")
130 _auth_engine = None
131 _auth_engine_path = None
133 # Ensure database exists
134 if not auth_db_path.exists():
135 init_auth_database()
137 # SECURITY: tighten permissions whenever the engine is (re)created
138 # for this path. init_auth_database() already does this for a
139 # fresh database, but this call also covers an existing file from
140 # an older install that predates owner-only creation (still 0644)
141 # — upgrading and restarting is then enough to tighten it, with no
142 # migration step. Best-effort and idempotent; this block runs at
143 # most once per process per path since the cached-engine check
144 # above short-circuits every later call for the same path.
145 _best_effort_chmod(auth_db_path, 0o600)
147 # Moderate pool — auth DB is unencrypted SQLite used by every
148 # authenticated request via before_request middleware.
149 # See ADR-0004 for pool sizing rationale.
150 _auth_engine = create_engine(
151 f"sqlite:///{auth_db_path}",
152 poolclass=QueuePool,
153 pool_size=10,
154 max_overflow=20,
155 pool_pre_ping=POOL_PRE_PING,
156 pool_recycle=POOL_RECYCLE_SECONDS,
157 echo=False,
158 )
160 def _apply_auth_pragmas(dbapi_connection, connection_record):
161 dbapi_connection.execute("PRAGMA busy_timeout = 10000")
162 dbapi_connection.execute("PRAGMA temp_store = MEMORY")
164 event.listen(_auth_engine, "connect", _apply_auth_pragmas)
166 _auth_engine_path = auth_db_path
167 logger.debug("Created cached auth database engine")
169 return _auth_engine
172def init_auth_database():
173 """Initialize the authentication database if it doesn't exist.
175 Uses SQL-level IF NOT EXISTS (via CreateTable/CreateIndex) so that
176 concurrent calls are handled atomically by the database engine,
177 eliminating the TOCTOU race in SQLAlchemy's checkfirst inspect.
178 """
179 auth_db_path = get_auth_db_path()
181 # Ensure the data directory exists.
182 # Lazy import: this runs during early app bootstrap, so avoid a
183 # top-level security -> ... -> database import cycle.
184 from ..security.directory_creation import create_directory
186 create_directory(
187 auth_db_path.parent,
188 context="auth database directory",
189 )
191 logger.debug(f"Ensuring auth database at {auth_db_path}")
193 # SECURITY: materialize the file ourselves, owner-only and no-follow,
194 # before handing the path to SQLite. Without this, SQLite's own lazy
195 # create below lands the file at umask-default permissions (typically
196 # 0644) for the window between that create and the post-DDL chmod
197 # further down — a same-host reader with an fd opened in that window
198 # keeps read access even after the chmod. This call is idempotent and
199 # is what init_auth_database's callers rely on for repeated/concurrent
200 # invocations (see the "IF NOT EXISTS" DDL below), so a FileExistsError
201 # from an already-present file is expected and tolerated — but only
202 # when it is a regular file. init_auth_database() only runs when
203 # _get_auth_engine() sees auth_db_path.exists() is False, which is
204 # also true for a dangling symlink, so a dangling symlink at this
205 # path is caught right here and refused. An existing database file —
206 # including one reached through a symlink to an existing target —
207 # never reaches this function at all: _get_auth_engine() opens it as
208 # before via create_engine() and tightens it with its own chmod,
209 # following the link the same way SQLite's open always has.
210 try:
211 _create_private_file(auth_db_path)
212 except FileExistsError:
213 if auth_db_path.is_symlink(): 213 ↛ 214line 213 didn't jump to line 214 because the condition on line 213 was never true
214 raise ValueError(
215 f"Auth database path {auth_db_path} is a symlink; "
216 "refusing to create or open the database there"
217 )
219 # Create the database with a temporary engine
220 engine = create_engine(f"sqlite:///{auth_db_path}")
222 # Use SQL-level IF NOT EXISTS — idempotent and atomic, safe for
223 # concurrent calls without Python-level locking.
224 with engine.begin() as conn:
225 conn.execute(CreateTable(User.__table__, if_not_exists=True))
226 for index in User.__table__.indexes:
227 conn.execute(CreateIndex(index, if_not_exists=True))
229 # Dispose the temporary engine
230 engine.dispose()
232 # SECURITY: SQLite creates the file with umask-default permissions
233 # (typically 0644), while every other DB artifact is owner-only.
234 # Tighten to owner read/write — best-effort so a chmod-hostile
235 # filesystem cannot break auth database creation.
236 _best_effort_chmod(auth_db_path, 0o600)
238 logger.debug("Auth database initialized successfully")
241def get_auth_db_session() -> Session:
242 """
243 Get a session for the auth database.
245 IMPORTANT: The caller MUST close the session when done to return
246 the connection to the pool. Use auth_db_session() context manager
247 for automatic cleanup.
248 """
249 engine = _get_auth_engine()
250 SessionFactory = sessionmaker(bind=engine)
251 return SessionFactory()
254@contextmanager
255def auth_db_session():
256 """
257 Context manager for auth database sessions.
259 Usage:
260 with auth_db_session() as session:
261 user = session.query(User).filter_by(username=username).first()
263 The session is automatically closed when the context exits.
264 """
265 session = get_auth_db_session()
266 try:
267 yield session
268 finally:
269 from ..utilities.resource_utils import safe_close
271 safe_close(session, "auth DB session")
274def dispose_auth_engine():
275 """
276 Dispose the auth engine (for shutdown/cleanup).
277 """
278 global _auth_engine, _auth_engine_path
280 with _auth_engine_lock:
281 if _auth_engine is not None:
282 _auth_engine.dispose()
283 _auth_engine = None
284 _auth_engine_path = None
285 logger.debug("Disposed auth database engine")