Multi-tenant data isolation

The shape of the problem

Isolation in a shared-schema application rests on one column. Every tenant-scoped table carries an organization id, and every query that touches such a table filters on it. Where that filter is written determines how much you are relying on discipline and how much on the system.

The failure is quiet. A missing filter does not error, it returns more rows — usually rows that look plausible, in an interface that will happily render them. Somebody sees another company's data, and the incident is discovered by the customer rather than by a test.

The three approaches below are not alternatives so much as layers. Application-level filtering alone is where most products start; adding a database-level constraint is what stops the Friday endpoint from being a breach.

Where the tenant id comes from

The organization id must come from the verified session, never from the request. A tenant identifier in a path, a query string or a request body is a value the caller chose, and trusting it converts every endpoint into a way to read any tenant's data by changing one number.

This sounds obvious and is violated constantly, because a path like /organizations/:id/invoices is a natural way to write a URL. The rule that survives review is that the id in the path is checked against the session rather than used from it: if they disagree, the answer is 404, not 403 — a 403 confirms the resource exists.

1export async function GET(request: Request) {
2  const session = await requireSession(request);
3 
4  // Kiraci kimligi YALNIZ oturumdan gelir — istekten degil.
5  const organizationId = session.organizationId;
6 
7  const invoices = await db.invoice.findMany({
8    where: { organizationId },
9    orderBy: { issuedAt: "desc" },
10  });
11 
12  return Response.json({ data: invoices });
13}

Making it structural

The strongest version pushes the filter below the application, so that a query written without a tenant context cannot return rows rather than returning all of them. In PostgreSQL that is row-level security with a session variable set at the start of each transaction; other databases have equivalents.

The practical benefit is not that the application code changes — it barely does — but that the failure mode inverts. A forgotten filter now produces an empty result during development, which somebody notices immediately, instead of a full result in production, which nobody does.

1. 1Add organization_id to every tenant-scoped table, not null, indexed, and part of the primary key where the access pattern allows.
2. 2Enable row-level security on those tables and write one policy comparing organization_id to a session setting.
3. 3Set that setting from the verified session at the start of every request transaction, in one place — connection acquisition, not per query.
4. 4Give background jobs and migrations an explicit tenant context too, or an explicit bypass role that is used deliberately and audited.
5. 5Add one test per table that asserts a read with the wrong tenant context returns nothing. This is the test that catches the regression three years from now.

The places that are not the database

Isolation is not only a query concern. Caches keyed without a tenant id serve one customer's data to another after a deploy reorders something. Search indexes have their own filtering that has to agree with the database's. Exported files land in object storage where the path is the only thing separating tenants, and a signed URL that outlives the session it was issued for is a slow leak.

Background jobs are the most common gap, because they run without a session and therefore without the context everything else depends on. Give each job an explicit tenant, pass it through the queue, and treat a job that processes all tenants in one pass as a design that needs its own review rather than a convenience.

Related