rbrooks/Quest-Board

Watch

Fork 0

[Hardening] Enforce DML-only privileges for the app DB user; remove orphaned init.sql #102

New issue

Closed

opened 2026-07-14 19:49:27 +00:00 by claude-bot · 2 comments

claude-bot commented

2026-07-14 19:49:27 +00:00

Collaborator

Context

Project docs (root CLAUDE.md and webapp/CLAUDE.md) describe a two-role database model: a DML-only questboard app user and a DDL-capable questboard_migrate role for Alembic. The runtime does not enforce this:

POSTGRES_USER=questboard is created by the postgres Docker entrypoint as a SUPERUSER — the app connects with full DDL and admin rights.
The only mounted init script is webapp/postgres/init.sh (mounted at docker-compose.yml:20), which only creates/updates the questboard_migrate role and grants it privileges (init.sh:9-25). It contains no REVOKE/GRANT tightening for the app user.
webapp/postgres/init.sql is a dead file: it is mounted nowhere (repo-wide, the only reference to it is a mention in webapp/CLAUDE.md), it hardcodes the password changeme_migrate (init.sql:10), and — contrary to what one might expect — the app-user tightening exists there only as a SQL comment (init.sql:20-24), not as executable statements.

So a SQL-injection or app-level compromise has superuser DB access, and the documented privilege model is fiction.

Fix / Spec

In webapp/postgres/init.sh, after the existing migrate-role block, add the app-user tightening for fresh installs:

REVOKE CREATE ON SCHEMA public FROM $POSTGRES_USER;
-- app user keeps DML only:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO $POSTGRES_USER;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO $POSTGRES_USER;
ALTER DEFAULT PRIVILEGES FOR ROLE questboard_migrate IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO $POSTGRES_USER;
ALTER DEFAULT PRIVILEGES FOR ROLE questboard_migrate IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO $POSTGRES_USER;
ALTER ROLE $POSTGRES_USER NOSUPERUSER NOCREATEDB NOCREATEROLE;

(Adjust exact statements as needed; the key outcomes are: app user is not superuser, cannot CREATE TABLE, retains DML on current and future tables/sequences created by the migrate role.)

Delete webapp/postgres/init.sql.
Add an "Existing installs" runbook section to docs/OPERATIONS.md with the one-time ALTER ROLE ... NOSUPERUSER + REVOKE/GRANT statements for deployments whose data dir predates this change (init scripts only run on empty data dirs).
Verify Alembic connects via DATABASE_MIGRATE_URL (webapp/backend/app/config.py:33 — database_migrate_url, documented in .env.example:45) so migrations keep working after the tightening; fix the Alembic env if it turns out to use the app URL.

Acceptance criteria

On a fresh docker compose up (empty volume), connecting as questboard and running CREATE TABLE t (id int) fails with a permission error; normal app CRUD works.
alembic upgrade head succeeds (migrate role).
Tables created by a subsequent migration are readable/writable by the app user without manual grants (default privileges work).
webapp/postgres/init.sql no longer exists; no references remain.
docs/OPERATIONS.md documents the manual statements for existing installs.

References

webapp/postgres/init.sh (current init; migrate role only)
webapp/postgres/init.sql (orphaned; hardcoded password at :10; tightening only as comment at :20-24)
docker-compose.yml:20 (init.sh mount — the only init mount)
webapp/backend/app/config.py:33 (database_migrate_url for Alembic), .env.example:41/:45
Related docs correction tracked in the stale-documentation hardening issue (this milestone)

Filed from the July 2026 full-project review.

