Postgres Row-Level Security for multi-tenant SaaS: what nobody tells you
RLS is the right answer for multi-tenant isolation in Postgres. Here are the four production gotchas we hit.
Postgres Row-Level Security (RLS) is the gold standard for multi-tenant isolation. Done right, it gives you defense in depth: even a query missing a WHERE site_id = ? filter cannot leak data across tenants.
Done wrong, RLS can absolutely tank your performance, break your migrations, and make debugging a nightmare. Here's what we learned shipping it in production.
Gotcha 1: session GUCs vs. local GUCs
A SET app.current_site_id = ? at the session level survives across queries. A SET LOCAL app.current_site_id = ? is scoped to the current transaction. For per-request isolation, you want SET LOCAL inside a transaction, otherwise context leaks across connections.
Gotcha 2: indexes follow policies
If your RLS policy uses current_setting('app.current_site_id'), your composite indexes need (site_id, …) as the leading column. Otherwise the planner can't use them.
Gotcha 3: bypass GUC for cross-tenant ops
Some operations (admin dashboards, billing reconciliation, the signup flow itself) need to read across tenants. We added app.bypass_rls = on as a session GUC, but only granted it to specific roles via SECURITY DEFINER functions.
Gotcha 4: testing RLS is hard
Unit tests usually run as superuser, which bypasses RLS. You need a separate test profile that mimics the application role, and you need integration tests that verify cross-tenant queries return 0 rows.
RLS isn't free. But for our threat model, where leaking customer data across tenants is unacceptable, it's the only answer we'd sleep on.