Row-level security for multi-tenancy: one shared schema, no cross-tenant reads Advanced

The problem

A B2B invoicing SaaS keeps 200 companies in one PostgreSQL 17 schema. Every table has a tenant_id column: Acme is tenant 1, Globex is tenant 2. The API filters with WHERE tenant_id = $1.

A new CSV export forgets that predicate. The same role that returns Acme’s 1,000 invoices from the list page returns 200,000 rows from 200 tenants from the export. This is BOLA at tenant scale.

Can the database refuse to return another tenant’s rows even when the code forgets, and what still breaks it: the role the app connects as, and a connection pool that keeps state between requests?

What to choose (summary first)

Situation	Choose	Condition
Few large tenants, contracts demand physical separation or per-tenant restore	Database per tenant	Automation for migrations and connections × N
Tens to hundreds of tenants, per-tenant extensions or schema drift	Schema per tenant	Catalog size and migration time grow with N
Many small tenants, one product schema	Shared schema + tenant_id + RLS	App role is not owner, has no BYPASSRLS; tenant set with SET LOCAL

Core concepts

Three tenancy models

	Database per tenant	Schema per tenant	Shared schema
Isolation boundary	Database, can be a separate server	Schema + search_path	A predicate on every row
Cost of a forgotten filter	None; the risk moves to routing the wrong connection	Wrong search_path = wrong tenant	Cross-tenant read, unless RLS
Migrations	N runs	N runs in one database	One run
Cross-tenant reports	Hard	Possible (UNION)	Simple
Noisy neighbour	Isolated if on separate servers	Shared	Shared; needs per-tenant limits

The shared schema is cheapest to run, so isolation must not depend on discipline alone. That is the job of row-level security (RLS).

RLS vocabulary

Term	Meaning
ENABLE ROW LEVEL SECURITY	Policies apply to normal roles. No policy at all = default deny
Policy USING	Which existing rows are visible to SELECT, UPDATE, DELETE
Policy WITH CHECK	Which new rows INSERT/UPDATE may write. Omitted on ALL = reuse USING
FORCE ROW LEVEL SECURITY	Also applies policies to the table owner
BYPASSRLS, superuser	Always skip policies, FORCE or not
app.tenant_id	A custom setting the policy reads via current_setting()

Loading diagram…

How it works

The policy reads the tenant from a per-transaction setting. NULLIF(..., '') matters: after a SET LOCAL expires the setting is '', not NULL, and ''::bigint raises an error.

ALTER TABLE demo_rls.invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE demo_rls.invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON demo_rls.invoices
  USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint);

Export for Acme without WHERE tenant_id: the policy filters it and the index still serves it

Enlarge diagram

API → PostgreSQLtenant = 1

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

BEGIN, then set_config(app.tenant_id, 1, true): the value lives only until this transaction ends.

1. set_config('app.tenant_id', '1', true): the third argument true means local to the transaction, the same as SET LOCAL.
2. The query forgets the predicate; that is the case RLS is for.
3. The role is checked: rls_app owns nothing and lacks BYPASSRLS, so the policy is added.
4. The policy predicate is a normal condition; with an index leading on tenant_id it becomes an Index Cond.
5. COMMIT clears the setting. The connection carries no tenant into the next request.

Why a plain SET leaks behind a pool

SET without LOCAL lasts until the session ends once its transaction commits. A pooled connection, in pg.Pool or PgBouncer transaction pooling, outlives the request. PgBouncer lists SET/RESET as “Never” supported in transaction mode.

Session SET: Globex's request inherits Acme's tenant from a reused connection

◀ Prev▶ PlayNext ▶↺ RestartStep 1/6

Request A runs SET app.tenant_id = 1 on C1. SET is session-level: it outlives the transaction.

The leak needs a code path that skips setting the tenant and a connection last used by another tenant, so it is intermittent. See PgBouncer transaction mode for the same effect with other settings.

Operational details

• Unset tenant = 0 rows, thanks to current_setting(..., true) returning NULL and NULLIF. Without missing_ok, a fresh session errors with unrecognized configuration parameter "app.tenant_id".
• Foreign keys and unique constraints bypass RLS. A UNIQUE (number) across tenants reveals that another tenant used a number. Make uniques per tenant: (tenant_id, number).
• Views run as the view owner unless created with security_invoker (PostgreSQL 15+). A view owned by the table owner skips the policy.
• TRUNCATE and REFERENCES are not subject to RLS.
• For backups and admin jobs, SET row_security = off makes a filtered query error instead of silently returning fewer rows.
• Indexes lead with tenant_id: (tenant_id, created_at DESC), (tenant_id, number). The policy predicate then becomes the first index column.
• Leakproof: user conditions run after the policy unless their function is LEAKPROOF. int8eq and texteq are; jsonb @> and LIKE are not, so under RLS they cannot become index conditions.
• A function called by the policy must be declared STABLE, not the VOLATILE default, or it is evaluated per row and the index is skipped.

Real-world example

Invoices leak during an export, then again intermittently after RLS

The export bug is closed by RLS. Weeks later a Globex user sees Acme invoices once, and nobody can reproduce it. The cause: a webhook handler that never sets the tenant, plus a middleware that used session SET on a pool of 20 connections.

A second near miss in review: migrations and the API shared the owner role, so without FORCE the policy never applied in production.

Scripts in examples/src/row-level-security/ (200 tenants × 1,000 invoices):

docker compose -f examples/docker-compose.yml up -d postgres
for f in 01-setup 02-without-rls 03-policies 04-bypass 05-performance 06-isolation-test; do
  docker compose -f examples/docker-compose.yml exec -T postgres \
    psql -U hub -d hub < examples/src/row-level-security/$f.sql
done
npx --prefix examples ts-node examples/src/row-level-security/pool-leak.ts

