Multi-Tenancy and Data Isolation
The export finished in nine seconds and the CSV had 84,000 rows. The person who clicked the button runs a company with 210 employees.
What happened is dull and it is fatal. Two weeks earlier someone refactored the “download all users” endpoint. The old query filtered by the current account. The new one, cleaner, faster, reviewed by two people, dropped a single clause on its way through the query builder. Now the admin at one customer had a spreadsheet with every user in the entire product: names, emails, password reset states, the works. No exception was thrown. The request returned 200. The dashboards were green the whole time.
This is the bug class that ends a B2B company. Not “a page was slow.” Not “a form lost its state.” One customer sees another customer’s data, the screenshot goes to a competitor or a regulator, and the trust you spent three years building is gone by lunch. Multi-tenancy is the discipline of running one product for many customers who must never, under any circumstance, touch each other’s data. Getting it right is mostly about making that leak impossible to write, not just unlikely.
Tenancy is authorization with a bigger blast radius
You already met the smaller version of this problem in authorization: a user requesting a record that belongs to someone else. Tenancy is the same shape of bug scaled up. An authorization slip leaks one order. A tenancy slip leaks everyone’s orders at once, because the thing you forgot to filter on wasn’t a user id, it was the boundary between two entire companies.
A “tenant” is one customer account: one company, one workspace, one org. Every row you store belongs to exactly one of them, and the whole job is keeping that mapping honest through every query, cache, background job, and export you will ever write. The stakes make the engineering conservative on purpose. You want isolation you can point at and prove, not isolation that holds as long as nobody makes a mistake.
There are three classic ways to draw the boundary, and the right answer is usually “it depends, and probably a mix.”
Three ways to keep tenants apart
The models differ in where the wall between tenants sits: inside a shared table, between schemas, or between whole databases. Each buys you isolation and charges you in cost and operational pain.
Shared schema: one tenant_id column
Every table gets a tenant_id column. One database, one set of tables, and rows for every customer sit side by side, distinguished only by that column.
CREATE TABLE invoices (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id uuid NOT NULL REFERENCES tenants(id),
amount_cents integer NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX invoices_tenant_idx ON invoices (tenant_id, created_at DESC);This is the cheapest model by a mile and it scales the furthest. One schema to migrate, one connection pool, one set of backups. A single mid-sized Postgres can hold thousands of tenants happily. Nearly every SaaS you have used starts here, and plenty of very large ones never leave.
The catch is the whole reason this article exists. The wall between tenants is one WHERE clause, repeated in every single query, forever. Miss it once and you get the 84,000-row CSV. That index above has tenant_id first for a reason: it is not just performance, it is a constant reminder that no query on this table is complete without it.
Schema per tenant
Postgres schemas are namespaces inside one database. You give each tenant their own: tenant_acme.invoices, tenant_globex.invoices. The tables are physically separate. A query in the wrong schema returns nothing rather than someone else’s rows, so a forgotten filter fails loudly instead of leaking.
You pick the tenant by setting the search_path at the start of a request:
SET search_path TO tenant_acme, public;Now unqualified SELECT * FROM invoices resolves to tenant_acme.invoices. Isolation is genuinely stronger, and it maps neatly onto “give me a per-customer backup.” The price shows up at scale. Every migration now runs once per schema, so a schema change across 400 tenants is 400 ALTER TABLEs that must all succeed or you are left in a split-brain state where some tenants have the new column and some don’t. Connection pooling gets fiddly too, because search_path, like any session setting, sticks to a pooled connection and has to be reset carefully (more on that footgun below). Hundreds of tenants is comfortable. Tens of thousands is a bad time.
Database per tenant
Each tenant gets a physically separate database, sometimes on a separate server. This is the strongest isolation you can offer and the easiest compliance story to tell. When a regulated customer asks “can another tenant’s query ever read our data,” the answer is a flat no, enforced by the database boundary itself, and “delete everything we have” is a single DROP DATABASE.
You pay for that clarity. Idle databases still consume memory and connections, so a thousand small tenants means a thousand mostly-idle databases, which is wasteful and eventually unworkable. Migrations become a fleet operation: a job that connects to every database in turn, applies the change, and reports which ones failed and need a retry. Backups, monitoring, and connection management all multiply by the tenant count. This model shines with a small number of large, high-value, isolation-demanding customers. It falls over under a long tail of tiny ones.
What most teams actually run
The clean answer on a slide is one model. The real answer is a hybrid. You run shared schema for the long tail of small and free tenants where density is everything, and you peel off the handful of big regulated customers into their own dedicated databases where they get isolation, a private backup schedule, and a compliance checkbox they will pay extra for.
The one forgotten WHERE
Since shared schema is where most of you will live, and where the scariest bug hides, spend real time here. The failure is not exotic. It is a query that ran without its tenant filter and returned rows it should never have seen.
Toggle the tenant filter off in the demo below and watch the other company’s invoices show up in your results. This is the exact query the leaky CSV endpoint ran. Nothing crashes. The rows just appear.
Stop relying on discipline
The instinct after a scare like this is a code review rule: “always remember the tenant filter.” That rule is worthless. Not because people are careless, but because “remember to do a thing on every query for the rest of the product’s life” is not a control, it is a wish. The query builder will refactor it away. The new hire won’t know. The one-off SQL you ran in a console at 2am to fix an incident won’t have it. You need the filter enforced by structure, in two layers that back each other up.
Layer one: a scoped data layer
Do not hand raw database access to feature code. Hand it a repository that has a tenant baked in and physically cannot produce an unscoped query.
class TenantDb {
constructor(
private readonly sql: Sql,
private readonly tenantId: string,
) {}
invoices() {
// tenantId comes from the constructor, never from the caller
return this.sql`
SELECT id, amount_cents, created_at
FROM invoices
WHERE tenant_id = ${this.tenantId}
ORDER BY created_at DESC
`;
}
invoice(id: string) {
return this.sql`
SELECT id, amount_cents FROM invoices
WHERE tenant_id = ${this.tenantId} AND id = ${id}
`;
}
}Feature code never sees tenant_id. It receives a TenantDb already bound to the current request’s tenant, and every method it can reach is scoped. This kills the accidental leak: you can no longer forget the filter, because the only handle you have already has it. Notice the second method still filters by tenant_id even though it also has an id. Never trust that a UUID is unguessable. A record lookup by primary key alone is exactly the insecure direct object reference that lets someone read a neighbour’s invoice by pasting its id.
This layer is necessary and it is not sufficient. It stops the honest mistake. It does nothing about the raw query in a migration script, the analytics job wired straight to the connection, or the ORM escape hatch someone reaches for under deadline. For those, you want the database itself to refuse.
Layer two: row-level security, the database says no
Postgres can enforce the tenant filter below your application, so a query that forgets it returns nothing instead of everything. The feature is row-level security (RLS), and it has been in Postgres since 9.5. On current Postgres (18 as of mid-2026) it is boring, stable, and exactly what you want as a backstop.
You turn it on per table and attach a policy:
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.current_tenant', true)::uuid)
WITH CHECK (tenant_id = current_setting('app.current_tenant', true)::uuid);Three things are doing work here. USING filters which rows a SELECT, UPDATE, or DELETE can even see. WITH CHECK guards writes, so an INSERT or UPDATE cannot create a row belonging to a different tenant. And current_setting('app.current_tenant', true) reads a per-connection variable you set at the start of each request. The custom name has to contain a dot; that is a Postgres rule for user-defined settings, not decoration.
Now the query from the leak diagram, run with no WHERE, returns only the current tenant’s rows. The database applied the policy. Your application forgot and it did not matter.
There is one more property that makes RLS a genuine safety net rather than a config toggle: it fails closed. Enable RLS on a table and if no policy matches, Postgres shows zero rows by default, not all of them. And current_setting('app.current_tenant', true) returns NULL when the variable was never set, so tenant_id = NULL is never true and the query returns nothing. Forget to set the tenant and you get an empty result and a bug report, not a data breach. That is the correct direction to fail.
The pooled-connection leak: SET versus SET LOCAL
Here is the bug that will actually page you, and it is subtle enough that it survives code review. You set the tenant variable on a connection, you run your queries, and everything works in testing. Then, under load, one tenant starts seeing another’s data intermittently, and you cannot reproduce it.
The cause is connection pooling. Your pool hands out a connection, you run a query, you give it back, and the pool hands that same physical connection to the next request, which might belong to a different tenant. A plain SET app.current_tenant = ... is session-scoped: it stays on the connection after your request ends and quietly becomes the default for whoever picks it up next.
The fix is SET LOCAL, which is transaction-scoped. It applies only until the transaction commits or rolls back, then reverts. Wrap the tenant setup and the actual work in one transaction, and the context is guaranteed gone before the connection returns to the pool. In practice you use the parameterized set_config form so the value is bound, not string-concatenated:
// pg: run one unit of work with the tenant pinned for exactly one transaction
async function withTenant(pool, tenantId, work) {
const client = await pool.connect();
try {
await client.query("BEGIN");
// third arg `true` = is_local: scoped to THIS transaction only
await client.query("SELECT set_config('app.current_tenant', $1, true)", [tenantId]);
const result = await work(client);
await client.query("COMMIT");
return result;
} catch (err) {
await client.query("ROLLBACK");
throw err;
} finally {
client.release(); // returns a clean connection: the SET LOCAL is already gone
}
}One real gotcha buried in there: SET LOCAL (and set_config(..., true)) outside a transaction is a no-op that emits a warning. If you see your RLS policy returning zero rows and a warning about SET LOCAL, you almost certainly ran it in autocommit mode where each statement is its own transaction, so the setting evaporated before the query ran. Open the BEGIN first. This matters double if you sit behind a transaction-pooling proxy like PgBouncer, where session-level settings are actively unsafe and SET LOCAL in an explicit transaction is the only correct pattern.
Where the tenant comes from
Everything above assumes you know the current tenant. Getting that wrong is its own class of breach, because if an attacker can choose which tenant they are, none of your isolation matters.
There are three sane sources: the subdomain (acme.app.com), a path prefix (/t/acme/...), or a claim inside a verified token or session. The one rule that ties them together is the important one.
The safe pattern resolves a candidate tenant from the URL for routing, then confirms the signed-in user is actually a member of it before anything touches the database. Resolve once, in middleware, and thread the result through a request-scoped context so no handler has to fetch it again.
// middleware: resolve once, verify membership, stash on the request
async function tenantContext(req, res, next) {
const subdomain = req.hostname.split(".")[0]; // "acme"
const userId = req.session.userId; // from a verified session
// the database decides membership, not the URL
const [membership] = await db.query(
`SELECT tenant_id FROM memberships
WHERE user_id = $1 AND tenant_slug = $2`,
[userId, subdomain],
);
if (!membership) return res.status(404).end(); // not your tenant: 404, not 403
req.tenantId = membership.tenant_id; // authoritative from here on
next();
}Returning 404 rather than 403 for a tenant you are not a member of is a small, deliberate choice: a 403 confirms the tenant exists, which leaks a little. Pretend it isn’t there.
Noisy neighbours
Shared infrastructure has a shared-infrastructure problem. One tenant runs a report that scans ten million rows, or opens a hundred connections, or hammers an endpoint in a loop, and everyone else on that database slows down or times out. Their data never leaked, but their experience got wrecked by a stranger. That is the noisy neighbour, and isolation of data does nothing to prevent it.
The containment is per-tenant quotas. Cap how much of the shared resource any one tenant can hold at once:
- Connections. Give each tenant a slice of the pool rather than first-come-first-served, so a runaway tenant cannot grab every slot. A connection pool that supports per-key limits, or a small semaphore keyed by
tenantId, does the job. - Request rate. Key your rate limiting by tenant, not just by IP or user. A tenant’s total budget is the thing you are protecting.
- Query cost. Set a
statement_timeoutso a single monster query gets killed instead of holding a connection for four minutes. Cap page sizes so nobody asks for a million rows at once.
You will also want fairness, not just caps. A tiny free tenant and your biggest paying customer both deserve to not be starved by a third party’s bad afternoon. Per-tenant limits give you a knob to tune that, and they give you the numbers to spot the noisy neighbour before the support tickets do.
The parts nobody demos
Isolation is the headline, but tenancy quietly complicates every operational task you have. These are the things that look easy in the shared-schema model right up until someone asks for them.
“Please delete all our data.” A dedicated database makes this a one-liner: DROP DATABASE tenant_globex, plus deleting its backups, done. In a shared schema it is a chore and a risk. You are issuing DELETE ... WHERE tenant_id = $1 across every table in dependency order (or leaning on ON DELETE CASCADE foreign keys), and then you still have to purge that tenant from the search index, the object store, the analytics warehouse, and the caches. Miss one and you have kept data you swore you deleted, which is its own regulatory problem. Build the “forget a tenant” routine early and test it, because you will be asked, and the clock on that request is often legal, not aspirational.
Backups and restore. Per-database backups are naturally per-tenant: you can restore one customer to last Tuesday without touching anyone else. In a shared schema your backup is all-or-nothing at the cluster level. Restoring a single tenant’s data means extracting their rows from a backup and merging them back, which is a script you want to have written before the customer who deleted their production data by accident calls you.
Migrations. In shared schema a migration is one schema change and you are done. Schema-per-tenant multiplies that by tenant count, and database-per-tenant makes it a fleet rollout with partial-failure handling, retries, and a way to answer “which tenants are on the new version?” mid-deploy. Whatever model you pick, your migration tooling has to match it.
Caching keys. This is the leak from the top of the article wearing a different hat. Every cache key must include the tenant, or you will serve one tenant’s cached response to another. A key like user-count is a landmine; tenant:acme:user-count is safe. The same goes for anything keyed in Redis, any in-memory memo, and any CDN cache. If the value depends on the tenant, the tenant belongs in the key. Every time.
Summary
- The bug that ends a B2B company is one tenant seeing another’s data. Tenancy is authorization with a far bigger blast radius, so engineer it to make the leak impossible to write, not merely unlikely.
- Three models, real tradeoffs. Shared schema (a
tenant_idcolumn) is cheapest and scales furthest but isolation is logical only. Schema per tenant is stronger but migrations run per schema. Database per tenant is strongest and the easiest compliance story but expensive, and migrations become a fleet operation. Most teams run a hybrid: shared for the long tail, dedicated for the big regulated customer. - Do not rely on discipline. A scoped data layer stops the accidental unfiltered query. Postgres row-level security is the database-enforced backstop that catches the query you forgot.
- RLS needs care.
ENABLEplusFORCE ROW LEVEL SECURITY, connect as an unprivileged role (owners andBYPASSRLSroles skip policies), and it fails closed to zero rows. - Use
SET LOCAL, neverSET. Session settings stick to pooled connections and leak into the next tenant’s request. Wrapset_config('app.current_tenant', id, true)and the query in one transaction. - Resolve the tenant from a subdomain, path, or verified token, never a client-supplied header, and confirm the user is a member before touching data.
- Contain noisy neighbours with per-tenant quotas on connections, request rate, and query cost.
- Mind the operational tail: data deletion, per-tenant restore, migrations, and above all cache keys, which must always include the tenant.