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

NexifyPro Engineering

Engineering team

13 min read
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.

Every consumer of tenant data and where its isolation rule lives today: by convention in the application, per tool in BI, missing in jobs and new integrations, with one database policy underneath them all

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.

ChooseWhenCost
Pool: shared tables keyed by tenantMost B2B SaaS. Tens to tens of thousands of tenants, similar shapesLowest to run; isolation must be enforced, not assumed
Bridge: pool plus a few extracted tenantsA handful of large or contractually demanding tenantsTwo code paths for routing and migration
Silo: database or cluster per tenantContractual physical isolation, data residency, per-tenant restore, divergent schemasHighest; 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:

sql
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:

python
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:

python
@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.

QueryWithout policy (BYPASSRLS)With policyOverhead
Point lookup by id64,680 tps, 0.124 ms55,593 tps, 0.144 msabout 14%
One tenant's monthly total48,530 tps, 0.165 ms42,839 tps, 0.187 msabout 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.
  • ENABLE and FORCE ROW LEVEL SECURITY on every such table; USING and WITH CHECK on 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.

NexifyPro Engineering

Written by

NexifyPro Engineering

Talk to the team

Use this article

Share

Ready to work with us?

Whether you need a dedicated team, a project partner, or strategic technology guidance, we're here to help.