Outputs from the run, trimmed (PostgreSQL 17):

-- 02: rls_app, export without WHERE, before RLS
  rows  | tenants
 200000 |     200
-- 03: rls_app, tenant 1, same query after the policy
 rows | tenants
 1000 |       1
INSERT ... tenant_id = 2  ->  ERROR: new row violates row-level security policy for table "invoices"
no tenant set             ->  count 0
-- 04: who bypasses
superuser hub: 200000 | owner, no FORCE: 200000 | rls_admin (BYPASSRLS): 200000
owner after FORCE: 0, with tenant 2 set: 1000
rls_app with row_security = off -> ERROR: query would be affected by row-level security policy

The pool demo runs 400 concurrent requests (pool max: 5), alternating tenants 1 and 2. Leak counts vary per run (173–196 in three runs):

session SET on pool: 400 requests, 187 saw another tenant, 0 saw nothing
forgot to set, same pool: saw tenant_id 2
SET LOCAL per transaction: 400 requests, 0 saw another tenant, 0 saw nothing
forgot to set, transaction-local pool: saw 0 tenants

Performance under the policy, tenant 1 set:

-- list page: policy predicate as Index Cond
Index Scan using invoices_tenant_created_idx  Index Cond: (tenant_id = (NULLIF(current_setting(...))::bigint)
-- meta @> '{"status":"disputed"}': GIN unused under RLS (BitmapAnd with GIN as superuser)
Index Scan using invoices_tenant_created_idx  Filter: (meta @> ...)  Rows Removed by Filter: 999
-- policy via a VOLATILE plpgsql function vs STABLE
Seq Scan on invoices  Rows Removed by Filter: 199000   Execution Time: 40.406 ms
Index Scan using invoices_tenant_created_idx           Execution Time: 0.018 ms

Trade-offs

Shared schema + RLS

• A forgotten WHERE tenant_id returns 0 foreign rows instead of a leak.
• WITH CHECK blocks writes into another tenant, including moving a row.
• One schema, one migration, simple cross-tenant reporting through a separate BYPASSRLS role.

Costs to manage

• Correctness depends on the role and on app.tenant_id being set per transaction.
• Non-leakproof operators lose index use; volatile policy functions cause full scans.
• Failures are silent: a wrong setting returns plausible data, not an error.
• Noisy neighbours and per-tenant restore remain unsolved.

🎮 Try it

• Tenant data leak: a customer sees another company’s invoices; find the missing filter, the owner role and the pooled SET.

Production checklist

1. Separate roles: migrations own tables; the API logs in as a non-owner role without BYPASSRLS.
2. ENABLE and FORCE ROW LEVEL SECURITY on every table with tenant_id; a CI query fails when one is missing.
3. Set the tenant only with set_config('app.tenant_id', $1, true) inside BEGIN; never session SET.
4. Policy uses current_setting(..., true) + NULLIF, so an unset tenant yields 0 rows.
5. Every index and unique constraint leads with tenant_id.
6. Run an isolation test per tenant as the app role (06-isolation-test.sql): foreign rows = 0, cross-tenant INSERT rejected.
7. Inventory roles with rolsuper or rolbypassrls that can log in; only admin tooling may use them.
8. Check EXPLAIN of hot queries as the app role, not as a superuser.

Common mistakes

Setting the tenant: session SET vs transaction-local

✕ Common

// session-level, and may land on a different pooled connection than the query
await pool.query("SELECT set_config('app.tenant_id', $1, false)", [tenantId]);
await pool.query('SELECT * FROM demo_rls.invoices');

✓ Prefer

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
  await client.query('SELECT * FROM demo_rls.invoices');
  await client.query('COMMIT');
} catch (e) { await client.query('ROLLBACK'); throw e; }
finally { client.release(); }

Why

The local value is cleared at COMMIT or ROLLBACK, before the connection returns to the pool.

The second trap:

Testing RLS as a superuser

Local tools often connect as the postgres superuser, which bypasses every policy. Tests pass by returning everything. Run isolation tests and EXPLAIN with SET ROLE to the app role.

Misconception	Consequence	Do instead
“RLS is on, so the owner is covered”	Owner bypasses without FORCE	FORCE + non-owner app role
“SET is fine, we set it every request”	One code path forgets; stale tenant from the pool	SET LOCAL / set_config(..., true)
“RLS replaces WHERE tenant_id”	A wrong app.tenant_id or a BYPASSRLS path has no second check	Keep explicit filters; RLS is the backstop

Self-check

1. Why does the owner see 200,000 rows after ENABLE but 0 after FORCE with no tenant set?
2. With SET LOCAL, a code path forgets to set the tenant. What does it return, and why is that safer than with SET?
3. Why does meta @> '{...}' use the GIN index as superuser but not as rls_app?

Summary

Key takeaways

• Shared schema + tenant_id is the cheapest model; RLS turns a forgotten filter into 0 rows.
• Superusers and BYPASSRLS always bypass; the owner bypasses unless FORCE. The app role must be neither.
• Set the tenant per transaction with set_config(..., true); plain SET survives on pooled connections.
• Lead indexes with tenant_id, keep policy functions STABLE, and expect non-leakproof operators to filter, not index.
• Prove isolation with per-tenant tests run as the app role.

References

• Row Security Policies — PostgreSQL 17: default deny, who bypasses, FORCE, row_security, constraint side channels.
• CREATE POLICY — PostgreSQL 17: USING vs WITH CHECK, permissive vs restrictive, leakproof ordering.
• SET — PostgreSQL 17: lifetime of SET and SET LOCAL across commit and rollback.
• System Administration Functions — PostgreSQL 17: set_config is_local, current_setting missing_ok.
• PgBouncer — Features: which SQL features each pooling mode supports.