Why we chose Postgres RLS for multi-tenant isolation

The problem with application-layer tenant filtering

Every multi-tenant SaaS has the same nightmare scenario: a bug in a WHERE clause leaks one tenant's data into another. This is not theoretical — it has happened to mature companies with experienced teams.

The standard fix is code review, careful naming conventions, and hoping nobody writes SELECT * FROM conversations. We wanted something stronger.

Row-Level Security (RLS): the database as the last line of defence

Postgres RLS lets you attach a predicate to every table that the database evaluates for every query, regardless of what the application asks for. In our case, each tenant's data is tagged with a tenant_id column, and a policy says: "a row is visible only if app.tenant_id (a session variable we set per-request) matches its tenant_id."

The critical invariant: even if the application code forgets a WHERE clause, the database returns zero rows. The worst case is a 404, not a data leak.

How we set it up

Each incoming API request passes through middleware/tenant_context.go, which extracts the tenant ID from the JWT and runs:

SET LOCAL app.tenant_id = '42';

The LOCAL scope means the GUC value is automatically cleared when the transaction ends — no cleanup required, no risk of it bleeding into the next request on a pooled connection.

The RLS policy on every tenant table:

CREATE POLICY tenant_isolation ON conversations
  USING (tenant_id = current_setting('app.tenant_id', true)::bigint);

What we got wrong (and fixed)

We hit an edge case with PgBouncer in transaction-pooling mode: after a LOCAL SET expires at transaction end, the GUC reverts to an empty string ('') rather than NULL. An unguarded ::bigint cast on '' throws a 22P02 invalid-input error on the next request that gets that connection.

The fix is a NULLIF guard in the policy:

USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint)

We applied this across 469 policies in a single migration. The test: spin up a pool, issue a local-set transaction, release the connection, issue a new query — it should return zero rows, not crash.

Tradeoffs

RLS adds a small overhead to every query (roughly 1–3% in our benchmarks). For a customer-messaging workload where queries are I/O-bound rather than CPU-bound, this is negligible. The isolation guarantee is worth it.

The harder tradeoff: super-admin operations (analytics, billing reconciliation) need to bypass RLS. We use a transaction-local SET LOCAL app.bypass_rls = 'true' paired with a second policy that checks for it. This keeps the bypass explicit and auditable.

The result

Our CI gate (make ci-check-rls) enumerates all tenant tables and fails the build if any are missing an RLS policy. As of today, 469 tables are covered. Adding a new table without a policy blocks the merge.

Security through the database layer is not a silver bullet, but for multi-tenant SaaS, it is the closest thing we have found.