Enterprise Multi-Tenancy & Row-Level Security Patterns

Every multi-tenant system enforces isolation somewhere. The only question is whether that somewhere is a place a developer can forget. A where tenant_id = ? in application code is correct until the day someone writes a report query, an admin tool, or a background job without it — and that query will not fail. It will quietly return everyone's data.

Row-level security moves the check into the database, below every query path. It is not free and it is not automatic, but it changes the default from "isolated if remembered" to "isolated unless explicitly bypassed".

Three tenancy models

Start with shared schema unless something specific forces otherwise. Migrating from shared schema to per-database later is tedious but well understood. Migrating in the other direction, after you have accumulated a hundred databases to keep in step, is worse.

Writing the policies

Enabling RLS is two statements, and the second one is the one people miss:

alter table invoices enable row level security;
alter table invoices force row level security;

Without force, the table's owner is exempt from its own policies. Application code very often connects as the owner, which means RLS appears configured and does nothing at all. This is the single most common misconfiguration we find.

Then the policy itself. Separate using from with check deliberately — the first filters rows that are read, the second validates rows being written:

create policy tenant_read on invoices
  for select
  using (tenant_id = current_tenant_id());

create policy tenant_write on invoices
  for insert
  with check (tenant_id = current_tenant_id());

create policy tenant_update on invoices
  for update
  using (tenant_id = current_tenant_id())        -- which rows may be targeted
  with check (tenant_id = current_tenant_id());  -- what they may become

The with check on update matters more than it looks. Without it, a tenant can take a row they legitimately own and reassign its tenant_id to someone else — a write that passes the using clause because the row was theirs at the moment it was selected.

Getting tenant into the session

The policies above call current_tenant_id(). That function has to read something the client cannot forge. Two workable approaches:

From a verified JWT claim

create or replace function current_tenant_id() returns uuid
language sql stable
as $$
  select nullif(
    current_setting('request.jwt.claims', true)::jsonb ->> 'tenant_id',
    ''
  )::uuid
$$;

This is safe only because the claims were verified by the gateway before the setting was populated. If any path lets a client set that GUC directly, the whole model collapses.

From a transaction-scoped setting

begin;
  set local app.tenant_id = '8f14e45f-ceea-467a-9f9e-2f4f3d6c1a2b';
  -- queries here are scoped to that tenant
commit;

Use set local, never set. With a connection pooler, a plain set persists on the pooled connection after your request finishes and leaks into whichever request picks up that connection next. set local is scoped to the transaction and cleared on commit or rollback.

Four ways RLS is bypassed

Policies are necessary but not sufficient. These are the doors that stay open:

1. Table owner without force. Covered above. Verify by connecting as your application role and confirming a cross-tenant select returns zero rows.
2. Roles with BYPASSRLS. Superusers and any role granted this attribute ignore policies entirely. Your migration role may legitimately need it; your application role must never have it.
3. SECURITY DEFINER functions. These run with the privileges of the function's owner, not the caller — so a helper written for convenience can hand back rows the caller could never select directly. If you need one, set an explicit search_path and re-apply the tenant filter inside the function body.
4. Views. Historically a view ran with its owner's privileges, silently bypassing the underlying table's policies. Postgres 15 added security_invoker for exactly this; set it on any view over a tenant-scoped table: alter view v set (security_invoker = true);

Performance

RLS appends your policy predicate to every query, so the predicate has to be cheap and indexable.

• Index tenant_id first in composite indexes. Nearly every query is now implicitly filtered by it, so it belongs at the leading edge: create index on invoices (tenant_id, created_at desc);
• Mark the context function stable. A volatile function may be re-evaluated per row, which is catastrophic on a large scan. stable lets the planner evaluate it once.
• Read the plan with RLS on. Run explain analyze as the application role, not as superuser — as superuser the policy is not applied and the plan you are reading is not the plan you will get.

Testing that it holds

Isolation is a property worth asserting in CI, because it degrades silently. A new table added without RLS produces no error — just an open table.

Two tests earn their keep. First, a guard that every tenant-scoped table actually has RLS enabled and forced:

select c.relname
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
  and c.relkind = 'r'
  and exists (
    select 1 from information_schema.columns
    where table_schema = 'public'
      and table_name = c.relname
      and column_name = 'tenant_id'
  )
  and (c.relrowsecurity = false or c.relforcerowsecurity = false);
-- must return zero rows

Second, an actual cross-tenant attempt, as the application role:

begin;
  set local role app_user;
  set local app.tenant_id = '';

  -- seeded rows belonging to tenant B must be invisible
  select count(*) from invoices where tenant_id = '';  -- expect 0

  -- and unreachable by write
  insert into invoices (tenant_id, total) values ('', 100);
  -- expect: new row violates row-level security policy
rollback;

Run both on every migration. The first catches the table someone added last Tuesday; the second catches the policy that was written but has a subtly wrong predicate.

Row-level security is not a substitute for authorisation logic in your application — you still need to decide what a given user may do within their tenant. What it gives you is a floor: a mistake in that logic becomes a bug for one customer, rather than a breach across all of them.