Every multi-tenant application starts the same way: a tenant_id column and a filter in every query. It works until the day somebody writes a query without the filter, and then it fails in the worst way a SaaS product can fail. We have built three multi-tenant platforms in the last eighteen months, and in each one the isolation lives in the database rather than in the application code.
The three approaches, and what each costs
| Approach | Isolation | Cost |
|---|---|---|
| Filter in application code | As good as your discipline | One forgotten clause leaks data |
| Database per tenant | Absolute | Migrations, connections and cost multiply by tenant count |
| Shared tables with row-level security | Enforced by Postgres | Setup care: policies, roles, and no bypass |
We use the third for tenant data. On our reputation platform, 79 tables carry an organisation column, 76 have row-level security enabled, 47 force it even for the table owner, and 77 policies are defined. Application code does not filter by tenant at all.
How the request actually runs
Every tenant query runs inside a transaction that switches to a Postgres role without bypass rights, then sets a per-request organisation identifier that the policies read. If the identifier is missing, the policies match nothing and the query returns an empty set. There is no path where a forgotten filter returns another tenant's rows, because the database will not hand them over.
BEGIN;
SET LOCAL ROLE app_tenant; -- no BYPASSRLS
SET LOCAL app.org_id = '...'; -- read by every policy
SELECT * FROM reviews; -- no WHERE org_id needed
COMMIT;"Moving the boundary into the database converts a catastrophic failure mode into an ordinary one: an empty result instead of a breach."
The part people forget: partitions do not inherit policies
In our voice platform the shared national directory is list-partitioned across the fifty states plus the District of Columbia. Postgres does not propagate row-level security to child tables, so it has to be enabled on all 52 partitions explicitly. Enable it on the parent only and every partition is wide open while the parent looks correctly locked.
Shared data with per-tenant state
Not everything is tenant-owned. That directory is one canonical row per business, shared by every tenant. Copying it per tenant multiplies storage by tenant count; putting tenant ids on the shared rows breaks the sharing. So the shared half carries no tenant policy at all and is reachable only through the service role, with the backend filtering it explicitly.
Per-tenant state over those shared rows lives beside them in an audit table with its own policy and a composite foreign key against both the business id and its partition key. A tenant sees their own notes and suppressions on rows everybody else can also see.
Defence in depth, because one layer is one point of failure
On a Dutch lead-generation platform we built, the intake tables accept an insert from an anonymous caller and allow no select at all unless the caller's profile row carries the admin role. Above that, edge middleware refreshes the session on every non-static request and bounces unauthenticated traffic off the console. Below that, each admin route handler resolves the user again and re-reads the role from the database rather than trusting the middleware that already ran.
The redundancy is the point. A middleware matcher is one regular expression away from silently exempting a path, and a route handler is one refactor away from losing its guard, but a policy evaluated inside Postgres fails closed no matter which layer above it was misconfigured.
Detecting silent refusals
There is one trap worth knowing. A delete or update that the policy declines can complete with no error object at all: the client sees no error and infers success, while nothing changed. We hit exactly that on an admin dashboard, where an operator deleted a lead, saw it confirmed, and found it still there after a refresh.
The fix is to make every privileged write ask for its affected rows back and treat an empty array as a rejection rather than a success. Relaxing the policy would have removed the protection; the goal was to detect the refusal, not to stop refusing.
A migration path if you are already shipping
- 01Add the tenant column everywhere it is missing and backfill it.
- 02Enable row-level security table by table, starting with the ones that hold customer data.
- 03Create a role without bypass rights and move application connections onto it.
- 04Set the tenant identifier per transaction, not per connection, so a pooled connection cannot leak it.
- 05Force row-level security on the tables where even the owner should not bypass it.
- 06Remove the application-side filters last, once the policies are proven, so you are never relying on neither.
The work is a few days. The alternative is trusting that nobody on your team ever writes a query in a hurry.
Questions we get asked
- Is Postgres row-level security fast enough for production?
- Yes, when policies are simple comparisons against a session variable and the tenant column is indexed. The platforms described here run 218 routes and 21 scheduled jobs against policy-protected tables without the policies being the bottleneck.
- Should each tenant get its own database?
- Only when the domains genuinely must not share, for example separating health data from social data. A database per tenant multiplies migrations, connections and cost by the number of customers, which is the wrong trade for most SaaS products.
- Does row-level security apply to partitioned tables automatically?
- No. Postgres does not propagate row-level security to child tables, so every partition has to have it enabled explicitly. A parent that looks locked can sit on top of wide-open partitions.
Mahad Ali
Co-founder & CEO · Principal Architect
The tech guy. Architecture, data models, pipelines, and the production incidents at three in the morning. Most of the engineering behind the sixteen products, and the first reply to your message.