Multi tenant Postgres with row level security: a working pattern

Most multi tenant SaaS leaks at the application layer, not the database. A junior engineer adds WHERE tenant_id = ? to every query and one day someone forgets. The query passes review, the test fixtures are single tenant, and a year later a customer support agent sees another customer's data. The fix is to push isolation down to the database, where forgetting is impossible.

Postgres row level security is the tool. The pattern is one SQL clause and three operational disciplines. Skip a discipline and the clause stops protecting you.

The clause

ALTER TABLE matters ENABLE ROW LEVEL SECURITY;
ALTER TABLE matters FORCE ROW LEVEL SECURITY;

CREATE POLICY matters_isolation ON matters
  USING (
    tenant_id = NULLIF(current_setting('app.current_tenant', true), '')::uuid
  )
  WITH CHECK (
    tenant_id = NULLIF(current_setting('app.current_tenant', true), '')::uuid
  );

The true flag on current_setting is the missing_ok variant: it returns NULL when the variable is unset rather than raising an error. The NULLIF(..., '') guard turns an empty string into NULL as well. Combined with the column comparison, this fails closed: an unset or empty tenant variable produces zero visible rows.

Three things to notice:

• USING filters readable rows. WITH CHECK prevents an insert or update that would create a row outside the current tenant. You need both.
• FORCE ROW LEVEL SECURITY is the one most people forget. Without it, the table owner bypasses the policy. The owner is most often the migration role, and you do not want migrations bypassing isolation by accident.
• The session variable app.current_tenant is read every time the policy evaluates. Setting it right is discipline one.

Discipline one: session variables in transactions

SET app.current_tenant = ... at the session level leaks across requests when you use a connection pool. The pool hands the same connection to the next request with the previous tenant still set. The next request reads someone else's data.

The fix is SET LOCAL inside an explicit transaction:

BEGIN;
SET LOCAL app.current_tenant = '8b1a-...';
SELECT * FROM matters WHERE id = $1;
COMMIT;

Two safety nets on top: reset the variable on checkin to the pool, and assert it is set on checkout. The reset prevents a stuck value if a transaction does not commit. The assertion catches a bug where someone forgets to SET LOCAL and the policy evaluates to NULL (which fails closed: no rows visible).

Discipline two: role separation

Maintain two database roles: a migration role and a runtime role.

• The migration role executes DDL and bypasses RLS (it is the owner). It runs only during migrations, never serves application traffic.
• The runtime role is a normal user with no BYPASSRLS attribute and is not a superuser. It serves all application queries.

If the application connects with the migration role by accident, your row level policies do nothing. Keep them separate. Audit which role each process uses.

Discipline three: performance and indexes

tenant_id should be the leading column of every composite index that supports a multi tenant query. Otherwise the planner falls back to a sequential scan, the RLS predicate evaluates row by row, and a hot path becomes O(n) over the entire table.

CREATE INDEX matters_tenant_status_idx
  ON matters (tenant_id, status, created_at DESC);

Verify the plan with EXPLAIN ANALYZE. Look for index scans, not sequential scans. Look for the tenant predicate in the index condition rather than in the filter. A multi tenant table that has been reindexed should never sequential scan in production.

Testing

Three scenarios that should appear in every multi tenant test suite:

-- 1. Cross-tenant read is denied.
SET LOCAL app.current_tenant = 'tenant-a';
SELECT count(*) FROM matters WHERE tenant_id = 'tenant-b';   -- expect 0

-- 2. WITH CHECK rejects an insecure insert.
SET LOCAL app.current_tenant = 'tenant-a';
INSERT INTO matters (id, tenant_id, title) VALUES (...,'tenant-b','x');   -- expect error

-- 3. Unset variable fails closed.
RESET app.current_tenant;
SELECT * FROM matters;   -- expect 0 rows or explicit error

Two test shapes catch the misses CI should not let through. The first is a per table primitive: write a row under tenant A, write a row under tenant B, set app.current_tenant to A, assert tenant B's row is invisible. The second is an end to end leak fuzzer: spin up the API, issue an API key per tenant, drive every authenticated route with the wrong tenant's key, and assert the response is a 4xx. The fuzzer catches the worst class of regression, which is a new route that forgot the dependency that sets the GUC. We have caught two near misses this way.

Auditability is a free win

The same RLS predicate that gates reads also gates the audit trail. If the read was denied, the audit log shows nothing. If the read was allowed, the audit log shows the same row the user saw. The compliance question collapses from "was every call site safe" to "does the policy match the log." That is much easier to answer.

When to adopt

Week one. Retrofitting RLS into a schema with cross tenant joins is much costlier than building it in. Cross tenant queries (analytics, internal admin) are the second week problem; they need a separate role with BYPASSRLS and a planned audit trail. Solve isolation first; solve the exceptions after.