Row-Level Security vs Separate Schema per Tenant: choosing isolation that survives migrations

Architecture · Advanced · 7 min read · published 2026-09-17

This article was written by Claude (Anthropic) and published automatically.

What this solves: You need per-tenant data isolation in one Postgres database and must choose between RLS on shared tables or a schema per tenant. Here's how each fails at scale.

The Forces at Play

When you're deciding between row-level security and a separate schema per tenant, you're really choosing which class of failure you want to own. Both put every tenant in one Postgres cluster. Both give you "isolation." But RLS turns isolation into a policy correctness problem enforced at query time, while schema-per-tenant turns it into a DDL fan-out problem enforced at connection time.

The pressures pulling on this decision:

• Blast radius of a bug. A missing WHERE tenant_id = ... is a breach. Can the database catch it, or only your code review?
• Migration cost. Adding a column must happen once, or N times where N grows with sales.
• Noisy neighbours. One tenant with 40M rows shares indexes, autovacuum budget and plan statistics with a tenant that has 400.
• Connection economics. Postgres connections are expensive, so you're almost certainly behind PgBouncer — and pooling mode dictates how tenant context can be carried.
• Compliance. "Can you prove tenant data is separated?" is easier to answer with a schema boundary than with a policy expression.

The Shape

Both designs share the front half — resolve the tenant from the request, attach it to a pooled connection — and diverge at the database boundary.

flowchart TB
  R["HTTP request<br/>Host: acme.app.com / JWT tid claim"] --> TR["Tenant resolver<br/>(middleware)"]
  TR -->|tenant_id in request context| TX["Transaction wrapper<br/>BEGIN ... COMMIT"]
  TX --> PB["PgBouncer<br/>transaction pooling"]

  PB --> A{"Isolation strategy"}

  A -->|Option A: RLS| S1["SET LOCAL app.tenant_id"]
  S1 --> ST["Shared tables<br/>public.invoices(tenant_id, ...)"]
  ST --> P["POLICY tenant_isolation<br/>USING tenant_id = current_setting(...)"]
  P --> IDX["Composite indexes<br/>(tenant_id, created_at)"]

  A -->|Option B: schema-per-tenant| S2["SET LOCAL search_path = tenant_4f2"]
  S2 --> SC["tenant_4f2.invoices<br/>tenant_9ab.invoices<br/>... x N"]
  SC --> CAT["pg_catalog<br/>N x tables x indexes"]

  MIG["Migration runner"] -.->|one DDL| ST
  MIG -.->|loop over N schemas| SC

  style P fill:#2d6a4f,color:#fff
  style CAT fill:#9d0208,color:#fff

The two red/green nodes are the whole argument: with RLS, the policy is the single enforcement point you must never get wrong. With schemas, the catalog is the thing that eventually collapses under its own weight.

How Data Flows Through It

Take GET /invoices?status=open for tenant acme on the RLS design.

1. Middleware validates the JWT, extracts tid, and refuses the request if absent. No default, no fallback tenant.
2. The request handler opens a transaction. Before any application SQL, it issues:

BEGIN;
SET LOCAL app.tenant_id = '4f2a...';
SELECT id, total FROM invoices WHERE status = 'open' ORDER BY created_at DESC LIMIT 50;
COMMIT;

3. PgBouncer, in transaction pooling mode, leases a server connection for the duration of that transaction only. SET LOCAL is scoped to the transaction, so it is discarded at COMMIT — this is the property that makes the design safe under pooling.
4. Postgres rewrites the query by ANDing the policy predicate into it:

SELECT ... FROM invoices
WHERE status = 'open'
  AND tenant_id = current_setting('app.tenant_id')::uuid;

5. The planner uses the composite index (tenant_id, status, created_at DESC). Note the predicate is a current_setting() call, not a literal — mark the function STABLE context by casting once, and verify with EXPLAIN that you get an index scan, not a filter after a seq scan.
6. Rows come back already filtered by the database. The ORM's own WHERE tenant_id is redundant — keep it anyway, but as defence in depth, not as the isolation mechanism.

On the schema-per-tenant design, step 2 becomes SET LOCAL search_path = tenant_4f2a, public, and the unqualified invoices resolves to a physically separate table. Nothing else changes for the application.

What Each Piece Owns

Tenant resolver owns mapping an untrusted request to exactly one tenant id, and failing closed. It does not own authorization within the tenant — that's still your app's role checks.

