Files
WolfandClaude Opus 5.5 9d666d0937 Bring in pipeline, auth, deploy and docs from the boilerplate
roledesk is built on the boilerplate from here on: pipeline 2.0 (queue and
scheduler), auth (one password, sessions, passkeys), the deploy kit and its
docs, plus the coupling and packaging tests. Downstream copies, unedited.

Refreshed from boilerplate/d56949f7d0f6c0bfe5745478e09007bfad67ae87

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-09-23 12:29:16 +02:00

506 lines
23 KiB
Python

"""One password, and the sessions it opens. Nothing else knows a hash.
Four tables in whatever SQLite file it is given — the app's own, normally, so
there is one file to back up and SQLite's writer lock keeps auth honest with
everything else in it. Every name starts `auth_`, because the file is shared:
a `sessions` table is a name an app is entitled to want for itself.
Four decisions worth arguing for, because each of them is the kind of thing
that gets "simplified" later by someone who does not know why it is here.
**The password is never stored, and neither is anything reversible.** What goes
in the file is `scrypt(password, salt)` and the salt. scrypt is in the standard
library, so this costs no dependency, and it is *memory*-hard rather than
merely slow — the parameters below make one guess cost 16 MB, which is what
takes a stolen database file out of range of a GPU. A plain SHA-256 of the
password would fit in the same column and be worth nothing.
**A session token is random, not signed.** The alternative — a signed cookie
carrying the claim, JWT-shaped — needs a signing key that has to be generated,
stored, kept out of logs and backups, and rotated, and it buys statelessness
that a single-user app on one SQLite file has no use for. Here the cookie is 32
random bytes and the database holds only its SHA-256, so:
- the file cannot be read to forge a session, the same way it cannot be read to
recover the password;
- signing out everywhere is one `DELETE`, which no signed token can offer
without keeping exactly the server-side state this already has;
- there is no key to leak.
Hashing the token before storing it is the same move as hashing the password
and for the same reason. It is only SHA-256 rather than scrypt because the
input is already 32 bytes of `secrets`, so there is no guessing to slow down.
**Failed attempts are counted in the file, not in memory.** A counter on the
process resets when the process does, which on a server that reloads or
crash-loops is a free reset for whoever is guessing. See `check_password`.
**Passkeys sit alongside all of that rather than replacing it.** What is stored
for one is a *public* key, which is the whole point — there is no secret here
to protect, because the private half never leaves the authenticator. The
ceremony that uses these rows is `auth/passkeys.py`; this module only keeps
them.
"""
from __future__ import annotations
import hashlib
import hmac
import math
import os
import secrets
import sqlite3
from contextlib import closing
from datetime import UTC, datetime, timedelta
from pathlib import Path
from auth.models import Passkey, SessionInfo
#: scrypt cost. `n` is the work factor, and 2**14 with `r=8` is the widely
#: recommended interactive setting: about 16 MB and ~50 ms per guess, which is
#: imperceptible on a login and ruinous on a dictionary. Raising `n` later is
#: safe — the parameters are not stored, so a change simply invalidates the
#: existing hash and `set_password` writes one at the new cost. Which is worth
#: knowing before raising it: it means the current password stops working and
#: has to be set again.
_N, _R, _P = 2**14, 8, 1
_DKLEN = 32
#: scrypt's own guard against a caller asking for more memory than it meant to.
#: The default ceiling is below what `_N` above needs, so it has to be raised
#: here or every derivation is a `ValueError` — and the failure would be at the
#: door, on the first login, in production.
_MAXMEM = 64 * 1024 * 1024
#: How many wrong answers before the door slows down, and how slow it gets.
#: Five is past a typo and a stale password manager entry, and short of anything
#: systematic. The delay then doubles per failure to a quarter of an hour —
#: enough that guessing is pointless, little enough that locking yourself out on
#: a Sunday is not a crisis.
_GRACE = 5
_FIRST_LOCK = timedelta(seconds=30)
_MAX_LOCK = timedelta(minutes=15)
#: How many doublings can still change the answer. Derived from the two above
#: rather than written down, so re-tuning either of them cannot silently move
#: the ceiling. See `_lockout` for what goes wrong without it.
_MAX_STEPS = math.ceil(math.log2(_MAX_LOCK / _FIRST_LOCK))
#: How stale `last_seen_at` may get before a request bothers to update it.
#: Without this, every single request — every socket frame's sibling fetch,
#: every poll of a page left open — takes SQLite's write lock to move a
#: timestamp nobody reads more precisely than "today", and contends with a
#: worker trying to write actual data. Five minutes is far finer than the
#: account screen's "last seen" line can usefully show.
_SEEN_EVERY = timedelta(minutes=5)
#: The tables. Created on construction rather than by a migration step, because
#: the only schema change this has ever had is "it did not exist yet", and a
#: migration runner for four `CREATE TABLE IF NOT EXISTS` is a thing to
#: maintain in exchange for nothing.
SCHEMA = """
CREATE TABLE IF NOT EXISTS auth_credential (
id INTEGER PRIMARY KEY CHECK (id = 1),
salt BLOB NOT NULL,
hash BLOB NOT NULL,
updated_at TEXT NOT NULL,
failures INTEGER NOT NULL DEFAULT 0,
locked_until TEXT NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS auth_sessions (
id INTEGER PRIMARY KEY,
token_hash TEXT NOT NULL UNIQUE,
label TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL,
last_seen_at TEXT NOT NULL,
expires_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS auth_passkeys (
id INTEGER PRIMARY KEY,
credential_id BLOB NOT NULL UNIQUE,
public_key BLOB NOT NULL,
sign_count INTEGER NOT NULL DEFAULT 0,
transports TEXT NOT NULL DEFAULT '',
label TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL,
last_used_at TEXT NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS auth_challenges (
challenge BLOB PRIMARY KEY,
purpose TEXT NOT NULL,
expires_at TEXT NOT NULL
);
"""
def _now() -> datetime:
return datetime.now(UTC)
def _stamp(when: datetime) -> str:
return when.isoformat()
def _token_hash(token: str) -> str:
return hashlib.sha256(token.encode()).hexdigest()
def _derive(password: str, salt: bytes) -> bytes:
return hashlib.scrypt(password.encode(), salt=salt, n=_N, r=_R, p=_P,
dklen=_DKLEN, maxmem=_MAXMEM)
class AuthStore:
"""The four `auth_*` tables, opened on the path it is handed.
store = AuthStore("data/app.db")
Its own connections, opened per call and closed after, rather than one held
open: SQLite in WAL mode is happy with that, a connection per call cannot be
shared across threads by accident, and the middleware runs this on a worker
thread from an async request.
"""
def __init__(self, path: str | os.PathLike[str]) -> None:
self.path = Path(path)
self.path.parent.mkdir(parents=True, exist_ok=True)
with closing(self._connect()) as conn, conn:
conn.executescript(SCHEMA)
def _connect(self) -> sqlite3.Connection:
conn = sqlite3.connect(self.path, timeout=10)
conn.row_factory = sqlite3.Row
# WAL so a login is not blocked by whatever else is writing to this
# file — the job queue, most likely, which writes constantly.
conn.execute("PRAGMA journal_mode=WAL")
return conn
# -------------------------------------------------------------- password
def has_password(self) -> bool:
"""Whether this database has ever had one set. False on a fresh file,
which is what makes the app print a generated one at startup."""
with closing(self._connect()) as conn:
return conn.execute(
"SELECT 1 FROM auth_credential WHERE id=1").fetchone() is not None
def set_password(self, password: str) -> None:
"""Set or replace it. A fresh salt every time, so setting the same
password twice does not produce the same row — which is what stops the
file from answering "did it change?" to anyone who kept a copy.
Deliberately does not close existing sessions. Whether a password
change should sign your other devices out is a policy question with a
real answer either way, and it belongs to the caller that knows why the
password is changing; `auth/router.py` decides.
"""
salt = secrets.token_bytes(16)
with closing(self._connect()) as conn, conn:
conn.execute(
"""INSERT INTO auth_credential
(id, salt, hash, updated_at, failures, locked_until)
VALUES (1, ?, ?, ?, 0, '')
ON CONFLICT(id) DO UPDATE SET
salt=excluded.salt, hash=excluded.hash,
updated_at=excluded.updated_at, failures=0, locked_until=''""",
(salt, _derive(password, salt), _stamp(_now())),
)
def locked_for(self) -> int:
"""Seconds until the next attempt will be looked at, or 0.
Read before checking rather than after, so a locked-out caller is told
to wait instead of having a guess counted — otherwise a fast enough
attacker extends their own lockout forever and never notices, and, more
to the point, an honest person hammering the button digs a deeper hole
with every press.
"""
with closing(self._connect()) as conn:
row = conn.execute(
"SELECT locked_until FROM auth_credential WHERE id=1").fetchone()
if row is None or not row["locked_until"]:
return 0
remaining = datetime.fromisoformat(row["locked_until"]) - _now()
return max(0, int(remaining.total_seconds() + 0.999))
def check_password(self, password: str) -> bool:
"""Right or wrong, and record which.
`hmac.compare_digest` rather than `==`, so the comparison takes the same
time whatever the first wrong byte is. That is close to superstition at
this scale — the network jitter in front of it is larger than the signal
— but it is one call, and the habit is worth more than the microseconds.
Note that a wrong answer still costs a full scrypt derivation, including
on a database with no password set at all. That is the point: the delay
an attacker meets is the same one the honest user meets, and it is the
reason `_GRACE` can be as generous as it is.
**There is one counter, not one per caller, and that is on purpose.**
The obvious improvement is to lock out the address that is guessing
instead of the password, so that a stranger cannot keep the owner
waiting at their own door. It cannot be built honestly here. The only
address this app can see is the one its proxy puts in a header — the
peer is a container on the same Docker network — and a header is set by
whoever is talking. Keyed on that, an attacker gets a fresh, empty
counter per request by changing one line of their own request, while the
owner still gets locked out by their own typos: strictly worse than
this, and it would look like protection. So the address is written to
the log instead, at WARNING, where fail2ban can act on it at the layer
that actually knows who connected — see `_warn` in `auth/router.py`.
What is left for this counter to do is make guessing pointless, which it
does whoever is guessing.
"""
with closing(self._connect()) as conn:
row = conn.execute(
"SELECT salt, hash, failures FROM auth_credential WHERE id=1"
).fetchone()
if row is None:
_derive(password, b"no password here")
return False
ok = hmac.compare_digest(_derive(password, row["salt"]), row["hash"])
with conn:
if ok:
conn.execute(
"UPDATE auth_credential SET failures=0, locked_until='' WHERE id=1")
else:
failures = int(row["failures"]) + 1
conn.execute(
"UPDATE auth_credential SET failures=?, locked_until=? WHERE id=1",
(failures, _stamp(_now() + _lockout(failures))
if failures >= _GRACE else ""),
)
return ok
def changed_at(self) -> str:
"""When the password was last set, for the account screen to show."""
with closing(self._connect()) as conn:
row = conn.execute(
"SELECT updated_at FROM auth_credential WHERE id=1").fetchone()
return row["updated_at"] if row else ""
# -------------------------------------------------------------- sessions
def open_session(self, label: str, days: int) -> str:
"""A new session. Returns the raw token — the only time it exists.
`token_urlsafe(32)` is 256 bits from the OS CSPRNG, which is past
anybody's ability to guess and short enough to sit in a cookie.
"""
token = secrets.token_urlsafe(32)
started = _now()
with closing(self._connect()) as conn, conn:
conn.execute(
"""INSERT INTO auth_sessions
(token_hash, label, created_at, last_seen_at, expires_at)
VALUES (?,?,?,?,?)""",
(_token_hash(token), label[:200], _stamp(started), _stamp(started),
_stamp(started + timedelta(days=days))),
)
return token
def touch(self, token: str) -> SessionInfo | None:
"""The session this token names, if it is live, marking it seen.
Expiry is enforced in the `WHERE`, so a session that has run out is
never returned even though its row is still there — the row is cleared
by `purge`, and until then it is simply not a session.
`last_seen_at` is what makes the sessions list worth reading: without it
every row says the same thing, and you cannot tell the laptop you use
daily from the phone you signed in on once. It is written at most once
every `_SEEN_EVERY` — this runs on every request there is, and a
timestamp is not worth the write lock.
"""
digest = _token_hash(token)
now = _now()
stamp = _stamp(now)
with closing(self._connect()) as conn:
row = conn.execute(
"SELECT * FROM auth_sessions WHERE token_hash=? AND expires_at > ?",
(digest, stamp),
).fetchone()
if row is None:
return None
seen = row["last_seen_at"]
if now - datetime.fromisoformat(seen) >= _SEEN_EVERY:
with conn:
conn.execute(
"UPDATE auth_sessions SET last_seen_at=? WHERE token_hash=?",
(stamp, digest))
seen = stamp
return _session(row, seen, current=True)
def sessions(self, current: str = "") -> list[SessionInfo]:
"""Every live session, most recently seen first, with this one flagged."""
digest = _token_hash(current) if current else ""
stamp = _stamp(_now())
with closing(self._connect()) as conn:
rows = conn.execute(
"""SELECT * FROM auth_sessions WHERE expires_at > ?
ORDER BY last_seen_at DESC, id DESC""", (stamp,)).fetchall()
return [_session(r, r["last_seen_at"], current=r["token_hash"] == digest)
for r in rows]
def close_session(self, token: str) -> None:
with closing(self._connect()) as conn, conn:
conn.execute("DELETE FROM auth_sessions WHERE token_hash=?",
(_token_hash(token),))
def close_by_id(self, session_id: int) -> bool:
"""Revoke one session from the account screen. By row id rather than by
token, because the screen has never seen a token and must not."""
with closing(self._connect()) as conn, conn:
cur = conn.execute("DELETE FROM auth_sessions WHERE id=?", (session_id,))
return cur.rowcount > 0
def close_all(self, keep: str = "") -> int:
"""Sign out everywhere, optionally sparing the caller's own session.
The whole reason sessions are rows rather than signed tokens. Returns
how many went, so the UI can say.
"""
with closing(self._connect()) as conn, conn:
if keep:
cur = conn.execute("DELETE FROM auth_sessions WHERE token_hash != ?",
(_token_hash(keep),))
else:
cur = conn.execute("DELETE FROM auth_sessions")
return cur.rowcount
# -------------------------------------------------------------- passkeys
def add_passkey(self, credential_id: bytes, public_key: bytes,
sign_count: int, transports: str, label: str) -> int:
"""Register one. Returns its row id.
`credential_id` is unique, so registering the same authenticator twice
is an error rather than a second row that would make "revoke this one"
ambiguous. The registration ceremony avoids reaching this by handing the
browser an `excludeCredentials` list — see `passkeys.register_options`.
"""
with closing(self._connect()) as conn, conn:
cur = conn.execute(
"""INSERT INTO auth_passkeys
(credential_id, public_key, sign_count, transports, label, created_at)
VALUES (?,?,?,?,?,?)""",
(credential_id, public_key, sign_count, transports,
label[:100], _stamp(_now())),
)
return int(cur.lastrowid or 0)
def passkeys(self) -> list[Passkey]:
"""Every registered authenticator, newest first. No key material: this
is what the account screen lists, and it has no use for a public key."""
with closing(self._connect()) as conn:
rows = conn.execute(
"SELECT * FROM auth_passkeys ORDER BY created_at DESC, id DESC").fetchall()
return [Passkey(id=r["id"], label=r["label"], created_at=r["created_at"],
last_used_at=r["last_used_at"],
transports=[t for t in r["transports"].split(",") if t])
for r in rows]
def passkey_ids(self) -> list[bytes]:
"""Raw credential ids, for the `allowCredentials` and
`excludeCredentials` lists the two ceremonies hand the browser."""
with closing(self._connect()) as conn:
return [r["credential_id"] for r in
conn.execute("SELECT credential_id FROM auth_passkeys")]
def passkey_for(self, credential_id: bytes) -> tuple[int, bytes, int] | None:
"""`(id, public_key, sign_count)` for the credential that just signed,
or None if this app has never seen it."""
with closing(self._connect()) as conn:
row = conn.execute(
"SELECT id, public_key, sign_count FROM auth_passkeys WHERE credential_id=?",
(credential_id,),
).fetchone()
if row is None:
return None
return row["id"], row["public_key"], int(row["sign_count"])
def record_passkey_use(self, passkey_id: int, sign_count: int) -> None:
with closing(self._connect()) as conn, conn:
conn.execute(
"UPDATE auth_passkeys SET sign_count=?, last_used_at=? WHERE id=?",
(sign_count, _stamp(_now()), passkey_id))
def rename_passkey(self, passkey_id: int, label: str) -> bool:
with closing(self._connect()) as conn, conn:
cur = conn.execute("UPDATE auth_passkeys SET label=? WHERE id=?",
(label[:100], passkey_id))
return cur.rowcount > 0
def delete_passkey(self, passkey_id: int) -> bool:
with closing(self._connect()) as conn, conn:
cur = conn.execute("DELETE FROM auth_passkeys WHERE id=?", (passkey_id,))
return cur.rowcount > 0
# ------------------------------------------------------------ challenges
def remember_challenge(self, challenge: bytes, purpose: str,
seconds: int = 300) -> None:
"""Keep a handed-out challenge until it is answered or runs out.
Five minutes is the browser's own ceremony timeout with room to spare;
a challenge outliving that is one nobody is going to answer.
"""
with closing(self._connect()) as conn, conn:
# Expired, unanswered ceremonies must not accumulate until restart:
# the endpoint that hands them out needs no session.
conn.execute("DELETE FROM auth_challenges WHERE expires_at <= ?",
(_stamp(_now()),))
conn.execute(
"INSERT OR REPLACE INTO auth_challenges (challenge, purpose, expires_at) "
"VALUES (?,?,?)",
(challenge, purpose, _stamp(_now() + timedelta(seconds=seconds))))
def take_challenge(self, challenge: bytes, purpose: str) -> bool:
"""Spend it. True if it was outstanding for this purpose, and it is now
gone whether it was or not.
Single-use is the property that matters: without the delete, a captured
response could be replayed for as long as the challenge lived. The
`purpose` is checked too, so a challenge issued for registering cannot
be answered with a login.
"""
with closing(self._connect()) as conn, conn:
cur = conn.execute(
"DELETE FROM auth_challenges WHERE challenge=? AND purpose=? "
"AND expires_at > ?",
(challenge, purpose, _stamp(_now())))
return cur.rowcount > 0
def purge(self) -> int:
"""Drop sessions and challenges that have expired. Nothing depends on
this — `touch`, `sessions` and `take_challenge` all filter on expiry —
so it is housekeeping, run at startup, not a security boundary."""
stamp = _stamp(_now())
with closing(self._connect()) as conn, conn:
cur = conn.execute("DELETE FROM auth_sessions WHERE expires_at <= ?", (stamp,))
gone = cur.rowcount
conn.execute("DELETE FROM auth_challenges WHERE expires_at <= ?", (stamp,))
return gone
def _lockout(failures: int) -> timedelta:
"""30s on the fifth wrong answer, doubling, capped at a quarter hour.
The exponent is capped, not only the delay it produces. `2 ** steps` is an
ordinary Python int and will keep doubling happily past anything a
`timedelta` can hold, so a few dozen wrong answers in a row used to end in
an OverflowError raised out of `check_password` — which is a 500 on every
login attempt until a right password lands, from an attacker who only ever
sent wrong ones. Once the cap has won, further doublings are arithmetic
nobody reads.
"""
steps = min(failures - _GRACE, _MAX_STEPS)
return min(_FIRST_LOCK * (2 ** steps), _MAX_LOCK)
def _session(row: sqlite3.Row, last_seen: str, current: bool) -> SessionInfo:
return SessionInfo(
id=row["id"], label=row["label"], created_at=row["created_at"],
last_seen_at=last_seen, expires_at=row["expires_at"], current=current,
)