feat(db): route tenant-scoped service queries through begin_with_tenant

David commented

2026-06-12 14:23:21 +02:00

Owner

Step 1 of 9 toward fail-closed RLS (parent PMS-255). Every tenant-scoped read and write across the 20 PSA modules now runs inside a transaction that has set the app.current_tenant GUC via Database::begin_with_tenant, so the RLS tenant_isolation policy can later be flipped fail-closed. The policy stays fail-open in this PR, so there is no behavior change yet: this only guarantees the GUC is always present.

begin_with_tenant now takes impl Into<Uuid> (new From<TenantId> for Uuid) so call sites pass either type unchanged, and TenantTransaction derefs to sqlx::PgConnection so the standard &mut *tx idiom yields a sqlx executor (sqlx 0.8 has no Executor for &mut Transaction). Standalone pooled reads were wrapped in read-only tenant txs; standalone pooled writes were wrapped in tenant txs with an explicit commit() (a pooled execute auto-commits, a transaction must not be dropped uncommitted). Existing WHERE tenant_id = $N filters are kept for defense in depth.

Cross-function-boundary writes were threaded through new service methods so the worker/route layers stay GUC-correct: CalendarService::claim_reminder, SlaService::claim_sla_notification, ReportsService::run_custom (with custom::run now taking a PgExecutor), and the auth login/logout out-of-band audit writes.

Deliberate exceptions (no single tenant to bind, or tables RLS does not cover) are kept on the pool and catalogued in dev-docs/audits/pms-256-rls-guc-audit.md: the tenants table itself, cross-tenant provisioning copies, cross-tenant background-worker sweeps, pre-tenant-resolution identity lookups (login/reset/portal/bootstrap), sla_targets/notification-template tables that have no tenant_id column, and the generic audit_write helper. The doc also lists the two follow-ups for PMS-255.2 (rmm sync-worker id-only writes need a tenant threaded in; rehome_user_between_tenants needs the source-vs-destination GUC decided).

Verified: just check clean; cargo test --lib (117) and cargo test --doc pass; all integration test binaries pass except the two tests/settings.rs module-gating tests, which fail identically on unmodified main in this environment and are outside CI's integration set.

#PMS-256

Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com

Step 1 of 9 toward fail-closed RLS (parent PMS-255). Every tenant-scoped read and write across the 20 PSA modules now runs inside a transaction that has set the `app.current_tenant` GUC via `Database::begin_with_tenant`, so the RLS `tenant_isolation` policy can later be flipped fail-closed. The policy stays fail-open in this PR, so there is no behavior change yet: this only guarantees the GUC is always present. `begin_with_tenant` now takes `impl Into<Uuid>` (new `From<TenantId> for Uuid`) so call sites pass either type unchanged, and `TenantTransaction` derefs to `sqlx::PgConnection` so the standard `&mut *tx` idiom yields a sqlx executor (sqlx 0.8 has no `Executor for &mut Transaction`). Standalone pooled reads were wrapped in read-only tenant txs; standalone pooled writes were wrapped in tenant txs with an explicit `commit()` (a pooled `execute` auto-commits, a transaction must not be dropped uncommitted). Existing `WHERE tenant_id = $N` filters are kept for defense in depth. Cross-function-boundary writes were threaded through new service methods so the worker/route layers stay GUC-correct: `CalendarService::claim_reminder`, `SlaService::claim_sla_notification`, `ReportsService::run_custom` (with `custom::run` now taking a `PgExecutor`), and the auth login/logout out-of-band audit writes. Deliberate exceptions (no single tenant to bind, or tables RLS does not cover) are kept on the pool and catalogued in `dev-docs/audits/pms-256-rls-guc-audit.md`: the `tenants` table itself, cross-tenant provisioning copies, cross-tenant background-worker sweeps, pre-tenant-resolution identity lookups (login/reset/portal/bootstrap), `sla_targets`/notification-template tables that have no `tenant_id` column, and the generic `audit_write` helper. The doc also lists the two follow-ups for PMS-255.2 (rmm sync-worker id-only writes need a tenant threaded in; `rehome_user_between_tenants` needs the source-vs-destination GUC decided). Verified: `just check` clean; `cargo test --lib` (117) and `cargo test --doc` pass; all integration test binaries pass except the two `tests/settings.rs` module-gating tests, which fail identically on unmodified `main` in this environment and are outside CI's integration set. #PMS-256 Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

David added 1 commit

2026-06-12 14:23:22 +02:00

feat(db): route tenant-scoped service queries through begin_with_tenant

E2E (staging) / Playwright against staging (pull_request) Successful in 37s

Details

Check / fmt + clippy + compile + tests (pull_request) Successful in 1m33s

Details

Build OCI container / Build and push mokosh-api image (push) Successful in 3m31s

Details

bb76afba79

Step 1 of 9 toward fail-closed RLS (parent PMS-255). Every tenant-scoped read and write across the 20 PSA modules now runs inside a transaction that has set the `app.current_tenant` GUC via `Database::begin_with_tenant`, so the RLS `tenant_isolation` policy can later be flipped fail-closed. The policy stays fail-open in this PR, so there is no behavior change yet: this only guarantees the GUC is always present.

`begin_with_tenant` now takes `impl Into<Uuid>` (new `From<TenantId> for Uuid`) so call sites pass either type unchanged, and `TenantTransaction` derefs to `sqlx::PgConnection` so the standard `&mut *tx` idiom yields a sqlx executor (sqlx 0.8 has no `Executor for &mut Transaction`). Standalone pooled reads were wrapped in read-only tenant txs; standalone pooled writes were wrapped in tenant txs with an explicit `commit()` (a pooled `execute` auto-commits, a transaction must not be dropped uncommitted). Existing `WHERE tenant_id = $N` filters are kept for defense in depth.

Cross-function-boundary writes were threaded through new service methods so the worker/route layers stay GUC-correct: `CalendarService::claim_reminder`, `SlaService::claim_sla_notification`, `ReportsService::run_custom` (with `custom::run` now taking a `PgExecutor`), and the auth login/logout out-of-band audit writes.

Deliberate exceptions (no single tenant to bind, or tables RLS does not cover) are kept on the pool and catalogued in `dev-docs/audits/pms-256-rls-guc-audit.md`: the `tenants` table itself, cross-tenant provisioning copies, cross-tenant background-worker sweeps, pre-tenant-resolution identity lookups (login/reset/portal/bootstrap), `sla_targets`/notification-template tables that have no `tenant_id` column, and the generic `audit_write` helper. The doc also lists the two follow-ups for PMS-255.2 (rmm sync-worker id-only writes need a tenant threaded in; `rehome_user_between_tenants` needs the source-vs-destination GUC decided).

Verified: `just check` clean; `cargo test --lib` (117) and `cargo test --doc` pass; all integration test binaries pass except the two `tests/settings.rs` module-gating tests, which fail identically on unmodified `main` in this environment and are outside CI's integration set.

#PMS-256

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

David added 1 commit

2026-06-13 12:57:23 +02:00

Merge branch 'main' into feat/pms-256-rls-guc-plumbing

Create release / Create release from merged PR (pull_request) Has been skipped

Details

E2E (staging) / Playwright against staging (pull_request) Successful in 33s

Details

Check / fmt + clippy + compile + tests (pull_request) Successful in 3m27s

Details

Build OCI container / Build and push mokosh-api image (push) Successful in 4m26s

Details

c929e7cb30