Schema-Per-Tenant vs Row-Level Security for HIPAA Workloads

When the rows in question are protected health information, the choice between one PostgreSQL schema per tenant and a single shared table guarded by row-level security stops being a performance debate and becomes a breach-notification and audit-scope decision. This guide sits inside the Schema-Per-Tenant Architecture section of the broader Multi-Tenant Database Isolation Models reference, and it weighs the two isolation models specifically against HIPAA obligations rather than raw throughput.

Problem Framing

Both models can satisfy the HIPAA Security Rule on paper. The Rule requires access controls, audit controls, and integrity safeguards on electronic PHI, and a correctly configured shared database with row-level security meets all three. The difference is not whether you can be compliant — it is what a single mistake costs you, and how much of your estate a regulator or auditor must examine to trust your isolation.

The decision turns on four PHI-specific axes. Blast radius is the amount of PHI a single defect can expose. With RLS, every tenant's rows live in one table; a policy that fails open, a BYPASSRLS role left attached to the application connection, or a query path that forgets to set the tenant GUC exposes all tenants at once. With schema-per-tenant, the same class of bug — a forgotten search_path, a wrong schema name — returns one tenant's data or an empty set, because the other tenants' PHI is not reachable through that connection at all. The boundary is physical (separate objects) rather than logical (a predicate the planner appends).

BAA and audit scope is the second axis. A HIPAA business associate agreement obligates you to demonstrate isolation to covered entities and their auditors. Proving that a WHERE predicate is universally and correctly applied across every code path, ORM, migration, and admin tool is an open-ended evidence problem. Proving that acme's tables are in the acme schema and that acme's role cannot USAGE any other schema is a finite GRANT matrix an auditor can read in one query. The third axis, backup and restore granularity, decides whether you can restore or export one patient population without touching others — trivial with pg_dump --schema=acme, awkward when one tenant's PHI is interleaved across shared tables. The fourth, breach-notification implications, is where the others compound: under the Breach Notification Rule your notification obligation scales with the number of individuals whose PHI was accessible, and a shared table makes the whole population accessible by default.

Step-by-Step Guide

1. Map each model against the HIPAA-specific axes

Before writing any SQL, decide what you are optimizing for. The table below is the core comparison; the rest of the guide implements the safeguards each row implies.

RLS wins on density and migration simplicity; schema-per-tenant wins on every axis that an auditor or a breach lawyer cares about. For PHI workloads under a BAA, the bias is usually toward the smaller blast radius unless tenant count makes per-schema migrations unmanageable.

2. Harden the RLS path if you stay shared

If density forces you to keep one table, the connection must never run as a superuser or a BYPASSRLS role, and the policy must fail closed. Bind isolation to a GUC set per transaction.

ALTER TABLE phi_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE phi_records FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON phi_records
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- application role must NOT have BYPASSRLS
ALTER ROLE app_user NOBYPASSRLS;

FORCE ROW LEVEL SECURITY is the line most teams miss: without it, the table owner bypasses the policy, so a migration or admin tool running as the owner reads every tenant's PHI. The current_setting(..., true) form returns NULL when the GUC is unset, and because NULL = anything is NULL (not true), the policy fails closed — an unset tenant returns zero rows instead of all of them. That is the single most important property for a HIPAA workload on RLS.

3. Set the tenant GUC with transaction scope

Set app.tenant_id with SET LOCAL so it cannot survive a connection return to a transaction-mode pooler such as PgBouncer.

async function withTenant<T>(db: PoolClient, tenantId: string, fn: () => Promise<T>) {
  await db.query("BEGIN");
  try {
    // tenantId resolved from a verified token claim, never raw input
    await db.query("SET LOCAL app.tenant_id = $1", [tenantId]);
    const result = await fn();
    await db.query("COMMIT");
    return result;
  } catch (err) {
    await db.query("ROLLBACK");
    throw err;
  }
}

SET LOCAL is scoped to the transaction, so the moment the connection is checked back into the pool the tenant context is gone. A plain SET (session-level) on a transaction-pooled connection is the classic cross-tenant PHI leak: the next request reuses the connection with the previous patient population still in scope.

4. Provision an isolated schema and role if you go physical

For schema-per-tenant, the boundary is a GRANT wall, not a predicate. Give each tenant its own schema and a role that can reach only that schema.

CREATE SCHEMA acme;
CREATE ROLE acme_app LOGIN PASSWORD :'pw' NOBYPASSRLS;

-- acme_app can see ONLY the acme schema
REVOKE ALL ON SCHEMA public FROM acme_app;
GRANT USAGE ON SCHEMA acme TO acme_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA acme TO acme_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA acme
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO acme_app;

