Tenant isolation in the application layer has one fatal weakness: it depends on every developer remembering to add a filter to every query, forever. Row-Level Security moves that rule into the database, so even if the app forgets, PostgreSQL refuses. It is a safety net, not a replacement for application discipline.
How RLS works, briefly
Every tenant table gets a policy: a row is visible only if its tenant column matches the current session's tenant. The session tenant is set per transaction via a parameter (e.g. SET LOCAL app.current_tenant_id), and the policy reads it.
Because SET LOCAL is bound to the transaction, it automatically clears when the transaction ends — no state leaks between requests in a connection pool. That small detail, done wrong, becomes a new source of leaks.
RLS complements, not replaces, the application filter
The application still carries tenant context and filters — that is the primary path and the most informative one for the query planner. RLS is the second layer: if a query forgets the filter, the database refuses instead of leaking.
Two layers that cover each other are far stronger than one perfect layer. And 'perfect forever' is not a safe assumption for code touched by many people over many years.
Leak tests are part of the feature
- For every tenant table, write an integration test that sets tenant A, then asserts tenant B's data is never visible through any path.
- Test the paths that commonly slip through too: aggregations, cross-table joins, and reports.
- Ensure the application role does not have BYPASSRLS; superusers and table owners can silently bypass the policy.
- Treat adding a new tenant table without a policy as a build failure, not just a note.
Summary
RLS turns tenant isolation from 'hope everyone remembers' into 'the database enforces it'. Use it as a second layer above the application filter, set the tenant per transaction, and make leak tests a mandatory requirement for every new table.