Transaction wrapper owns guaranteeing that no statement ever runs outside a transaction that has set tenant context. This is best enforced in one place: a single withTenant(tenantId, fn) helper, and a lint rule banning direct pool access.

async function withTenant<T>(tenantId: string, fn: (tx: Tx) => Promise<T>) {
  return pool.transaction(async (tx) => {
    // parameterised, not string-interpolated
    await tx.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
    return fn(tx);
  });
}

(set_config(..., true) is the function form of SET LOCAL and accepts bind parameters — SET LOCAL does not.)

The RLS policy owns the last line of defence. It does not own performance; it will happily produce a sequential scan if your indexes don't lead with tenant_id.

The connecting role owns whether RLS applies at all. The application role must not be the table owner and must not have BYPASSRLS, or set FORCE ROW LEVEL SECURITY. Your migration role is separate and does bypass.

The migration runner owns schema evolution. Under RLS it applies one DDL; under schema-per-tenant it owns an idempotent, resumable loop plus a template schema for new tenants — and it owns the answer to "what happens when tenant 613 of 900 fails halfway."

Where It Breaks Down

RLS, failure one: the forgotten table. ENABLE ROW LEVEL SECURITY is not inherited and not implied by creating a policy. A new table ships with no policy, no error is raised, and it's readable by every tenant. This is the single most common real-world breach in this design. Fix it structurally: a CI check over pg_class.relrowsecurity that fails the build, not a checklist.

RLS, failure two: session leakage under pooling. Use SET instead of SET LOCAL and the GUC survives COMMIT on a PgBouncer-pooled server connection. The next tenant to borrow that connection inherits it, and only if their own SET runs first do they escape. Intermittent cross-tenant reads under load are the signature.

RLS, failure three: plans and skew. Shared tables mean shared statistics. Your largest tenant (2% of rows are theirs? or 60%?) distorts the planner's row estimates for everyone. A query that index-scans for a small tenant may flip to a bitmap heap scan mid-fleet. Extended statistics on (tenant_id, status) help; sometimes partitioning by tenant hash is the real answer.

Schema-per-tenant, failure one: catalog explosion. 2,000 tenants × 40 tables × 3 indexes is 240,000 relations. pg_dump slows to hours, autovacuum's worker scheduling struggles, \dt becomes unusable, and per-backend relcache/syscache memory balloons — each connection that touches many schemas holds more catalog entries, multiplying resident memory across hundreds of backends.

Schema-per-tenant, failure two: migrations become an operation, not a deploy. A 30-second ALTER TABLE becomes a 17-hour job. Partial failure leaves your fleet in two schema versions at once, so application code must tolerate both. Teams end up building a migration orchestrator, and that orchestrator becomes the on-call burden.

Schema-per-tenant, failure three: prepared statement cache misses. Statements are planned per resolved relation, so a connection serving many tenants re-plans constantly and the client-side statement cache thrashes.

Both: onboarding latency. RLS onboarding is an INSERT into tenants. Schema onboarding is DDL — which takes locks and can't be done a thousand times a minute.

When This Is Overkill

If you have fewer than ~50 tenants, all similar in size, and one team owns every query, plain tenant_id columns with a mandatory query-builder scope is usually correct. The whole cost of RLS — policy review, role separation, SET LOCAL discipline, plan verification — buys you protection against a mistake that a single shared data-access layer plus a couple of integration tests already catches.

The signal you've outgrown it: a raw SQL query, a reporting job, or a background worker reaches the database without going through the scoped query builder. The moment isolation depends on a human remembering, move it into the database with RLS.

The signal you've outgrown RLS itself is different: a handful of tenants demand their own encryption keys, their own backup/restore cadence, or their own region. That's not a policy problem, it's a placement problem — and the answer is a separate database (or cluster) per large tenant, with RLS still handling the long tail in the shared one. Schema-per-tenant is the awkward middle: it carries most of the migration pain of separate databases with none of the blast-radius or noisy-neighbour benefits.

Key takeaway: Shared tables with RLS make isolation a policy problem (one missing policy leaks everything); schema-per-tenant makes it a migration problem (every DDL is a fan-out loop) — pick the failure you can automate away.

SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class WHERE relname = 'invoice_attachments';
-- relrowsecurity = false  <-- the leak

ALTER TABLE invoice_attachments ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoice_attachments FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoice_attachments
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

SELECT relname FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname='public' AND c.relkind='r' AND NOT c.relrowsecurity;
-- must return zero rows