Multi-Tenant Data Isolation in Prisma and Postgres

There is one bug that matters in a multi-tenant application, and it is not a performance problem. It is a query somewhere that forgot to filter by tenant, returning another customer's rows to somebody who should never see them.

It does not throw. Nothing in the logs looks wrong. It surfaces when a user recognises a company name that is not theirs, which is the worst possible way to discover it and the one that ends contracts.

This is how to make that structurally difficult rather than a matter of remembering, using the Neon and Prisma setup I build B2B products on.

What are the three isolation models?#

They differ in where the boundary sits, and the trade is always isolation strength against operational cost.

Almost everything should be shared schema. The other two exist for genuine constraints — a compliance requirement naming physical separation, or a customer contract demanding their own database — and both cost real operational effort per tenant that scales badly past a few dozen.

It is worth noticing that the isolation column is about where the boundary is enforced rather than how strong it is in practice. A shared schema with policies enforced by the database is meaningfully safer than separate databases where the application picks the connection string from user input, and the second arrangement is more common than it should be.

How does shared schema actually work?#

Every table holding customer data carries an organisation reference, and every query filters on it. That is the whole model, and its weakness is right there in the word "every".

model Document {
  id        String   @id @default(uuid(7))
  orgId     String                          // on every customer-owned table
  title     String
  createdAt DateTime @default(now())

  org       Organization @relation(fields: [orgId], references: [id])

  // Filter column first, sort column second, or the sort will not use it.
  @@index([orgId, createdAt(sort: Desc)])
}

The index matters as much as the column. Nearly every query in a tenant-scoped application starts with the organisation and orders by time, and a composite index on that pair is what keeps it fast as the table grows past a few million rows.

What this model does not give you is any protection when the filter is forgotten. That is the next three sections.

What does the leaking query look like?#

Boring, which is why it survives review.

// The intended query.
const docs = await prisma.document.findMany({
  where: { orgId, status: 'active' },
});

// The one added in a hurry for an admin report, six weeks later.
const docs = await prisma.document.findMany({
  where: { status: 'active' },        // no orgId — returns every tenant
  orderBy: { createdAt: 'desc' },
});

The second is not obviously wrong on its own. It becomes wrong because of a constraint that lives in a different file and in somebody's head. Reviewers reading a diff about reporting do not reliably notice a missing clause in a where object.

These appear in predictable places: exports, admin views, reports, background jobs, and anything written against a deadline. All four are code paths where the author is thinking about the output format rather than about tenancy.

Raw SQL is the other reliable source. The moment somebody drops to a raw query for a report or an aggregate that the ORM expresses awkwardly, every automatic protection is bypassed — the query goes straight to the database with whatever filters were typed by hand. That is not an argument against raw SQL, which is frequently the right tool; it is an argument for row-level policies underneath, since they are the only layer a raw query cannot skip.

How do you enforce it in Prisma?#

A client extension that injects the tenant filter on every query, so forgetting it is not possible rather than not recommended.

// One tenant-scoped client per request. Every query gets the filter,
// whether or not the caller remembered it.
export function tenantClient(orgId: string) {
  return prisma.$extends({
    query: {
      $allModels: {
        async $allOperations({ model, args, query }) {
          if (!TENANT_MODELS.has(model)) return query(args);
          args.where = { ...args.where, orgId };
          return query(args);
        },
      },
    },
  });
}

Two details make this work in practice. TENANT_MODELS is an explicit list, because global tables — plans, feature flags, country codes — must not be filtered and would return nothing if they were. And the filter is applied last so a caller cannot accidentally override it by passing their own orgId.

The remaining hole is anyone importing the base client directly. Close that with a lint rule banning the raw import outside the file that constructs the scoped one. A convention that is enforced is a control; a convention that is documented is a hope.

Where does row-level security fit?#

Underneath, as the backstop that catches whatever gets past the application.

Postgres policies are evaluated by the database itself, so a query that reaches it without a tenant filter still returns nothing. That is the difference between a bug that leaks data and a bug that returns an empty list — one is an incident and the other is a ticket.

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
  USING (org_id = current_setting('app.current_org', true));

