Tenant isolation belongs in the database, not the dashboard
Shared-schema multi-tenancy with row-level security, the tenant carried per transaction, BI filters as defence in depth, and a test that tries to read tenant B as tenant A on every route.

NexifyPro Engineering
Engineering team

On this page
Sooner or later an enterprise prospect asks the question: how is our data isolated from your other
customers? For most multi-tenant products the honest answer is "by convention". Every query has a
WHERE tenant_id = ? clause, written by hand, and the day someone adds a report endpoint, a
scheduled export or an AI assistant and forgets the clause, one customer reads another's data.
Nothing in the test suite would notice.
We have built and operated multi-tenant analytics platforms for long enough to hold a firm view. The isolation rule belongs in the lowest shared layer, the database, applied to every transaction whether it comes from an API route, a worker, a report or a BI tool. The BI tool's own row filter is defence in depth, never the control. And the rule is only as real as the test that tries to break it. This article shows the whole shape, with a working implementation on PostgreSQL that you can run, including the leak test and the numbers for what the policy costs.
The leak path nobody drew
Draw every consumer of tenant data and write next to each one where its isolation rule lives. The picture is usually uncomfortable.
The API has the clause in every handler, until it does not. Background jobs are the usual gap: the job payload carries an invoice id but not a tenant, and the worker queries by id. The CSV export was copied from a handler eighteen months ago. The dashboards have row-level security configured in the BI tool, which is fine until a second consumer reads the same tables. The new assistant reads them directly, because that was the fastest way to ship.
The pattern is five rules in five places, each maintained by whoever touched it last. The fix is one rule in one place, underneath all of them.
Pool, bridge or silo
The AWS SaaS Lens names the three models: silo, where each tenant gets dedicated resources; pool, where tenants share them; and bridge, a mix. The choice is made on contracts and operations, not on fashion.
| Choose | When | Cost |
|---|---|---|
| Pool: shared tables keyed by tenant | Most B2B SaaS. Tens to tens of thousands of tenants, similar shapes | Lowest to run; isolation must be enforced, not assumed |
| Bridge: pool plus a few extracted tenants | A handful of large or contractually demanding tenants | Two code paths for routing and migration |
| Silo: database or cluster per tenant | Contractual physical isolation, data residency, per-tenant restore, divergent schemas | Highest; fleet operations for schema changes, backups, upgrades |
Default to pool. Extract a tenant when a contract, a regulator or its load demands it, with the runbook at the end of this article. Do not change the model for everyone because one tenant is hot. AWS's own prescriptive guidance for pooled PostgreSQL is blunt: row-level security is required to maintain tenant isolation in a pooled model.
Put the rule in the database
PostgreSQL row-level security attaches a policy to a table. Once enabled, every statement from a role that is subject to the policy is filtered by it, and inserts and updates that would create a row outside the policy are rejected. Our policy is one line:
CREATE FUNCTION current_tenant() RETURNS uuid
LANGUAGE sql STABLE PARALLEL SAFE
RETURN nullif(current_setting('app.tenant_id', true), '')::uuid;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY invoices_tenant_isolation ON invoices
USING (tenant_id = current_tenant())
WITH CHECK (tenant_id = current_tenant());
Four details in those lines carry the design.
The tenant comes from a transaction setting, not a parameter. current_setting('app.tenant_id', true)
returns NULL when nothing has been set, and tenant_id = NULL is never true. A connection with no
tenant sees no rows. That is the right default: the failure mode is an empty result, not a leak.
FORCE ROW LEVEL SECURITY matters, because owners bypass policies. The PostgreSQL manual is
explicit: superusers and roles with BYPASSRLS always bypass row security, and table owners
normally do too unless the table is forced. The application must connect as a role that owns
nothing and cannot bypass. In the example that role is app_user; migrations run as a different
role.
The policy is both USING and WITH CHECK. USING filters what a tenant can read, update or
delete. WITH CHECK stops a tenant writing a row with another tenant's id. Forget the second and
isolation is read-only.
Views need security_invoker. By default a view applies the policies of its owner. A
reporting view created by an administrator would quietly show everyone everything. Since
PostgreSQL 15, WITH (security_invoker = true) makes the view apply the caller's policies, which
is what the revenue_by_month view in the example does.
Two things the manual warns about that are easy to miss. Referential integrity checks bypass row
security, so a unique constraint on a value that is secret across tenants can leak existence: a
tenant inserting a duplicate learns that the value exists somewhere. Design unique constraints to
include tenant_id. And policy expressions run before the user's own WHERE only when the
functions involved are marked LEAKPROOF; the policy above uses only built-in operators, which
are.
Carry the tenant through the application
The policy reads a transaction-scoped setting. The application's only job is to set it, once per transaction, from the verified credential, and to make forgetting impossible.
In Python with SQLAlchemy that is a single Session event:
current_tenant: ContextVar[UUID | None] = ContextVar("current_tenant", default=None)
@event.listens_for(Session, "after_begin")
def _apply_tenant(session, transaction, connection):
tenant = current_tenant.get()
if tenant is None:
raise NoTenantContext("a database transaction was opened without a tenant in context")
connection.execute(text("SELECT set_config('app.tenant_id', :tenant, true)"), {"tenant": str(tenant)})
Middleware resolves the tenant from the token into the context variable. Every Session on every
route then applies it at the start of every transaction, and there is no tenant_id filter
anywhere in the route code. A route that opens a transaction without a tenant in context does not
run at all.
One detail cost us an hour while building the example and is worth passing on. SET LOCAL does
not accept a bound parameter, so the obvious SET LOCAL app.tenant_id = :tenant silently sets
nothing useful. set_config(name, value, true) is the parameterised form, and the third argument
makes it transaction-scoped exactly like SET LOCAL.
Why transaction-scoped and not session-scoped? Connection poolers. PgBouncer in transaction
pooling mode hands a server connection to a client only for the duration of a transaction, and its
documentation lists SET among the session features that must not be used in that mode. A
session-level tenant setting can be inherited by the next client's transaction on the same server
connection. The 2020 AWS blog post that popularised this pattern sets the tenant at connection
checkout; under a transaction pooler that is a leak waiting to happen.
The same pattern in Node, with a Prisma client extension that wraps every operation in a
transaction beginning with set_config, is in the repository. It mirrors Prisma's own
row-level-security example and carries the same two caveats: the application's own explicit
transactions need care because every operation is already one, and the database role must not be
a superuser.
Background jobs get the rule by construction: the job type requires a tenant id in its payload, and the worker sets the context before it touches a Session, exactly as a request does. A job that cannot name its tenant cannot be enqueued.
The BI tools are defence in depth
Embedded dashboards are where most teams first implemented isolation, and the tools can do it. The problem is that each does it differently, each has a case where it silently does not apply, and none of them protects the other consumers.
Power BI, in the app-owns-data embedding pattern, applies row-level security through an effective identity on the embed token: a username, a list of roles and the dataset. Dynamic roles filter on that username. Microsoft's documentation adds the caveats that matter: RLS restricts only users with Viewer permissions and does not apply to workspace Admin, Member or Contributor roles; a service principal cannot itself be placed in an RLS role; and single sign-on to the source for DirectQuery models is supported only against Azure SQL Database. Forget to pass an identity with a master user and token generation succeeds with no filter at all.
Looker uses access_filter on an Explore, bound to a user attribute, with the attribute
supplied per user, per group or in the signed embed URL. Every user who touches that Explore must
have a value, or the query errors. The access level on the attribute must be View or None, because
an attribute the user can edit is a tenant selector.
Tableau offers user filters on USERNAME() and ISMEMBEROF(), entitlement tables, and
USERATTRIBUTE() fed from a connected-app token. Tableau's own best-practice page states the
limit plainly: if the data source is embedded and a user can web-edit or download the workbook,
the row-level security is nonexistent, because the filters enforcing it can be removed.
All three share the service-account problem. The BI server connects to the data as one principal. Unless the end user's identity is propagated and the warehouse enforces on it, the tool's filter is the only thing between tenants, and it covers only that tool.
The warehouse copy
The same principle applies when the analytics tables live in a warehouse. Snowflake row access
policies attach a boolean expression to a table, typically joined to a mapping table on the
session's role or user; the feature requires Enterprise Edition and the policy is evaluated with
the policy owner's privileges, which is what lets it read the mapping table. BigQuery row access
policies are created with CREATE ROW ACCESS POLICY ... GRANT TO (...) FILTER USING (...), usually
on SESSION_USER(), and come with documented limits worth knowing before you depend on them: no
wildcard tables, no participation in query pruning, and no acceleration from BI Engine or
materialised views on filtered tables.
Either way, the identity the warehouse sees must be the end user's or a per-tenant service account, not one shared BI principal. If it is the shared principal, you are back to the dashboard being the control.
Prove it with a test
A rule nobody tests is a rule that is true today. The test in the repository does not trust the routes. It introspects the application's route table, collects every id that belongs to tenant B from the database, and calls every GET route as tenant A with those ids:
@pytest.mark.parametrize("path", sorted(get_routes()))
def test_no_route_returns_another_tenants_rows(path):
foreign = {"project_id": ids_owned_by(GLOBEX_ID, "projects"), "invoice_id": ids_owned_by(GLOBEX_ID, "invoices")}
params = re.findall(r"{(\w+)}", path)
if params:
for foreign_id in foreign[params[0]]:
response = client.get(path.replace("{" + params[0] + "}", foreign_id), headers=ACME)
assert response.status_code == 404 # never 403, which confirms the row exists
else:
body = client.get(path, headers=ACME).json()
assert [row for row in body if row_belongs_to_globex(row)] == []
Add a route and it is covered without anyone remembering to write a test. Around it sit the cases
that catch the other ways isolation fails: a write against another tenant's row returns 404 and
changes nothing; a request with no credential never reaches the database; a job is scoped by its
payload; a Session opened without a tenant in context refuses to run; and a raw query as the
application role sees three rows for one tenant, two for the other and zero with no tenant set,
while the reporting role with BYPASSRLS sees all five. That last test is there to make a point:
the role that can see everything exists for the warehouse export, and it must never be handed to
anything that serves one tenant.
Note the 404. A 403 on another tenant's id tells an attacker the id is valid. From the outside, a row you may not see should be indistinguishable from a row that does not exist.
What it costs
The question every engineer asks next is what the policy costs on the hot path. We measured it with pgbench on PostgreSQL 17.11, 100 tenants with 1,000 invoices each, eight clients, fifteen seconds per run, on a developer laptop. Both roles run the same query with an explicit tenant filter; for the application role the policy is evaluated on top, for the bypass role it is not.
| Query | Without policy (BYPASSRLS) | With policy | Overhead |
|---|---|---|---|
| Point lookup by id | 64,680 tps, 0.124 ms | 55,593 tps, 0.144 ms | about 14% |
| One tenant's monthly total | 48,530 tps, 0.165 ms | 42,839 tps, 0.187 ms | about 12% |
A laptop number is a laptop number, but the shape is what matters: a flat, modest overhead per
statement, not a cliff. The cliffs come from three mistakes that are documented across the
community: no index on the tenant column, a policy that calls a user-defined function per row
instead of comparing against a stable value, and a security definer lookup inside the policy.
Index tenant_id on every policy-protected table, keep the policy expression to a comparison with
a stable function, and the overhead stays in the range above.
One more thing to know rather than to fix: a 2023 SIGMOD paper demonstrated that an attacker with query access can infer whether a protected value exists by crafting index-using queries and timing them, in both PostgreSQL and SQL Server. Row-level security is isolation for application consumers, not a defence against an adversary with direct SQL access to the database.
When this is wrong
Silo is right when a contract requires physical isolation or in-country residency, when one tenant needs its own point-in-time restore, or when tenants genuinely need different schemas. Any of those is a reason to extract that tenant, not to abandon the pool.
The pattern is also wrong, specifically, if your pooler runs in transaction mode and your
application sets the tenant per session. Use set_config(..., true) inside the transaction, or
switch the pooler to session mode and pay for the connections.
And it is overkill for an internal tool with one BI consumer and no API; there, the tool's filter is the layer, and adding a database policy is ceremony.
When a tenant does outgrow the pool, the repository includes a runbook for moving it to its own database with PostgreSQL logical replication and a row-filtered publication, cutting over with a short read-only window and no application change: the tenant's routing entry points at a new connection string, and the same leak test runs against the new database before traffic moves.
The checklist
- Every tenant-owned table has
tenant_id, an index on it, and unique constraints that include it. ENABLEandFORCE ROW LEVEL SECURITYon every such table;USINGandWITH CHECKon every policy.- The application role owns nothing and has no
BYPASSRLS; migrations run as a separate role. - The tenant is set per transaction with
set_config(..., true)from the verified credential, by a hook no route can skip. - Jobs carry the tenant in their payload and set it before any query.
- Reporting views use
security_invoker; the role that can see everything is inventoried and never serves a tenant. - BI tools propagate the end user's identity and keep their own filters as a second layer.
- A test walks the route table as tenant A with tenant B's ids and expects 404s and empty lists.
- The policy's cost is measured on your hardware before anyone argues about it.
The schema, the application, the Prisma extension, the test and the benchmark are in the engineering notes repository.

Written by
NexifyPro Engineering
Use this article
