Row-level security for multi-tenant Postgres

Choosing a tenancy model is the architectural decision. Enforcing it is the engineering one, and it is where most multi-tenant products quietly carry risk. A shared-schema design is entirely defensible — it is the cheapest to operate and suits most products — but only if isolation is enforced somewhere that cannot be forgotten.

The failure mode this prevents

In a shared-schema product, every query against a tenant-scoped table needs a tenant predicate. Add a reporting endpoint, a background job, an admin tool or a data export, and each one is a fresh opportunity to omit it. The omission does not throw. It returns rows — just the wrong ones.

• A new endpoint written against a repository method that was never tenant-scoped
• A background job that resolves tenant context from the wrong place, or loses it on retry
• An aggregate query written for a dashboard that sums across every tenant
• A CSV export that joins a table nobody remembered was tenant-scoped

Application-layer filtering catches most of these most of the time. Row-level security catches all of them every time, because the engine applies the predicate whether or not the query author remembered to.

The mechanics

PostgreSQL evaluates row-level security policies as an implicit predicate on every statement against a table where it is enabled. The policy reads tenant identity from a session setting, which the application sets per connection from the authenticated session — never from user input.

-- 1. Enable RLS, and force it to apply to the table owner too.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

-- 2. The policy. USING filters reads; WITH CHECK constrains writes,
--    so a tenant cannot insert a row attributed to another tenant.
CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- 3. The application role must not be able to bypass it.
--    Superusers and roles with BYPASSRLS ignore every policy.
CREATE ROLE app_user NOLOGIN NOBYPASSRLS;
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;

Note the second argument to current_setting. Passing true makes a missing setting return NULL rather than raising an error. That choice matters, and it is worth being deliberate about — see the fail-closed section below.

Setting tenant context safely

The setting has to be applied to the same physical connection that runs the query, and it has to be cleared before that connection is reused. This is where pooled connections cause real bugs.

-- Scope the setting to the transaction, not the session.
-- set_config(..., true) means "local to this transaction", so it is
-- discarded at COMMIT or ROLLBACK and cannot leak to the next borrower
-- of a pooled connection.
BEGIN;
SELECT set_config('app.tenant_id', $1, true);

-- ... application queries run here, all implicitly scoped ...

COMMIT;

Transaction pooling adds a further constraint. With PgBouncer in transaction mode, a connection is assigned per transaction rather than per session, which is compatible with transaction-local settings and incompatible with session-level ones. If you use statement-level pooling, neither works — every statement may land on a different connection.

Fail closed, not open

If app.tenant_id is never set, current_setting('app.tenant_id', true) returns NULL, the comparison evaluates to NULL, and the policy matches no rows. An unscoped query therefore returns an empty result rather than everything — which is the correct direction to fail, but it is a silent failure.

An empty result is easy to misread as "this tenant has no invoices." Make the failure loud instead: have the data-access layer refuse to run a query when no tenant context is set, and alert on it in production. The database is the backstop, not the primary signal.

The one test worth writing

Policies are configuration, and configuration drifts. The test that catches drift is not a unit test of the policy syntax — it is an integration test that attempts a cross-tenant read and asserts it fails.

1. 01Seed two tenants with distinguishable rows.
2. 02Open a transaction, set context to tenant A, and count rows. Assert only A's rows are visible.
3. 03In the same test, attempt to select a row belonging to tenant B by its primary key. Assert zero rows.
4. 04Attempt to insert a row carrying tenant B's id while scoped to A. Assert the write is rejected.
5. 05Run a query with no tenant context set. Assert zero rows, and assert the application layer raised rather than returning empty.

Run it in CI on every change. It is the only mechanism that tells you the boundary still holds after a migration adds a table, a role changes, or someone grants BYPASSRLS to debug something on a Friday.

What this still does not cover

Row-level security protects the primary database. Tenancy leaks elsewhere, and those paths need their own scoping — RLS gives no help with any of them.

• Cache keys that omit the tenant, serving one tenant's data to another
• Search indexes queried without a tenant filter applied at query time
• Object storage paths without tenant prefixes, and signed URLs scoped too broadly
• Background jobs that resolve tenant context from a stale payload
• Logs, traces and error reports that capture another tenant's payload

Used this way, row-level security converts a class of bug that depends on human vigilance into one the database refuses to allow. That is a worthwhile trade for a predicate you write once per table.