psa-systems/mokosh-server
Watch
9
0
Fork
You've already forked mokosh-server
 1
Code Issues Pull requests Projects Releases 10 Packages Wiki Activity Actions

feat(db): self-provision split DB roles at startup (PMS-489) #381

Merged
Claude-Run merged 1 commit from feat/PMS-489-self-provision-db-roles into main 2026-06-26 13:27:28 +02:00
Conversation 0 Commits 1 Files changed 8 +257 -193
Claude-Run commented 2026-06-26 13:20:52 +02:00
Member
Copy link

Unify role provisioning into one env-driven mechanism identical in dev and prod. The server now self-bootstraps the split roles at startup, before connecting the request pools and running migrations, replacing both the dev scripts/pg-init.sh initdb script and the prod mokosh-bootstrap provision-roles CLI step.

New src/db/provision::provision_roles (called from main before Database::new): probes whether mokosh_migrator can already log in and skips entirely if so, so prod can drop the admin credentials after the first boot. Otherwise it connects with the privileged MOKOSH_ADMIN_DATABASE_URL, idempotently creates mokosh_migrator (LOGIN BYPASSRLS) and mokosh_app (LOGIN NOSUPERUSER NOBYPASSRLS), reconciles the app-role grants, and closes the admin pool immediately. It also grants the migrator CREATE ON DATABASE - the grant the old provision-roles step omitted - so migrations self-install the trusted uuid-ossp/pg_trgm/citext/pgcrypto extensions and create the mokosh_auth schema with no permission errors.

Remove the provision-roles subcommand (plus its now-unused sql_quote/quote_ident helpers and help text) from mokosh-bootstrap; the binary deletion itself is tracked in PMS-492.

Delete scripts/pg-init.sh and its initdb.d mount in compose.dev.yml, drop the false "untrusted extension" comments, move MOKOSH_MIGRATOR_PASSWORD/MOKOSH_APP_PASSWORD onto the server service, and add MOKOSH_ADMIN_DATABASE_URL (the postgres superuser) to the server env and .env.example.

RLS posture is unchanged: the app role stays NOSUPERUSER NOBYPASSRLS so a missing tenant filter fail-closes to zero rows; the migrator grant change is additive.

#PMS-489

Unify role provisioning into one env-driven mechanism identical in dev and prod. The server now self-bootstraps the split roles at startup, before connecting the request pools and running migrations, replacing both the dev `scripts/pg-init.sh` initdb script and the prod `mokosh-bootstrap provision-roles` CLI step. New `src/db/provision::provision_roles` (called from `main` before `Database::new`): probes whether `mokosh_migrator` can already log in and skips entirely if so, so prod can drop the admin credentials after the first boot. Otherwise it connects with the privileged `MOKOSH_ADMIN_DATABASE_URL`, idempotently creates `mokosh_migrator` (LOGIN BYPASSRLS) and `mokosh_app` (LOGIN NOSUPERUSER NOBYPASSRLS), reconciles the app-role grants, and closes the admin pool immediately. It also grants the migrator `CREATE ON DATABASE` - the grant the old `provision-roles` step omitted - so migrations self-install the trusted `uuid-ossp`/`pg_trgm`/`citext`/`pgcrypto` extensions and create the `mokosh_auth` schema with no permission errors. Remove the `provision-roles` subcommand (plus its now-unused `sql_quote`/`quote_ident` helpers and help text) from `mokosh-bootstrap`; the binary deletion itself is tracked in PMS-492. Delete `scripts/pg-init.sh` and its initdb.d mount in `compose.dev.yml`, drop the false "untrusted extension" comments, move `MOKOSH_MIGRATOR_PASSWORD`/`MOKOSH_APP_PASSWORD` onto the server service, and add `MOKOSH_ADMIN_DATABASE_URL` (the postgres superuser) to the server env and `.env.example`. RLS posture is unchanged: the app role stays NOSUPERUSER NOBYPASSRLS so a missing tenant filter fail-closes to zero rows; the migrator grant change is additive. #PMS-489
feat(db): self-provision split DB roles at startup (PMS-489)
All checks were successful
E2E / Playwright against staging (pull_request) Successful in 28s
Details
Check / fmt + clippy + build + tests (pull_request) Successful in 2m21s
Details
Integration / integration tests (pull_request) Successful in 6m17s
Details
Create release / Gate (release-branch merges only) (pull_request) Successful in 1s
Details
Create release / Create release from merged PR (pull_request) Has been skipped
Details
c73584ad67 
Unify role provisioning into one env-driven mechanism identical in dev and prod. The server now self-bootstraps the split roles at startup, before connecting the request pools and running migrations, replacing both the dev `scripts/pg-init.sh` initdb script and the prod `mokosh-bootstrap provision-roles` CLI step.

New `src/db/provision::provision_roles` (called from `main` before `Database::new`): probes whether `mokosh_migrator` can already log in and skips entirely if so, so prod can drop the admin credentials after the first boot. Otherwise it connects with the privileged `MOKOSH_ADMIN_DATABASE_URL`, idempotently creates `mokosh_migrator` (LOGIN BYPASSRLS) and `mokosh_app` (LOGIN NOSUPERUSER NOBYPASSRLS), reconciles the app-role grants, and closes the admin pool immediately. It also grants the migrator `CREATE ON DATABASE` - the grant the old `provision-roles` step omitted - so migrations self-install the trusted `uuid-ossp`/`pg_trgm`/`citext`/`pgcrypto` extensions and create the `mokosh_auth` schema with no permission errors.

Remove the `provision-roles` subcommand (plus its now-unused `sql_quote`/`quote_ident` helpers and help text) from `mokosh-bootstrap`; the binary deletion itself is tracked in PMS-492.

Delete `scripts/pg-init.sh` and its initdb.d mount in `compose.dev.yml`, drop the false "untrusted extension" comments, move `MOKOSH_MIGRATOR_PASSWORD`/`MOKOSH_APP_PASSWORD` onto the server service, and add `MOKOSH_ADMIN_DATABASE_URL` (the postgres superuser) to the server env and `.env.example`.

RLS posture is unchanged: the app role stays NOSUPERUSER NOBYPASSRLS so a missing tenant filter fail-closes to zero rows; the migrator grant change is additive.

#PMS-489
Sign in to join this conversation.
Reviewers
No reviewers
Labels
Clear labels
No items
No labels
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
psa-systems/mokosh-server!381
No description provided.