Now-Next

SaaS architecture

Prisma and PostgreSQL row-level security, measured

Setting the tenant for PostgreSQL row-level security in Prisma 7, measured: $executeRaw, batch and interactive transactions, and the client extension.

Chris van Eijk, Now-Next · · 9 min read

Row-level security in PostgreSQL needs to know the tenant, and the usual way to tell it is a setting on the connection: set_config('app.tenant', …). In Prisma that is one $executeRaw — and where you put it decides whether a request sees its own rows, no rows, or another customer’s rows. In Prisma there is a second question: whether the query that follows runs on the same connection at all.

We measured five ways on Prisma 7. Setting the tenant with a plain $executeRaw and then querying failed in a way the setups in our earlier articles did not: under concurrent load, requests that did set their tenant got another customer’s invoices in about half the cases, because nothing kept the setting and the query on the same connection. Prisma’s own row-level-security extension example kept tenants apart — until it was used inside $transaction, where its queries left the transaction: a write that should have been rolled back stayed in the table. What held one at a time and under the same concurrent load was an interactive transaction that sets the tenant itself.

The setup

Prisma 7.10.0 with @prisma/adapter-pg 7.10.0 and node-postgres 8.23.1, Node 22.23; PostgreSQL 17.10 in a throwaway container. Measured on 4 October 2026. (That day the npm latest tag of prisma pointed to a release candidate of 8.0; we used 7.10.0, the latest 7.x release.)

  • One table invoices, 20 rows for tenant 1 and 10 for tenant 2, row-level security forced, policy using (tenant_id = nullif(current_setting('app.tenant', true), '')::int). Prisma connects as an ordinary role, not the table owner.
  • Prisma 7 runs its queries through a driver adapter; with @prisma/adapter-pg the connections come from a node-postgres pool, default size 10. One PrismaClient per test, as an application would have one.
  • A “request” sets the tenant, if it has one, and counts the invoices with prisma.invoice.count(). Four requests in a row: tenant 1, no tenant, tenant 2, no tenant. “No tenant” stands for any path that does not set one — a public page, a health check, a handler someone forgot.

What did each request see?

How the tenant is setTenant 1No tenantTenant 2No tenant
$executeRaw set_config(…, false), then the query20201010
$executeRaw set_config(…, true), then the query0000
batch $transaction([set_config(…, true), query])200100
interactive $transaction(async (tx) => …)200100
client extension, as in Prisma’s example200100

Rows counted per request, one request at a time. Bold is another customer’s data; italics is a customer who sees none of their own.

Why does the plain $executeRaw go wrong twice?

Because every Prisma call outside a transaction takes a connection from the pool and gives it back. The $executeRaw that sets the tenant and the count() that follows are two calls, and nothing ties them to the same connection.

One request at a time, that hides: node-postgres hands out the connection that was returned last, so both calls happened to get the same one, and the setting — made with set_config(…, false), for the whole session — stayed on it for the next request. That is the leak in the first row: the requests without a tenant counted the previous customer’s invoices.

Under concurrency the two calls come apart. Thirty requests at a time, twenty rounds, each request for tenant 1, tenant 2 or none; one run:

Request forOwn rowsAnother customer’s rowsNo rows
tenant 1 (200)105950
tenant 2 (200)951041
no tenant (200)—1964

A request that had set its own tenant got the other tenant’s invoices in 199 of 400 cases; two more runs gave 200 and 192. Its count() ran on a connection where another request had just set another tenant. In a run where we recorded the backend of every call, 180 of the 192 wrong counts ran on a different connection than their $executeRaw, and the other 12 on the same one, after another request had overwritten the setting in between. Most right counts were luck: 194 of them also ran on a different connection, one that happened to carry the right tenant. This is not a leftover from an earlier request that a reset would clear; it is a race between requests running at the same time.

The transaction-local form, set_config(…, true), does not leak, but on its own it finds nothing: outside a transaction the statement is its own transaction, and the setting ended with it before the query ran.

Why does the extension break inside $transaction?

