nestonexStart a project How do you stop one tenant's query returning another tenant's rows?
Enforce the filter in the database rather than in every query. Row-level security attaches a policy to the table, tied to the current user or tenant, so a query missing its WHERE clause returns nothing instead of returning somebody else's data. The failure mode changes from a leak to an empty result.
In a shared-database multi-tenant system, every query needs a tenant filter. Hundreds of queries, written over years by people who joined at different times, each needing the same clause.
It works until one does not have it. A new endpoint, a reporting query, a migration script, a join that reaches a table through a path nobody checked. The bug is invisible in testing, because in a test database there is usually only one tenant's data to leak.
And the consequence is not a stack trace. It is a customer seeing another customer's records, which is a disclosure incident rather than a defect, with the reporting obligations that come with one.
The asymmetry is what makes this worth solving structurally: hundreds of chances to get it right, one chance to get it wrong, and the wrong outcome is categorically worse than the right one is good.
You attach a policy to the table describing which rows the current session may see, expressed against a session variable your application sets on connection — the tenant id, the user id, the role.
Then a query that forgets its filter returns zero rows rather than everything. The bug still exists and still needs fixing, but it now presents as an empty page, which is noticed immediately and leaks nothing.
That is the entire value proposition and it is a large one. You are not preventing mistakes; you are changing what a mistake costs, from a disclosure to a defect.
It composes with application filtering rather than replacing it. Keep the WHERE clauses — they express intent and help the planner — and let the policy be the thing that holds when one is missing.
The policy is evaluated per row, so it appears in every query plan. For a simple equality against an indexed tenant column that is cheap; for a policy involving a subquery or a function call it is not, and it will surprise you on a large table. Keep policies simple and indexable.
Connection handling becomes load-bearing. The session variable must be set for every connection before any query runs, which needs care with a connection pool where connections are reused across requests. A pooled connection carrying the previous request's tenant is the exact failure you were preventing.
Migrations and admin tooling need a deliberate path. Something has to be able to see everything, and that something needs to be a specific, audited role rather than a convenience that spreads.
And testing needs at least two tenants in the fixtures. A test suite with one tenant's data cannot detect a policy that does nothing.
When isolation is a contractual requirement rather than an engineering preference, and a policy in a shared table will not satisfy the auditor.
When tenants need genuinely different schemas, or need to be restored independently — a single tenant's point-in-time recovery from a shared table is a painful operation you do not want to discover under pressure.
When one tenant's volume would degrade everyone else's queries. Noisy-neighbour effects are real and shared indexes are where they show up.
Against that, a database per tenant multiplies migration surface, connection overhead and operational work by the number of customers. Shared with RLS is the right default; separate is what you move the largest few to when there is a reason.
AgencyOS runs operator, admin and client portals off one API across 78 data models, with each operator's marketing site provisioned from an embedded generator. Tenant isolation at that shape is not something you can leave to every query author remembering.
ProTrader is multi-tenant with white-label provisioning, where each community's members and computed broker metrics must never cross. The same reasoning applies: the data is per-tenant, the code paths are shared, and the enforcement belongs where it cannot be forgotten.
No. RLS decides which rows a session may see; your application still decides what a user is allowed to do with them. Role-based access control and row-level security answer different questions and both are needed.
It depends entirely on the policy. A simple equality against an indexed column adds very little. A policy calling a function or running a subquery per row is expensive and will show up on large tables. Read the query plans with policies enabled rather than assuming.
The session variable has to be set at the start of every request on whichever pooled connection it gets, and cleared or overwritten reliably afterwards. A connection reused with a previous tenant's context is precisely the leak RLS was meant to prevent, so this is worth testing explicitly.
Yes, and incrementally. Enable it table by table, starting with the ones holding tenant-scoped data, and run both application filtering and policies together. The tables that return zero rows in testing are the ones telling you which queries were relying on luck.