julien
6137486d64
## Summary
The bootstrap SQL ended with:
```sql
GRANT audit_owner, audit_writer, audit_reader, audit_archiver TO portal;
```
— hard-coded `portal`. The compose file and `.env.example` both document `POSTGRES_USER` as overridable; any contributor who changed it hit:
```
ERROR: role "portal" does not exist
psql: /docker-entrypoint-initdb.d/01-init.sql:48: ERROR: role "portal" does not exist
```
Replace with `current_user`, which resolves at execution time to whoever is running the init SQL — i.e. the superuser Postgres just created from `POSTGRES_USER`, whatever its name.
## Recovery for anyone hit by the bug
The half-failed init left the postgres-data volume in a partially-initialised state. To reset:
```bash
cd infra/local
docker compose -f dev.compose.yml down -v # wipes the volume
docker compose -f dev.compose.yml up -d # bootstrap re-runs cleanly
```
## Test plan
- [ ] After merge + recovery: `docker compose ps` shows postgres healthy.
- [ ] `psql postgres://<user>:<pwd>@localhost:5432/portal_dev -c "\du"` lists the four `audit_*` roles, and your superuser is "Member of: {audit_owner, audit_writer, audit_reader, audit_archiver}".
- [ ] `psql ... -c "\dn"` shows the `audit` schema.
- [ ] Test with a non-default `POSTGRES_USER` value (set `POSTGRES_USER=apf_portal` in `.env`, wipe volume, re-up) — init still succeeds.
---------
Co-authored-by: Julien Gautier <julien.gautier@apf.asso.fr>
Reviewed-on: #60
58 lines
2.7 KiB
SQL
58 lines
2.7 KiB
SQL
-- Bootstrap SQL for local-dev Postgres, applied on FIRST boot only
|
|
-- (Postgres image runs files in /docker-entrypoint-initdb.d once,
|
|
-- when the data volume is empty).
|
|
--
|
|
-- Implements the role + schema layout from ADR-0013 (Audit trail,
|
|
-- separated Postgres schema, append-only by Postgres role grants).
|
|
-- Production deployment manifests (future infrastructure ADR) will
|
|
-- replicate the same layout with real service accounts; in dev the
|
|
-- default `portal` user (created automatically from POSTGRES_USER)
|
|
-- is the schema owner, and the audit roles are granted to it for
|
|
-- testing role-based access patterns locally.
|
|
|
|
-- ---------------------------------------------------------------- Audit roles
|
|
-- NOLOGIN: these are permission containers, not login users. The BFF
|
|
-- connects as a login user that has been GRANTed the role.
|
|
CREATE ROLE audit_owner NOLOGIN;
|
|
CREATE ROLE audit_writer NOLOGIN;
|
|
CREATE ROLE audit_reader NOLOGIN;
|
|
CREATE ROLE audit_archiver NOLOGIN;
|
|
|
|
-- ---------------------------------------------------------------- Audit schema
|
|
-- Owned by audit_owner so DEFAULT PRIVILEGES below take effect for
|
|
-- every future table created under the schema (Prisma migration etc.).
|
|
CREATE SCHEMA audit AUTHORIZATION audit_owner;
|
|
|
|
GRANT USAGE ON SCHEMA audit TO audit_writer, audit_reader, audit_archiver;
|
|
|
|
-- ---------------------------------------------------------------- Append-only contract
|
|
-- ADR-0013 §"Append-only by role grants":
|
|
-- audit_writer → INSERT only
|
|
-- audit_reader → SELECT only
|
|
-- audit_archiver → DELETE only (used to prune past retention)
|
|
-- nobody → UPDATE, TRUNCATE
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE audit_owner IN SCHEMA audit
|
|
GRANT INSERT ON TABLES TO audit_writer;
|
|
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE audit_owner IN SCHEMA audit
|
|
GRANT SELECT ON TABLES TO audit_reader;
|
|
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE audit_owner IN SCHEMA audit
|
|
GRANT DELETE ON TABLES TO audit_archiver;
|
|
|
|
-- ---------------------------------------------------------------- Dev convenience
|
|
-- The dev superuser (created by Postgres from POSTGRES_USER, default
|
|
-- `portal` but overridable in infra/local/.env) bypasses these grants
|
|
-- anyway, but granting the audit roles explicitly lets us test
|
|
-- role-based access with `SET ROLE audit_writer;` etc. from a psql
|
|
-- session against the dev DB.
|
|
--
|
|
-- `current_user` resolves at script execution time to whoever is
|
|
-- running the init SQL — i.e. the superuser created from POSTGRES_USER,
|
|
-- whatever its name happens to be. Hard-coding `portal` here was
|
|
-- wrong: it broke any setup where the contributor changed
|
|
-- POSTGRES_USER in their .env.
|
|
--
|
|
-- Production never grants all four roles to one user.
|
|
GRANT audit_owner, audit_writer, audit_reader, audit_archiver TO current_user;
|