Because acme_app has no USAGE on any other tenant's schema, even a maliciously crafted query naming globex.phi_records is rejected at the privilege layer before the planner runs. This is the evidence an auditor wants: isolation that does not depend on every query being written correctly. Connecting as the per-tenant role rather than just setting search_path is what turns schema-per-tenant from a convention into an enforced boundary.

5. Make backups and breach scope per-tenant

Whichever model you pick, structure backups so one tenant can be exported, restored, or deleted without reading another's PHI — a requirement that also covers data-subject and right-to-delete requests handled under multi-tenant compliance and data governance.

# schema-per-tenant: one tenant, one file, no other PHI touched
pg_dump --schema=acme --no-owner --format=custom \
  "$DATABASE_URL" > acme-$(date +%F).dump

# RLS / shared: must filter, and the dump process still reads the whole table
psql "$DATABASE_URL" -c "\copy (SELECT * FROM phi_records \
  WHERE tenant_id = '$TENANT_ID') TO 'acme.csv' WITH CSV HEADER"

The schema dump never opens another tenant's objects, so the export itself is in-scope for exactly one BAA. The filtered shared export reads the whole table to apply the predicate, which means the backup operator's connection is, momentarily, a path to all PHI — a fact you must document in your risk assessment.

Verification

Prove the boundary holds before any PHI lands. For RLS, confirm the policy fails closed when the GUC is unset and that the owner cannot bypass it.

-- as app_user, with no app.tenant_id set
RESET app.tenant_id;
SELECT count(*) FROM phi_records;  -- expect 0, not all rows

-- as the table owner, FORCE must still apply
SET ROLE table_owner;
SELECT count(*) FROM phi_records;  -- expect 0 without a tenant GUC

Both counts must be 0. A non-zero result means the policy is failing open or FORCE ROW LEVEL SECURITY is missing, and you must not route PHI through it. For schema-per-tenant, confirm the per-tenant role cannot cross the wall.

SET ROLE acme_app;
SELECT count(*) FROM globex.phi_records;
-- expect: ERROR: permission denied for schema globex

An error is the pass condition here — a returned count means acme_app has stray USAGE on globex and the isolation is broken. Fold both checks into a scheduled job that samples tenants and alerts on any deviation, so a regression in provisioning is caught by monitoring rather than by an incident.

Failure Modes & Gotchas

• Application connects with a BYPASSRLS or superuser role. Symptom: queries return every tenant's PHI despite a policy existing. Cause: the role bypasses RLS entirely. Fix: run the app as a NOBYPASSRLS non-owner role and add FORCE ROW LEVEL SECURITY.
• SET instead of SET LOCAL for the tenant GUC. Symptom: a request reads the previous patient's records. Cause: session-level GUC persists across a pooled connection checkout. Fix: always set app.tenant_id with SET LOCAL inside an explicit transaction.
• Schema-per-tenant scoped only by search_path, not by role. Symptom: a fully-qualified other_tenant.table query succeeds. Cause: one shared role has USAGE on all schemas. Fix: connect as a per-tenant role with USAGE on only its own schema.
• Backup tooling reads the whole table to filter one tenant. Symptom: the backup connection is in-scope for all PHI. Cause: filtered export over a shared table. Fix: prefer pg_dump --schema per tenant, or document the shared export path in the risk assessment.

FAQ

Is row-level security HIPAA-compliant for PHI? Yes, RLS can satisfy the HIPAA Security Rule's access and integrity safeguards when the policy uses FORCE ROW LEVEL SECURITY, fails closed on an unset GUC, and the application runs as a NOBYPASSRLS non-owner role. Compliance is not the issue; the larger blast radius and broader audit-evidence burden are why many PHI workloads still prefer schema-per-tenant.

Why does schema-per-tenant reduce breach-notification scope? The Breach Notification Rule ties your obligation to the number of individuals whose PHI was accessible during the incident. A failed isolation check on a shared table makes the entire population accessible, while the same failure under schema-per-tenant reaches at most one tenant's schema, so the notification is bounded to that tenant's patients.

Can I mix the two models? Yes. A common hybrid keeps high-value or large PHI tenants in dedicated schemas while grouping small tenants behind RLS in a shared schema, which is often the practical answer when raw tenant count makes per-schema migrations expensive but a subset of tenants demands the smallest possible blast radius.

Related

• Schema-Per-Tenant Architecture
• Shared Database with Row-Level Security
• Multi-Tenant Compliance & Data Governance