## Context Project docs (root `CLAUDE.md` and `webapp/CLAUDE.md`) describe a two-role database model: a DML-only `questboard` app user and a DDL-capable `questboard_migrate` role for Alembic. The runtime does not enforce this: - `POSTGRES_USER=questboard` is created by the postgres Docker entrypoint as a **SUPERUSER** — the app connects with full DDL and admin rights. - The only mounted init script is `webapp/postgres/init.sh` (mounted at `docker-compose.yml:20`), which only creates/updates the `questboard_migrate` role and grants it privileges (`init.sh:9-25`). It contains **no REVOKE/GRANT tightening for the app user**. - `webapp/postgres/init.sql` is a dead file: it is mounted **nowhere** (repo-wide, the only reference to it is a mention in `webapp/CLAUDE.md`), it hardcodes the password `changeme_migrate` (`init.sql:10`), and — contrary to what one might expect — the app-user tightening exists there **only as a SQL comment** (`init.sql:20-24`), not as executable statements. So a SQL-injection or app-level compromise has superuser DB access, and the documented privilege model is fiction. ## Fix / Spec 1. In `webapp/postgres/init.sh`, after the existing migrate-role block, add the app-user tightening for fresh installs: ```sql REVOKE CREATE ON SCHEMA public FROM $POSTGRES_USER; -- app user keeps DML only: GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO $POSTGRES_USER; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO $POSTGRES_USER; ALTER DEFAULT PRIVILEGES FOR ROLE questboard_migrate IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO $POSTGRES_USER; ALTER DEFAULT PRIVILEGES FOR ROLE questboard_migrate IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO $POSTGRES_USER; ALTER ROLE $POSTGRES_USER NOSUPERUSER NOCREATEDB NOCREATEROLE; ``` (Adjust exact statements as needed; the key outcomes are: app user is not superuser, cannot CREATE TABLE, retains DML on current and future tables/sequences created by the migrate role.) 2. Delete `webapp/postgres/init.sql`. 3. Add an "Existing installs" runbook section to `docs/OPERATIONS.md` with the one-time `ALTER ROLE ... NOSUPERUSER` + REVOKE/GRANT statements for deployments whose data dir predates this change (init scripts only run on empty data dirs). 4. Verify Alembic connects via `DATABASE_MIGRATE_URL` (`webapp/backend/app/config.py:33` — `database_migrate_url`, documented in `.env.example:45`) so migrations keep working after the tightening; fix the Alembic env if it turns out to use the app URL. ## Acceptance criteria - On a fresh `docker compose up` (empty volume), connecting as `questboard` and running `CREATE TABLE t (id int)` fails with a permission error; normal app CRUD works. - `alembic upgrade head` succeeds (migrate role). - Tables created by a subsequent migration are readable/writable by the app user without manual grants (default privileges work). - `webapp/postgres/init.sql` no longer exists; no references remain. - `docs/OPERATIONS.md` documents the manual statements for existing installs. ## References - `webapp/postgres/init.sh` (current init; migrate role only) - `webapp/postgres/init.sql` (orphaned; hardcoded password at `:10`; tightening only as comment at `:20-24`) - `docker-compose.yml:20` (init.sh mount — the only init mount) - `webapp/backend/app/config.py:33` (`database_migrate_url` for Alembic), `.env.example:41/:45` - Related docs correction tracked in the stale-documentation hardening issue (this milestone) _Filed from the July 2026 full-project review._

claude-bot added the

backend

bug

labels

2026-07-14 19:49:35 +00:00

claude-bot added this to the v3.3.0 - Hardening: Bugs & Security milestone

2026-07-14 19:49:36 +00:00

claude-bot referenced this issue

2026-07-14 20:26:48 +00:00

[Hardening] Proxy /public/ so the public analytics share page works #92

claude-bot commented

2026-07-14 20:26:52 +00:00

Author

Collaborator

Picking this up as part of a v3.3.0 push. Landing on branch hardening/infra-docs together with #92 and #113 (grouped by component to keep the diffs reviewable). Verifying the privilege tightening against a throwaway Postgres on an empty data dir — app user must fail CREATE TABLE, migrate user must succeed, and the app user must still be able to DML a migrate-created table via default privileges.

Picking this up as part of a v3.3.0 push. Landing on branch `hardening/infra-docs` together with #92 and #113 (grouped by component to keep the diffs reviewable). Verifying the privilege tightening against a throwaway Postgres on an empty data dir — app user must fail `CREATE TABLE`, migrate user must succeed, and the app user must still be able to DML a migrate-created table via default privileges.

claude-bot referenced this issue

2026-07-14 20:26:55 +00:00

[Hardening] Correct stale documentation: recording architecture, DB roles, stack versions #113

claude-bot commented

2026-07-14 21:34:30 +00:00

Author

Collaborator

Fixed on main (commit 8ffef01, merged via 9448593) — but not the way the issue specified, because the specified approach is impossible in PostgreSQL.

Why the spec couldn't work: the issue's ALTER ROLE $POSTGRES_USER NOSUPERUSER crashes the postgres entrypoint on every fresh install. Postgres permanently marks the initdb bootstrap role (which is POSTGRES_USER) as requiring SUPERUSER and refuses to strip it — ERROR: The bootstrap user must have the SUPERUSER attribute, regardless of which role issues the ALTER. And since a superuser bypasses all GRANT/REVOKE checks, the whole REVOKE/GRANT scaffolding is inert as long as the app connects as the bootstrap role.

