For most B2B SaaS apps, a tenant_id column plus row-level security (RLS) on every table is the right default: you keep one schema and one migration path, and the database enforces isolation even when someone forgets a WHERE clause. Schema-per-tenant only pays off when you have a small number of large tenants with genuinely different needs (per-tenant restores, per-tenant data residency). Database-per-tenant is an operations decision, not a data-modeling one — pick it when tenants must be backed up, moved, or deleted independently.
The part nobody warns you about: RLS fails silently in both directions. Misconfigured one way, it returns zero rows and your app looks broken. Misconfigured the other way, it returns everyone's rows and nothing looks wrong at all.
What does "multi-tenancy" actually mean at the storage layer?
Three models, in increasing order of isolation and operational cost:
| Model | Isolation enforced by | Migration cost | Per-tenant restore | Realistic tenant count |
|---|---|---|---|---|
tenant_id column, app filters |
Your application code | One migration | Hard (row-level surgery) | Unlimited |
tenant_id column + RLS |
Postgres, per query | One migration | Hard | Unlimited |
| Schema per tenant | Postgres, per search_path
|
One migration × N schemas | Easy (pg_dump -n) |
Tens to low hundreds |
| Database per tenant | Postgres, per connection | One migration × N databases | Easy (pg_dump) |
Tens |
The first row is where most teams start and where most tenant-data leaks come from. A single endpoint that builds a query without the tenant predicate — a new report, an admin tool, a background job reusing a helper — is enough. Code review catches this most of the time, which is exactly the problem: "most of the time" is not an isolation guarantee.
Takeaway: if isolation depends on every future query being written correctly, you do not have isolation, you have a convention.
How do I set up RLS so it actually enforces anything?
Two statements per table, plus a policy:
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.current_tenant', true)::uuid)
WITH CHECK (tenant_id = current_setting('app.current_tenant', true)::uuid);
ENABLE turns policies on. FORCE is the one people skip, and it matters: without it, the table's owner bypasses every policy. If your application connects with the same role that created the tables — the default in a lot of small setups — then ENABLE ROW LEVEL SECURITY alone does nothing for your app's queries. It looks configured. It enforces nothing.
USING filters reads (and which rows UPDATE/DELETE can see). WITH CHECK validates rows being written. Omit WITH CHECK and a tenant can insert rows labeled with someone else's tenant_id.
On the application side, set the tenant inside the transaction, never as a session-level SET:
// pg (node-postgres). set_config(..., true) == SET LOCAL, scoped to this transaction.
async function withTenant(pool, tenantId, fn) {
const client = await pool.connect();
try {
await client.query('BEGIN');
await client.query('SELECT set_config($1, $2, true)', ['app.current_tenant', tenantId]);
return await fn(client);
} finally {
await client.query('COMMIT').catch(() => client.query('ROLLBACK'));
client.release();
}
}
SET LOCAL cannot take a bind parameter, which is why set_config($1, $2, true) is the version you want — string-concatenating a tenant id into a SET statement is an injection point in the one place you least want one.
Takeaway: ENABLE without FORCE, or USING without WITH CHECK, is a policy that reads as secure in a migration diff and is not.
Why does RLS return zero rows (or everyone's rows) in production?
These are the failure modes I keep running into, in the order they bite.
Zero rows, no error. current_setting('app.current_tenant', true) with the second argument true returns NULL when the setting is missing instead of raising unrecognized configuration parameter. tenant_id = NULL is never true, so every query returns an empty set. The symptom is a page that renders with no data and no stack trace, usually in a code path that got a connection outside your withTenant wrapper — a health check, a migration script, a queue worker. Drop the true during development so you get a loud error instead of an empty list.
Everyone's rows, no error. Three causes: the app role owns the tables and you did not FORCE; the app role has BYPASSRLS (superuser always does — this is why your app should never connect as postgres); or the table is new and nobody enabled RLS on it. The third is the common one. Put it in CI:
-- Fails the build if any table in the schema is missing RLS.
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 (NOT c.relrowsecurity OR NOT c.relforcerowsecurity);
Assert that query returns no rows, with an explicit allowlist for genuinely shared tables (plans, feature flags, country codes).
Cross-tenant bleed under a connection pooler. In transaction pooling mode, PgBouncer or Supavisor hand your next transaction whatever server connection is free. A session-level SET app.current_tenant survives on that server connection and is then inherited by a different tenant's transaction. SET LOCAL / set_config(..., true) resets at commit, which is what makes RLS and transaction pooling compatible at all.
Takeaway: test isolation with a real query as a real tenant role in CI — a passing migration proves the policy exists, not that it applies.
When is schema-per-tenant worth the operational cost?
When a tenant is a unit of operations, not just a unit of data. Concretely: you need pg_dump -n tenant_42 to restore one customer without touching the others, you have contractual data-residency requirements, or a few large tenants need different indexes or retention.
The cost is real. Every migration runs N times, so a schema change is now a job with partial-failure semantics rather than a single statement. Thousands of schemas bloat the system catalogs and make pg_dump, autovacuum scheduling, and query planning noticeably slower. Under a pooler, search_path has the same leak problem as session-level SET.
If you want per-tenant isolation without hand-rolling the migration fan-out, Neon's database branching gives you cheap copy-on-write databases you can create and drop per tenant or per environment, at the cost of tying that piece of your infrastructure to one vendor's control plane. If you are already on Postgres-as-your-backend and want RLS wired to your auth layer, Supabase enforces policies against JWT claims by default, which is genuinely good hygiene but means an incorrect policy is directly internet-facing rather than behind your API. For tenants that outgrow one machine, Citus distributes tables by tenant_id so a tenant's rows stay colocated on one node, with the tradeoff that cross-tenant analytical queries and some schema changes get more expensive.
Takeaway: choose schema- or database-per-tenant when you need per-tenant restore and delete, not because it feels more secure.
Does RLS cost performance?
It adds a predicate to every query on the table, so the honest answer is: only if your indexes ignore the tenant. Make tenant_id the leading column of the indexes that back your hot queries — (tenant_id, created_at DESC) rather than (created_at DESC) — and the RLS predicate rides along for free.
The subtler cost: Postgres evaluates RLS quals before user-supplied quals unless the operators involved are marked LEAKPROOF, since a non-leakproof function could otherwise expose values from rows you should never see. That restriction occasionally blocks a plan the planner would have chosen otherwise. Check EXPLAIN on your slowest tenant-scoped query with RLS on and off before assuming it is free.
Takeaway: RLS is cheap when tenant_id leads your indexes and worth measuring when it doesn't.
FAQ
Can I use row-level security with PgBouncer in transaction mode?
Yes, as long as you set the tenant with SET LOCAL or set_config('app.current_tenant', $1, true) inside the transaction. A plain session-level SET persists on the pooled server connection and will be inherited by another tenant's transaction.
Why does my RLS policy return no rows even though the data exists?
The configuration parameter the policy reads is unset on that connection, so current_setting('app.current_tenant', true) returns NULL and the comparison is never true. It almost always means a code path acquired a connection without going through your per-request transaction wrapper.
Do I need one database per tenant for compliance?
Usually no — RLS satisfies most logical-isolation requirements. Separate databases matter when you need per-tenant backup, restore, deletion, or data residency as an operational guarantee rather than a query-level one.
Bottom line
Start with a tenant_id column and RLS with both ENABLE and FORCE, an app role that is not the table owner and has no BYPASSRLS, and SET LOCAL inside every request transaction. Add a CI check that fails when a new table ships without a policy, because that is how the leak actually happens. Move to schema- or database-per-tenant only when per-tenant restore, deletion, or residency becomes a requirement — and accept that your migration pipeline becomes a fan-out job that day. Whatever you pick, write one test that logs in as tenant A and asserts it cannot read tenant B's row; it is the only evidence that any of this works.
Top comments (0)