17 September 2026 · 5 min read

The test that reads another tenant's rows

Tenant isolation in a shared Postgres schema is only as trustworthy as the test that tries to break it. A generated probe turns a convention into a property CI enforces.

Every multi-tenant application has a sentence in its design document that says every query is scoped to the tenant. The sentence is true on the day it is written. It stops being true the first time someone writes a raw query for a report, or adds a table without the scoping column, or runs a session-level setting through a connection pool that reuses sessions. None of those mistakes fails a test, because there is no test. The CRM I built at CustomGlide serves companies that must never see each other's pipelines, and the thing that let me sleep was not the WHERE clause. It was a test that logs in as one tenant and tries, table by table, to read another.

Every implementation has a way to stop applying

There are three common ways to scope a shared-table schema, and each has a failure mode that is silent.

The WHERE clause: every query includes tenant_id = $1. It stops applying whenever someone writes a query that does not include it, which in a codebase of any age is a matter of when. A Prisma client extension or a query middleware that appends the clause automatically is better, and it stops applying for raw queries, for queries through a second client, and for the one model that was added after the extension's list was written.

Row-level security: Postgres policies on each table that compare a row's tenant column with a setting on the connection, set with SET LOCAL inside the transaction. The Postgres documentation is clear that policies are bypassed by superusers and by roles with BYPASSRLS, and that a table's owner is exempt unless the table is set to force it. So RLS stops applying whenever the application connects as the wrong role, and it stops applying in a different way when the setting is made at session level instead of transaction level and the connection goes back to a pool.

That last one is the subtle one. A transaction-mode pooler such as PgBouncer hands each transaction to whichever server connection is free; its documentation lists session-level features that do not work in that mode, and SET outside a transaction is one of them. A SET app.tenant = 'A' that is not LOCAL survives the transaction, follows the server connection back to the pool, and is inherited by the next client, who may be tenant B. Nothing errors. B's first query runs as A.

Where a session-level setting leaks through a transaction pooler Three lanes: the application, the pooler, and one Postgres server connection. Request one begins a transaction, sets the tenant, queries and commits; the connection returns to the pool. Request two from a different tenant is given the same server connection. With SET LOCAL the setting was cleared at commit. With a plain SET it is still A, and B's query reads A's rows. Application Transaction pooler Server connection tenant A: BEGIN; SET tenant = 'A'; query; COMMIT connection returned to the pool tenant B: BEGIN; query; COMMIT SET LOCAL: setting cleared at COMMIT B's query sees no tenant, or its own SET: still 'A' on the reused connection B reads A's rows, no error anywhere
Illustrative: the hangover case that a transaction-mode pooler produces when a setting is not transaction-scoped; see the PgBouncer feature notes.

The cross-tenant probe

So the test is generated, not written, because a hand-written test covers the tables someone remembered. The probe reads the schema, finds every table that carries a tenant column, and produces one case per table. Each case seeds a row for tenant B, authenticates as tenant A through the application's normal path, and asserts that a query for that table returns zero rows belonging to B. It does this through every read path the application has: the ORM, the raw query helper, the reporting endpoint, and anything else that talks to the database.

Two details of the seeding decide whether the probe means anything. The rows for tenant B must be identical to tenant A's in every column except the tenant column, so that a leak cannot hide behind a filter that happens to exclude B's data for some other reason, a status, a date range, a soft-delete flag. And the probe must run through the same connection topology as production, pooler included, because the failure modes worth finding live in the pooler and a probe that connects directly to Postgres will never see them.

Then one more case per table, the pool-hangover case. Run a request as tenant A that sets the tenant, commit, return the connection, then run a request as tenant B on the same pool and assert that B's first query does not see A's rows. On a transaction pooler this case fails within seconds if the setting is session-level. On a direct connection it passes, which is exactly why the bug survives in development and appears in production.