The application sets app.current_org at the start of each request or transaction. With a connection pool this needs care: the setting must be scoped to the transaction rather than the session, or a pooled connection carries one tenant's context into another's request — which is a considerably worse bug than the one you were preventing.

Note that the database owner and any superuser bypass policies entirely, so the application must connect as a restricted role. A policy that the application user bypasses is decoration.

How do you test isolation properly?#

With tests written from the attacker's position, not the user's.

Seed two tenants, always#

Every integration test fixture should contain at least two organisations with overlapping-looking data. A test suite with one tenant cannot detect a missing filter, because there is nothing to leak.

Assert on absence, not presence#

The useful assertion is that tenant B's records do not appear in tenant A's results — not merely that tenant A's do. Those are different tests and only the first catches the bug.

Test by id, not just by list#

A list endpoint that filters correctly may sit beside a detail endpoint that fetches by primary key without checking ownership. Request tenant B's record id as tenant A and assert a not-found, which is also the correct response — a forbidden reveals that the id is real.

Cover the paths nobody thinks about#

Exports, reports, webhooks, background jobs, admin views. These are where the leaks actually live, and they are the least-tested code in most applications precisely because they are not the main flow.

What about migrations across tenants?#

On shared schema this is the model's biggest advantage: one migration, applied once, done.

Schema-per-tenant means running every migration N times, handling the case where it succeeds for 40 tenants and fails on the 41st, and living with a system that is temporarily in two states. Database-per-tenant is the same problem plus connection management.

That difference is worth more than it first appears. With shared schema a migration is an ordinary deploy concern; with the alternatives it is a distributed operation with partial-failure semantics, and it needs tooling and a runbook of its own.

It is also why per-tenant models scale badly with tenant count rather than with data volume. Fifty tenants is manageable. Five thousand is a platform team.

How do you handle the noisy neighbour?#

The objection people raise against shared schema is that one heavy customer degrades everyone. It is a real effect and it is almost never solved by separating the data.

The usual cause is a single tenant with far more rows than the rest, and the fix is indexing rather than isolation. A composite index starting with the tenant column means a query for a small tenant does not care how large the biggest one is — the index scan is scoped before any rows are read.

Where a genuine problem remains, it is usually one of three things: a query without a tenant-leading index doing a sequential scan, an unbounded result set where a large tenant returns a hundred thousand rows to a page expecting fifty, or a background job for one tenant occupying every worker. All three are addressed with limits and queue concurrency caps rather than with separate databases.

Separating the data does fix noisy neighbours, at the cost of multiplying every migration, every connection pool and every operational task by tenant count. That trade is worth making for a handful of very large customers and is a poor one below that, which is why the honest answer is nearly always to fix the index first and measure again.

When is schema-per-tenant actually right?#

Three situations, and none of them is "our customers are large".

• A compliance requirement naming separation. Some regulated sectors specify it, and an auditor is not persuaded by row-level security however sound it is.
• A contractual commitment made during a sale. Occasionally the honest answer is that this was promised and now has to be delivered.
• Genuinely divergent schemas per customer — an enterprise product where tenants have custom fields and tables. Rare, and usually better solved with a JSON column.

What is not on the list: performance. A shared schema with proper composite indexes handles very large tenant counts, and the noisy-neighbour problem is better addressed with query limits and connection pooling than with physical separation.

How do you handle a user in several organisations?#

A membership table, and an explicit current-organisation concept in the session.

The common mistake is putting orgId on the user record, which makes belonging to two organisations impossible and is exactly the kind of thing that becomes a schema migration later. Membership is a relationship, so it belongs in its own table with the role on it.

model Membership {
  userId String
  orgId  String
  role   String                 // owner | admin | member

  @@id([userId, orgId])         // one membership per pair
  @@index([orgId])              // "who is in this org"
}

The session then carries a current organisation, validated against membership on every request. Validated, not trusted — a client that can set its own current organisation is a client that can read any tenant by changing a value, which is the same leak arriving through the front door.

Switching organisations then needs care about anything cached per user rather than per membership. A user with two organisations who switches while a cached list is still warm will see the previous tenant's data, which looks exactly like a leak to whoever reports it. Include the current organisation in every cache key that holds tenant data, and invalidate on switch.

