Most multi-tenant SaaS apps isolate customers with one rule: every query must include WHERE tenant_id = .... That works until someone forgets it once, in a report endpoint or an admin script, and one
Markdown supported · ⌘↵ to post
Strong post. RLS is the right answer for exactly the reason you give. A few production gotchas from my side. RLS doesn't apply to table owners or superusers, so the app role must not own the tables, and watch out for BYPASSRLS. The tenant context (e.g. SET app.current_tenant) has to be reset per checkout when you're behind PgBouncer. A leaked tenant setting is a data leak. And policy expressions run on every row, so keep them sargable and index-friendly on large tables or RLS becomes a performance tax. Miss any of those and the enforcement story quietly breaks.
Good to see a write up that covers the parts that usually bite later, such as connection pooling and setting the tenant context per transaction rather than per session.
One practice I would add: make cross tenant access tests part of the definition of done for every feature that touches tenant data. A small suite that logs in as tenant A and tries to read, update and export tenant B's records catches the cases where a new view or a background job quietly bypasses the policy. Those tests are cheap to write and they turn row level security from a configuration you trust into a property you verify on each deploy.
It also makes security reviews with customers much easier, because you can show the tests rather than describe the intent. Do you run background jobs under a separate role with its own policies, or do they set the tenant context the same way requests do?