Prisma’s row-level-security example wraps every model query in a batch transaction that sets the tenant first:

const forTenant = (tenant: string) => Prisma.defineExtension((prisma) =>
  prisma.$extends({ query: { $allModels: {
    async $allOperations({ args, query }) {
      const [, result] = await prisma.$transaction([
        prisma.$executeRaw`select set_config('app.tenant', ${tenant}, true)`,
        query(args),
      ]);
      return result;
    },
  } } }));

Used on its own it was correct: 20, 0, 10, 0 one at a time, and 0 of 400 wrong counts and 0 of 200 leaks under the same concurrent load. The README warns that “explicitly running transactions with companyPrisma.$transaction() may not work as intended”, and calls the extension “an example only”, not for production. We measured what “not as intended” means.

Inside an interactive transaction on the extended client, the extension’s queries do not run in that transaction. The tenant setting on the transaction’s connection was empty before and after a count(), and the count still returned tenant 2’s 10 rows: it had run on another connection, in the extension’s own batch transaction. A handler that created an invoice inside $transaction and then threw left the invoice behind — tenant 2 counted 10 before and 11 after. The new row carried a different transaction ID than the surrounding transaction, which was still open at the time; the create had gone through the extension’s own batch transaction and was committed there. The same handler written as a plain interactive transaction, without the extension, counted 11 before and 11 after: the insert was rolled back. So with the extension, a transaction around several writes is not a transaction.

What works?

An interactive transaction per request that sets the tenant as its first statement, with every query of the request going through tx:

async function withTenant<T>(tenant: string, fn: (tx: Prisma.TransactionClient) => Promise<T>) {
  return prisma.$transaction(async (tx) => {
    await tx.$executeRaw`select set_config('app.tenant', ${tenant}, true)`;
    return fn(tx);
  });
}

// per request
const invoices = await withTenant(tenantId, (tx) => tx.invoice.findMany());

The setting ends with the transaction, so the connection goes back to the pool without one — unless something set it session-wide on that connection; see the NestJS section below. It gave 20, 0, 10, 0, a failing write inside it was rolled back, and under the concurrent load above it gave 0 wrong counts of 400 and 0 leaks of 200, in two runs. The batch form, $transaction([set_config(…, true), query]), is correct too, but only for queries that do not depend on each other’s results.

Requests that counted another customer's invoices, 30 requests at a time
Variant, request forCounted another customer's rows
$executeRaw set_config(…, false), then the query, tenant 195 of 200
$executeRaw set_config(…, false), then the query, tenant 2104 of 200
$executeRaw set_config(…, false), then the query, no tenant196 of 200
Interactive transaction, tenant 1 or 20 of 400
Interactive transaction, no tenant0 of 200

Bar length is the share of the requests in that row. The first three rows are the one run in the concurrency table further up (two more runs gave 200 and 192 wrong counts of 400). The highlighted rows are the interactive transaction above, with the same result in both of its runs.

Two things come with an interactive transaction. Prisma’s defaults are a timeout of 5,000 ms for the whole transaction and a maxWait of 2,000 ms to get a connection, so a request that calls a slow external service inside it fails. And it holds a connection from the pool for the whole request, so a pool of 10 serves at most 10 such requests at a time: with 30 requests that each held their transaction for 1.2 s, no more than 10 ran at once, and 10 of the 30 failed with “Unable to start a transaction in the given time” after maxWait. Anything that has to work across tenants — an admin overview, a nightly job — needs its own route around the policy; the ways that go wrong are in row-level security in PostgreSQL: where tenants leak.

What if a NestJS service uses the injected client inside $transaction?

Then the query is not in the transaction. In the usual NestJS setup, class PrismaService extends PrismaClient as in the NestJS documentation, the service injected into another service is the outer client, and it is easy to call this.prisma.invoice.count() inside a $transaction callback instead of tx.invoice.count(). We measured that on 16 October 2026, with Prisma 7.10.0, @prisma/adapter-pg 7.10.0 and PostgreSQL 17.11, using the outer client in the callback of the transaction above — in plain Node.js, not inside NestJS:

