Why is a WHERE clause not an isolation model?
Most multi-tenant products enforce tenancy the same way: every query that touches shared tables carries a predicate scoping rows to the current tenant. It works until a single query ships without it, and the failure is silent. A missed tenant filter does not raise an error or fail a 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, not an isolation model. The tenancy property of the system depends on every engineer re-implementing the same filter correctly on every query, indefinitely. A security property that relies on nobody ever making a routine mistake is not a property. It is a habit, and habits break.
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, not in the code.
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, not all of them. 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 precisely what you do not want for a convenience connection someone keeps open in a terminal. This is the least-privilege practice from our trust page, applied to database roles.
Why health data raises the stakes
Aurii is multi-tenant clinical software. A specialist speaks a consult and Aurii drafts the clinical note, the letters and the billing. 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 not a bug ticket to triage next sprint. It is a potential notifiable data breach, with an assessment obligation, possible OAIC notification, and the affected practices' patients at the end of the chain.
That is why the isolation layer lives in the database. Aurii runs on Azure in Australia: Container Apps for the application, PostgreSQL with tenant row-level security for the data, Key Vault for secrets, with tamper-evident audit trails and rate limiting 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.
Our approach page describes how an engagement runs from start to finish; the short version is that findings arrive in plain English with concrete fixes, and a re-test proves the holes are closed.