The probe reports two numbers: tables covered and tables leaking. The first number is how much of the schema the probe understands. The second must be zero, and a leaking count above zero fails the build. That is the entire contract. The isolation guarantee is no longer a sentence in a design document; it is a CI job with a number in it.

What the schema tells you before the probe runs

Generating the probe from the schema has a side effect: it makes you look at which tables carry a tenant column and which do not. I ran that count against a large public schema, the Prisma schema of cal.com, on the main branch on 17 September 2026. Of 100 models, 61 carry a team, user or organisation column directly, and 39 do not.

Models with and without a direct scoping column in cal.com's schema Two horizontal bars: 61 of 100 models carry a teamId, userId, organizationId or orgId column directly; 39 do not. The unscoped bar is highlighted because it is the set the probe has to classify. One public schema, counted cal.com, packages/prisma/schema.prisma, main branch, 100 models Direct tenant column 61 No direct tenant column 39 Among the 39: Attendee, Payment, BookingReference and BookingSeat reach a tenant through a booking; App, Feature, Deployment and RateLimit are global by design. counted with a script over the schema file; scoping columns matched: teamId, userId, organizationId, orgId
Source: computed from the cal.com Prisma schema on 17 September 2026; the classification of the 39 is the author's reading of the model names, not a statement about cal.com's isolation.

That second group is not a list of bugs. It is a list of decisions. Some of those models are global by design: feature flags, app metadata, deployment settings. Some reach a tenant through a parent: an attendee belongs to a booking, and a booking belongs to a user or a team, so the scoping is one join away. The probe has to know which is which, and the honest way to give it that knowledge is a small annotation per table: global, direct, or through-parent-X. A table with no annotation fails the build too, because an unclassified table is the one that will be added next month with the scoping forgotten.

Four ways it stops applying, four cases that catch each

Failure modes of tenant isolation and the probe case that catches each A two column table. Left column, how isolation stops applying: a raw query without the clause; a model missing from the client extension; a session-level SET through a transaction pooler; a connection role with BYPASSRLS or table ownership. Right column, the probe case that catches it: the per-table read through every path; the generated case for every table with a tenant column; the pool-hangover case; a role check that runs before the suite. Each way in, one case that catches it HOW ISOLATION STOPS APPLYING THE CASE THAT CATCHES IT Raw query without the clause Per-table read through every path Model missing from the extension Generated case for every scoped table Session SET through a pooler The pool-hangover case Role that bypasses RLS Role check before the suite runs Table added without a tenant column Unclassified table fails the build The list grows; the generator does not need to know in advance.
Illustrative: the mapping the probe is built around; the fifth row is the one that keeps the other four honest over time.

The role check is worth spelling out because it is the cheapest and the most often skipped. Before the suite runs, it asks the database which role the application is connected as, and whether that role is a superuser, has BYPASSRLS, or owns the tables it queries. If any answer is yes, the suite fails before a single tenant case runs, because every RLS-based case would pass for the wrong reason. A probe that reports zero leaks while connected as a role that policies do not apply to is worse than no probe.

What it costs and what it buys

The generator is a few hundred lines. It reads the schema, emits one test file per table, and runs in the same CI job as everything else against a database seeded with two tenants. The suite is slow in the way any database suite is slow, minutes rather than seconds, and the way to keep it from being skipped is to make it the job that gates deployment rather than the job that runs nightly. A leak found nightly is a leak that shipped.

What it buys is a different relationship with the sentence in the design document. The sentence still says every query is scoped to the tenant. Now it is followed by a number, the count of tables the probe checks, and by a job that turns red on the day the sentence stops being true. That is the whole difference between a convention and a property, and for a CRM where two companies' pipelines share a table, it is the difference that matters.

PostgreSQLMulti-tenant SaaSTesting
All writing

Written by Mohd Shayan

Get new posts by email

Occasional essays on engineering, AI, and building for the people technology leaves behind.

One email per new post. Unsubscribe any time.

Subscribe with RSS