Tenant isolation that survives a bad query
A tenant predicate on every query isolates data only while every query carries it, and one missing predicate is a silent cross-tenant read. PostgreSQL row-level security puts the boundary in the database: the policy binds the tenant and denies by default, so a query without the predicate returns nothing.
On this page
Why is a WHERE clause not an isolation model?
A tenant predicate on every query that touches shared tables works until one query ships without it, and that failure is silent. A missed tenant filter raises no error and fails no type check. The query runs, returns rows from every tenant, and the test suite passes because the test database only ever seeded one.
The ways a predicate goes missing are mundane. A new endpoint written under deadline. A raw SQL report that steps around the ORM's default scopes. A background job that iterates a whole table because it always had. A support script run directly against production. An eager-loaded association that joins through a table the scoping layer never covered. None of these are exotic; every one of them turns up in any codebase old enough to have had staff turnover.
Call this what it is: a convention, with no isolation model behind it. The tenancy property of the system depends on every engineer re-implementing the same filter correctly on every query, indefinitely. A property that relies on nobody making a routine mistake will fail the first time someone does.
How does row-level security stop a cross-tenant read?
PostgreSQL row-level security attaches the tenancy rule to the table itself. A policy declares which rows a session may read and which rows it may write, and the engine applies that policy to every statement that touches the table: SELECT, INSERT, UPDATE, DELETE, whichever code path issued it. The ORM, the raw report, the background job and the support script all hit the same wall, because the wall is in the database.
That changes what a bad query can do. A query that forgets the tenant filter no longer returns everything; it returns the current tenant's rows, because the engine appended the filter the developer forgot. A write aimed at the wrong tenant fails its check instead of landing. The blast radius of an application bug collapses from every tenant's data to the data the session was already entitled to see.
This is defence in depth in the strict sense. The application still scopes its queries, and should: the policy is the layer that holds when the application does not. This is the pattern we reach for in our own multi-tenant builds. The application layer is written carefully, and the database is built on the assumption that one day some part of it will not be.
The policy pattern: bind the tenant, deny by default
The pattern has three parts. First, session tenant binding. After authentication, the application opens a transaction and sets a transaction-local variable carrying the tenant identifier, and every policy reads that variable. Transaction-local matters under connection pooling: the setting dies with the transaction, so a pooled connection cannot carry one tenant's identity into the next request. A session-level setting on a pooled connection is a cross-tenant incident waiting for traffic.
Second, a policy on every tenant table, covering both directions. The read side restricts which rows come back; the write side, the check clause, refuses inserts and updates that would place a row under another tenant. A policy that only covers reads leaves the write path open, and a corrupted write is harder to detect and clean up than a leaked read.
Third, deny by default. The application connects as a dedicated role that does not own the tables and cannot bypass policies. Table owners skip row-level security unless the table forces it, so force it. And when the tenant variable is unset, the policy evaluates to false and returns nothing. An unauthenticated or misconfigured session gets zero rows. Fail closed.
What does row-level security cost to run?
Policies are code, and untested code is broken code. Every tenant table needs tests that authenticate as one tenant and assert that another tenant's rows come back empty, on reads and on writes. That means test data seeded with at least two tenants; a suite that only ever creates one tenant proves nothing about isolation. These tests are dull to write, and they are the whole point.
Migrations need the same discipline. The migration that creates a tenant table ships the policy and the isolation test in the same change, and a build gate enforces it, because relying on a reviewer to remember is the WHERE-clause problem all over again, one level up. Schema review changes shape too: the question on every new table is no longer just what it stores, but which tenant boundary it lives inside.
There is also a query-planning cost. The engine appends the tenant predicate to every statement, so the tenant column must be indexed, and composite indexes should lead with it. Maintenance paths need deliberate role decisions as well: superusers and bypass roles step around policies entirely, which is what you want for migrations and what you do not want for a convenience connection someone keeps open in a terminal. This is least-privilege access, applied to database roles.
Why health data raises the stakes
Aurii is a multi-tenant clinical workflow platform for Australian private-hospital specialists. It records the consult and drafts the note, the letters, the discharge summary and the billing items.
The rows behind those tenants are health information, which the Privacy Act 1988 treats as sensitive information with tighter handling obligations than ordinary personal data. A cross-tenant read between two practices is a potential notifiable data breach, with an assessment obligation, possible OAIC notification, and the affected practices' patients at the end of the chain. The one designed exception is explicit: another practice sees a shared patient only after the owning doctor approves its request, and that access is time-limited and can be revoked.
That is why the isolation layer lives in the database. Aurii runs in Australia, with the app on Microsoft Azure (Australia East). Tenant data sits in PostgreSQL under row-level security, with tamper-evident audit trails through the stack. The design assumption is not that the application will never ship a bad query. Over the life of a clinical product, under deadline, across staff changes, someone will. The database is built so that when it happens, the query returns nothing instead of another practice's patients.
Where this fits in our work
This pattern comes from running it in production. Multi-tenant data layers with row-level security are part of our Azure cloud engineering work, and when we review an existing multi-tenant codebase, tenancy enforcement is one of the first things we read for at line level.
Findings arrive in plain English with concrete fixes, and a re-test proves the holes are closed.
