Postgres roles

shithub uses a single Postgres role today (shithub) for everything — the web server, the SSH dispatcher, the worker, and the git hooks all connect with the same credentials. That's deliberate for development ergonomics; production hardening (S37) splits roles along blast-radius lines so an exploit in one surface can't read or mutate the rest of the database.

This doc captures the target end state so S37 doesn't have to re-derive it. None of these roles exist yet in dev or CI.

Role plan

The hook split is the highest-value because hooks run inside the push process — fewer guardrails between a compromised git push and DB writes than the web layer (which goes through CSRF + middleware + handlers). The AKC split mirrors the contract from S07: that path only needs to authenticate keys; if it can't write to repos or push_events the blast radius is "log spam at worst."

SQL recipe (S37 will turn this into a migration or Ansible task)

-- ─── shithub_hook ───────────────────────────────────────────────
CREATE ROLE shithub_hook LOGIN PASSWORD :'shithub_hook_password';

-- Reads (re-checked from DB so env staleness can't authorize a push):
GRANT SELECT (id, suspended_at, deleted_at)         ON users  TO shithub_hook;
GRANT SELECT (id, is_archived, deleted_at, default_branch) ON repos TO shithub_hook;

-- Writes (post-receive's exact INSERT surface):
GRANT INSERT                                  ON push_events           TO shithub_hook;
GRANT INSERT, SELECT, USAGE                   ON jobs                  TO shithub_hook;
GRANT INSERT                                  ON webhook_events_pending TO shithub_hook;
GRANT USAGE, SELECT                           ON SEQUENCE push_events_id_seq           TO shithub_hook;
GRANT USAGE, SELECT                           ON SEQUENCE jobs_id_seq                  TO shithub_hook;
GRANT USAGE, SELECT                           ON SEQUENCE webhook_events_pending_id_seq TO shithub_hook;

-- Connect & schema:
GRANT CONNECT ON DATABASE shithub TO shithub_hook;
GRANT USAGE   ON SCHEMA   public  TO shithub_hook;

-- Notify channel: pg_notify needs no extra grant (it's a function call).

-- Explicitly REVOKE the public defaults if any were inherited:
REVOKE ALL ON ALL TABLES    IN SCHEMA public FROM shithub_hook;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM shithub_hook;
-- Then re-apply the precise grants above. (Order: REVOKE first when
-- migrating an existing role; on a fresh CREATE ROLE the explicit
-- grants are already authoritative.)


-- ─── shithub_akc ────────────────────────────────────────────────
CREATE ROLE shithub_akc LOGIN PASSWORD :'shithub_akc_password';

GRANT SELECT (id, fingerprint, public_key, user_id, expires_at, revoked_at)
                      ON user_ssh_keys TO shithub_akc;
GRANT UPDATE (last_used_at, last_used_ip)
                      ON user_ssh_keys TO shithub_akc;
GRANT SELECT (id, username, suspended_at, deleted_at)
                      ON users         TO shithub_akc;

GRANT CONNECT ON DATABASE shithub TO shithub_akc;
GRANT USAGE   ON SCHEMA   public  TO shithub_akc;

Plumbing changes when S37 lands

• Config: add SHITHUB_HOOK_DATABASE_URL and SHITHUB_AKC_DATABASE_URL to internal/infra/config/config.go. Fall back to SHITHUB_DATABASE_URL when unset (dev keeps single-role).
• Hook subcommands: cmd/shithubd/hook.go::loadHookCtx checks SHITHUB_HOOK_DATABASE_URL first, falls back to the main URL.
• AKC subcommand: cmd/shithubd/ssh.go::sshAuthkeysCmd checks SHITHUB_AKC_DATABASE_URL first.
• Ansible role: deploy/ansible/roles/postgres/tasks/main.yml runs the SQL recipe above against a fresh DB; passwords come from sops or 1Password.
• Migration policy: don't add the GRANT statements to the goose migrations directory — those run as the shithub super-role and changing them would re-grant on every dev re-up. Roles + grants are Ansible territory, not migration territory.

Why we deferred

S14's deliverables include "Hook DB connection: small pool, distinct credentials with the minimum-needed grants." The dev path (single role) is what S14 actually shipped. Splitting it now adds friction to every new schema migration (each new write target needs a grant tweak) while the schema is still iterating fast — by S37 the schema has settled enough that the grant surface is stable.

Tracking

• This doc is the design.
• .docs/sprints/S37-deployment-automation.md references this doc under "Postgres" → "Hook DB role split" so the deferred work is on the sprint's deliverable list, not floating.
• The S14 sprint spec (.docs/sprints/S14-push-processing-pipeline.md) remains the authoritative description of what S14 should have done in an ideal world; this doc explains what got cut and where it landed.