ADR 0004 — PostgreSQL 18 with row-level security for tenant isolation

Status: Accepted · Date: 2026-07-23

Context

Cross-tenant PHI exposure is the highest-impact failure. Application WHERE clauses alone are one forgotten predicate away from a breach.

Decision

Every tenant-owned table carries clinic_id (and organization_id where the row is org-scoped). RLS is enabled and FORCED on all tenant tables. The API role (dental_app) has no BYPASSRLS. core-api sets per-transaction GUCs:

SELECT set_config('app.org_id',  $1, true);
SELECT set_config('app.clinic_ids', $2, true);  -- csv of authorized clinics
SELECT set_config('app.actor_id', $3, true);
SELECT set_config('app.purpose', $4, true);

Policies compare row tenancy against those GUCs. Platform-admin access flows through a separate audited role (dental_platform) whose every statement is wrapped by a support-access grant row (break-glass, time-boxed, fully audited). Migrations run as dental_migrator.

Consequences

• Defense in depth: application authz AND database policy must both fail for a cross-tenant read to occur.
• All connection acquisition goes through one pool helper that refuses to hand out a connection without tenancy GUCs (except explicitly-marked system jobs).
• Automated cross-tenant tests assert zero leakage per table.