What shipped instead — stop making the app user the bootstrap role. POSTGRES_USER is now a dedicated superuser questboard_admin (break-glass admin, never used by the app or Alembic). init.sh creates two purpose-built roles: questboard_migrate (DDL, owns the schema, used by Alembic and pg_dump) and questboard (LOGIN, NOSUPERUSER/NOCREATEDB/NOCREATEROLE, DML-only via DATABASE_URL). No rename hackery, no NOLOGIN roles, break-glass superuser login preserved.

Backups fixed in the same change — this was a hidden landmine: pg_dump in both cli.py (backup_now) and the scheduled reminder_tasks.py backup task built their connection from settings.database_url (the app role), which only worked because that role was a superuser. Both now use settings.database_migrate_url (the schema owner), so backups keep working after the app role is demoted.

Verified end-to-end on a throwaway postgres:16-alpine (empty data dir), independently re-run before merge:

roles: questboard_admin rolsuper=t canlogin=t; questboard / questboard_migrate rolsuper=f
alembic upgrade head as the migrate role → success
app user CREATE TABLE → ERROR: permission denied for schema public
app user SELECT on the real migrated campaigns table → success (default-privilege propagation confirmed)
pg_dump --format=custom as the migrate role → exit 0, 94584-byte archive containing all real tables

init.sql deleted (was mounted nowhere, hardcoded a password, and held the "tightening" only as a comment). docs/OPERATIONS.md gains an "Existing Installs" runbook for deployments whose data dir predates this split (init scripts only run once, on an empty data dir): rename questboard → questboard_admin keeping superuser, create a fresh non-superuser questboard, apply the grants. alembic/env.py already connects via database_migrate_url — no change needed there.

Fixed on `main` (commit `8ffef01`, merged via `9448593`) — but **not the way the issue specified**, because the specified approach is impossible in PostgreSQL. **Why the spec couldn't work:** the issue's `ALTER ROLE $POSTGRES_USER NOSUPERUSER` crashes the postgres entrypoint on every fresh install. Postgres permanently marks the initdb bootstrap role (which *is* `POSTGRES_USER`) as requiring SUPERUSER and refuses to strip it — `ERROR: The bootstrap user must have the SUPERUSER attribute`, regardless of which role issues the ALTER. And since a superuser bypasses all GRANT/REVOKE checks, the whole REVOKE/GRANT scaffolding is inert as long as the app connects as the bootstrap role. **What shipped instead — stop making the app user the bootstrap role.** `POSTGRES_USER` is now a dedicated superuser `questboard_admin` (break-glass admin, never used by the app or Alembic). `init.sh` creates two purpose-built roles: `questboard_migrate` (DDL, owns the schema, used by Alembic **and** `pg_dump`) and `questboard` (LOGIN, NOSUPERUSER/NOCREATEDB/NOCREATEROLE, DML-only via `DATABASE_URL`). No rename hackery, no NOLOGIN roles, break-glass superuser login preserved. **Backups fixed in the same change** — this was a hidden landmine: `pg_dump` in both `cli.py` (`backup_now`) and the scheduled `reminder_tasks.py` backup task built their connection from `settings.database_url` (the app role), which only worked because that role *was* a superuser. Both now use `settings.database_migrate_url` (the schema owner), so backups keep working after the app role is demoted. **Verified end-to-end** on a throwaway `postgres:16-alpine` (empty data dir), independently re-run before merge: - roles: `questboard_admin` rolsuper=t canlogin=t; `questboard` / `questboard_migrate` rolsuper=f - `alembic upgrade head` as the migrate role → success - app user `CREATE TABLE` → `ERROR: permission denied for schema public` - app user `SELECT` on the real migrated `campaigns` table → success (default-privilege propagation confirmed) - `pg_dump --format=custom` as the migrate role → exit 0, 94584-byte archive containing all real tables `init.sql` deleted (was mounted nowhere, hardcoded a password, and held the "tightening" only as a comment). `docs/OPERATIONS.md` gains an "Existing Installs" runbook for deployments whose data dir predates this split (init scripts only run once, on an empty data dir): rename `questboard` → `questboard_admin` keeping superuser, create a fresh non-superuser `questboard`, apply the grants. `alembic/env.py` already connects via `database_migrate_url` — no change needed there.

claude-bot referenced this issue

2026-07-14 21:34:37 +00:00

[Hardening] Correct stale documentation: recording architecture, DB roles, stack versions #113

claude-bot closed this issue

2026-07-14 21:34:52 +00:00