ADR 0003 — Tenancy is enforced by Postgres

ADR 0003 — Tenancy is enforced by Postgres An architecture diagram generated by Archify. Tenancy is enforced by Postgres · FORCE ROW LEVEL SECURITY · a per-transaction setting · Architecture component Tenancy is enforced by Postgres FORCE ROW LEVEL SECURITY · a per-transaction setting policy on every tenant table · tenant_id = current_setting('platformkit.tenant_id') · the mechanism policy on every tenant table tenant_id = current_setting('platformkit.tenant_id') db.Run · db.RunSystem · set_config(…, true) · re-read before COMMIT · the mechanism db.Run · db.RunSystem set_config(…, true) · re-read before COMMIT Tx[Tenant] · Tx[System] · phantom types · System only from kit/ · the mechanism Tx[Tenant] · Tx[System] phantom types · System only from kit/ scripts/check_gucs.sh · only kit/db writes platformkit.* · kept true by scripts/check_gucs.sh only kit/db writes platformkit.* Rejected: a WHERE predicate by convention · a forgotten clause leaked a tenant · Architecture component Rejected: a WHERE predicate by convention a forgotten clause leaked a tenant every repository · takes Tx[Tenant] only · Architecture component every repository takes Tx[Tenant] only platformkit_app role · NOSUPERUSER NOBYPASSRLS · Architecture component platformkit_app role NOSUPERUSER NOBYPASSRLS is placed by typed as checked by instead of bound by the policy compile error otherwise the mechanism kept true by Legend Backend Database Security External

Context

  • • Before: a Go predicate by convention, row-level security on 22 of 54 modules, nothing on the rest, and a superuser connection
  • • A forgotten WHERE tenant_id = ? leaked another tenant

Consequences

  • • A missing WHERE returns no rows; cross-tenant work asks for Tx[System] and is greppable
  • • One extra round trip per transaction; tests run as the app role against a real Postgres

Evidence

  • • go test ./kit/db -run TestTenantIsolationIsEnforcedByPostgres, and the FORCE, rewrite and restoring-escape cases beside it
  • • scripts/check_gucs.sh in make check; kit/db/scope_compile_test.go behind //go:build never