Requests in flightCorrectCounted 0Failed
tx.invoice.count()9, 10 or 30600 of 60000
prisma.invoice.count()90600 and 6000
prisma.invoice.count()10, the pool size0597 and 5983 and 2
prisma.invoice.count()3009 and 9591 and 591, in about 55 seconds

Two runs of 600 requests each. One request at a time, the transaction set the tenant on one backend and the count ran on another, outside the transaction, where no tenant was set: 0 rows, no error. When every connection was held by an open transaction, each count waited for another connection: at 10 in flight, the pool size, that happened once per run (a stall of about 5 seconds, then 3 and 2 failed commits); at 30 it was the rule. pg-pool waits for a connection without a limit, here as in TypeORM, where nothing in the application ended the same stall. In Prisma the defaults of the interactive transaction ended it: at 30 in flight, in each run 490 requests failed with Unable to start a transaction in the given time (after maxWait) and 101 with A commit cannot be executed on an expired transaction (after timeout).

A count of 0 is the safe outcome, and it depends on the other connections being clean — a set_config(…, false) anywhere in the application, as in the first row of the table further up, leaves them otherwise. We first left set_config('app.tenant', '2', false) on all ten connections: the outer-client count in a request for tenant 1 then returned tenant 2’s 10 invoices, 300 times out of 300. A prisma.invoice.create() for tenant 1 inside the transaction, followed by an exception, was refused by the policy on a clean pool; with tenant 1 left on the connections it succeeded, and the row stayed after the transaction rolled back (one run). With tenant 2 left on the connections, the transaction through tx stayed correct for tenants 1 and 2 (600 of 600, two runs) — but a request that skipped set_config because it had no tenant counted tenant 2’s 10 rows. Set '' explicitly when there is no tenant: with set_config('app.tenant', '', true) the same transaction counted 0.

In a NestJS service, pass tx down to whatever runs inside the transaction, and do not use the injected client between $transaction(async (tx) => { and its closing brace.

What does it cost?

Average over 300 requests one at a time, alternating between tenant 1 and tenant 2; ranges over four runs per variant, on two separate occasions. The extension variant builds its $extends client per request, as an application with a tenant per request would:

ConfigurationPer request
$executeRaw session-level, then the query (leaks)0.50–0.54 ms
interactive transaction0.71–0.77 ms
client extension0.77–0.86 ms
batch transaction0.78–0.91 ms

The correct variants cost about 0.2–0.4 ms per request more than the leaking one — the begin and commit around the query. The differences between the three correct variants are within the variation between runs.

What we did not measure

NestJS itself and request-scoped context such as nestjs-cls, Prisma 6 and earlier with the default query engine and its own connection pool instead of the pg adapter, Prisma Accelerate, other driver adapters, and Prisma behind PgBouncer in transaction mode — the rules for a pooler in front of the database are measured in PgBouncer and row-level security. The same question for Python frameworks is in Django and PostgreSQL row-level security and SQLAlchemy and PostgreSQL row-level security, and for PHP in Laravel and PostgreSQL row-level security. Drizzle on the same pg pool showed the same race — measured in Drizzle and PostgreSQL row-level security. When a policy does not return what you expect, the checks are in debugging row-level security in PostgreSQL. The timings come from one machine and are for comparison.

Sources: the Prisma ORM v7 documentation on transactions and batch queries (sequential operations, interactive transactions, maxWait and timeout defaults) and Prisma’s row-level-security extension example (code and caveats), both read on 4 October 2026; the source of pg-pool 3.14.0 as shipped with node-postgres 8.23.1 (default pool size 10, idle connections reused last-in first-out). All measurements are our own, on Prisma 7.10.0 and PostgreSQL 17.10, on 4 October 2026; the NestJS section on PostgreSQL 17.11, on 16 October 2026.

Let's talk

What are we building?

One conversation is enough to know whether we fit. Tell us what you have in mind — we will tell you how fast it can happen, and whether we are the right people for it.

Start the conversation