Tenant isolation with Postgres row level security

Sovica is a multi-tenant platform: every clinic shares one Postgres 17 database and one schema. That is simple to run, but it puts one rule above all others. A clinic must never see another clinic’s data, not through a bug, a forgotten filter or a clever URL.

We did not want that rule to depend on every engineer remembering a WHERE clause. So the database enforces it.

Every row knows its clinic

Every tenant-owned table has a tenant_id column, and its primary key is the pair (tenant_id, id). Foreign keys include tenant_id too, so a row can only ever point at rows of the same clinic. Ids are UUIDv7, which sort by creation time and keep indexes tidy.

Forced row level security

Each table enables and forces row level security, and gets the same policy:

alter table crm.marketing_list enable row level security;
alter table crm.marketing_list force row level security;
create policy tenant_isolation on crm.marketing_list to app_rw
  using (tenant_id = app.current_tenant_id())
  with check (tenant_id = app.current_tenant_id());

The policy every tenant table carries.

force matters: without it, the table owner skips the policy. The application connects as app_rw, a role without BYPASSRLS, so there is no connection in the app that can read across clinics.

Setting the clinic per transaction

Application code only touches tenant data inside withTenant(db, ctx, fn). It opens a transaction and sets the clinic and user for that transaction alone:

return db.transaction(async (tx) => {
  await tx.execute(
    sql`select set_config('app.tenant_id', ${ctx.tenantId ?? ''}, true),
               set_config('app.user_id', ${ctx.userId ?? ''}, true)`,
  );
  return fn(tx);
});

The true makes the setting transaction-local, so a pooled connection never carries one clinic’s context into the next request. When nothing is set, the policy compares against an empty value and returns no rows: it fails closed.

Reads that happen before we know the clinic

Signing in, looking up a session or listing the clinics a person belongs to happen before a clinic is selected. Those few reads are SECURITY DEFINER functions owned by a dedicated role, and users and sessions are isolated per user rather than per clinic. We add new ones sparingly.

A test that fails the build

A guard test asks Postgres for every table the application role can read or write, and fails if any of them lacks enabled and forced row level security. The allow-list of exceptions is empty. Integration tests create a throwaway database per file and connect as app_rw, so every test exercises the real policies, including a cross-tenant negative test for each feature.