Invitations are the other place this model gets fiddly. An invite is sent to an email address that may not correspond to an account yet, so it cannot reference a user id. Model it as its own record keyed by email and organisation, consumed when somebody signs up or accepts while logged in — and expire them, because an invitation that works indefinitely is a standing grant to whoever eventually controls that address.

What else leaks besides database rows?#

Four surfaces that sit outside the query layer entirely, and are missed precisely because the database work felt like the whole job.

Uploaded files#

Object storage has no tenant column. A file at a guessable path is readable by anyone who guesses it, and "nobody will guess" is not access control. Prefix every key with the organisation id and serve through short-lived signed URLs issued only after the same membership check the database queries use.

Background jobs#

A job carries an id and runs later, outside the request that created it. If the worker loads data using an unscoped client — which is the easy way to write it — the tenant filter never applies. Jobs should carry the organisation id in their payload and construct a scoped client from it, exactly as a request does.

Caches and search indexes#

A cache key without the organisation in it serves one tenant's data to another, and it is worse than a database leak because it is intermittent and therefore hard to reproduce. The same applies to any search index: the tenant has to be part of the key or the filter, not applied afterwards.

Error reports and logs#

Exception payloads frequently contain the record that caused them. Anyone with access to error tracking can then read customer data without touching the application. Scrub before sending, and treat the error tracker as a system that holds production data — because it does.

Each of these needs the same membership check the database uses, which is an argument for that check living in one function rather than being reimplemented per surface. Deriving it from one place is the same discipline that stops types drifting.

How do you find the leaks you already have?#

Three passes, in increasing order of effort, and the first finds most of them.

Grep for the unscoped client#

Every direct import of the base Prisma client outside the file that builds the scoped one is a candidate. This is a five-minute audit and it reliably surfaces the exports and admin views that were added quickly.

Turn on policies in staging and watch what breaks#

With row-level security enabled, any query missing its tenant context returns nothing rather than everything. Run the application against production-shaped data on a branch and the broken pages are the list of unfiltered queries — an unusually direct way to find them.

Write the two-tenant test suite before fixing anything#

Seed two organisations and assert absence across every endpoint. The failures are the audit, and unlike a manual review the suite keeps working afterwards. This is the same reasoning as gating on tests rather than intentions — a check that runs is a control, and a checklist is a hope.

What does this cost in practice?#

A day or two on a new project, and considerably more retrofitted.

On a greenfield build: an orgId column on customer tables, one client extension, policies on the tenant-scoped tables, and two-tenant test fixtures. Perhaps two days including the tests, and it is invisible thereafter.

Retrofitting is the expensive path — a column on every table, a backfill inferring which tenant historical rows belong to, an audit of every existing query, and policies added to a schema whose queries were written assuming their absence. Two to four weeks is realistic, and the backfill is the part that can genuinely be impossible if the information was never recorded.

The column is free on day one and a multi-week migration on day two hundred. That asymmetry is the entire argument for adding it before you need it.

Conclusion#

Use shared schema with a tenant column unless a compliance requirement or a contract says otherwise. Put orgId on every customer-owned table with a composite index on it and your common sort column.

Then make forgetting the filter impossible rather than discouraged: a Prisma client extension that injects it on every query, a lint rule preventing direct use of the unscoped client, and row-level policies underneath as the backstop for whatever still gets through.

Test from the attacker's side with two tenants seeded, asserting that the other tenant's data is absent — including on fetch-by-id, exports and background jobs, which is where the real leaks live. The whole thing is about two days on a new project, and it is the difference between a bug that returns an empty list and one that ends a contract.

And remember the surfaces outside the database. Files in object storage, cache keys, search indexes, job payloads and error reports all hold tenant data and none of them is protected by a Postgres policy. Each needs the same membership check, which is the argument for that check existing once in a function everything calls rather than being reimplemented per surface.

If you are adding this to something already live, start by enabling policies against a branch with production-shaped data. The pages that break are your list of unfiltered queries, and it is a far more direct audit than reading code hoping to spot a missing clause. Fix those, add the client extension so new code cannot reintroduce the problem, then write the two-tenant suite so the fix stays fixed.