Files
werewulf/hpcod/db/004-accounts.sql
T
ilkerandClaude Opus 5 5d398c9429 hpcod: sign up on the site, and nobody gets in until someone says so
The front page asked people to send an email, which is not a process - it
is a hope that somebody reads their inbox. Anyone can register now, nobody
is let in by registering, and an administrator decides.

Accounts move from the htpasswd-style file into the database, because a
file read once at startup cannot take a registration. The file stays as
the bootstrap: an account in it is an administrator, which is how the
portal is brought up at all, and someone who can write that file has the
machine anyway.

The safety rests on two column defaults rather than on care in a handler.
A new row is 'pending' and is not an administrator, and the signup INSERT
names neither column - so no mistake in the code above can produce a live
account. The handler checks anyway and disables anything that comes back
usable, which is for the day someone changes the defaults.

What the tests pin down, because these are the parts that would be quietly
wrong:

  - a registration yields state=pending, is_admin=false, no decision
  - a pending or disabled account cannot sign in, even with the right password
  - a wrong password says exactly what it says for an unknown username, so
    the "awaiting approval" message cannot be used to find out who exists
  - only a verified password ever gets a specific reason
  - approving does not also grant administrator rights; they are separate
    decisions and conflating them is how someone gets more than was meant
  - an administrator cannot decide on their own account, which would either
    do nothing or lock the last one out
  - the admin page 404s for a signed-in non-admin rather than 403, and its
    link is not rendered for them either
  - the password column refuses anything that is not a bcrypt hash, so a
    caller that forgot to hash cannot store a plaintext password
  - a decision without a name is refused by a CHECK, because an audit trail
    with holes in it is not one

The password hash is not a field on Account. Everything that renders an
account would carry it; only the code comparing a password has a reason to
see it, so only that call returns it.

hpcod account is the way in when the portal is not: the first administrator
has to come from somewhere, and an operator who has locked themselves out
still has a shell.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
2026-09-21 08:13:34 +02:00

53 lines
2.0 KiB
PL/PgSQL

-- Portal accounts.
--
-- Until now there was one account, in an htpasswd-style file read at
-- startup, and the public page asked people to send an email. A file loaded
-- once cannot take a registration, so accounts move here.
--
-- Two rules the database keeps rather than the application:
--
-- A new account is not usable. state defaults to 'pending' and nothing in
-- the signup path can set it otherwise, so forgetting a check in a
-- handler cannot produce a live account.
--
-- Nobody signs themselves up as an administrator. is_admin defaults to
-- false and the signup INSERT never names the column.
BEGIN;
CREATE TABLE IF NOT EXISTS account (
id bigserial PRIMARY KEY,
username text NOT NULL UNIQUE,
-- bcrypt. Never anything else: the length check exists so a plaintext
-- password cannot be stored by a caller that forgot to hash.
password_hash text NOT NULL CHECK (password_hash LIKE '$2%' AND length(password_hash) >= 55),
email text,
-- Why they want access, in their words. The only thing an approver has
-- to go on.
reason text,
state text NOT NULL DEFAULT 'pending'
CHECK (state IN ('pending', 'active', 'disabled')),
is_admin boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
decided_at timestamptz,
decided_by text,
-- A decision has to say who made it. Enforced here because an audit
-- trail with holes in it is not one.
CONSTRAINT decision_is_attributed CHECK (
(state = 'pending' AND decided_at IS NULL AND decided_by IS NULL)
OR (state <> 'pending' AND decided_at IS NOT NULL AND decided_by IS NOT NULL))
);
CREATE INDEX IF NOT EXISTS account_pending_idx ON account (created_at) WHERE state = 'pending';
COMMENT ON COLUMN account.state IS
'pending until an administrator decides. Only active may sign in.';
COMMENT ON COLUMN account.is_admin IS
'Never set by signup. An administrator grants it explicitly.';
COMMIT;