One Missing WHERE Clause

Picture a customer opening the monthly export in your dashboard. The columns are familiar. The numbers are not. Halfway down the file are invoices addressed to a company they have never heard of — a competitor, as it happens, on the same platform.

Nothing dramatic caused this. Somebody added a reporting endpoint on a Friday, wrote a query against invoices, joined a couple of tables, filtered by date range, and shipped it. The WHERE tenant_id = $1 was never there. Every test passed, because in the test database there was only one tenant.

That is the whole story of multi-tenant data leaks, and it is why I no longer trust isolation that lives in application code.

Discipline is not a control

If your tenant boundary is "every developer remembers the filter," then your security model has a failure rate proportional to the number of queries you will ever write. Not the number written so far — the number written later, by people who have not read this post, at 6pm, under a deadline, in a codebase where two hundred other queries already got it right.

Code review catches most of them. Most is the problem. A leak is not a degraded experience, it is a disclosure event you have to tell a customer about, and it takes one query out of thousands.

So the useful question is not "how do we remember?" It is: what would make forgetting harmless?

Three models, three different bills

A database per tenant. The strongest isolation available. A query cannot reach another tenant's rows because the rows are not in the same database. Restores are per-tenant, noisy neighbours are contained, and a customer asking "is our data physically separate?" gets a clean yes.

The bill arrives in operations. Migrations run N times and can fail on tenant 340 of 500, leaving you with a fleet in two different schema versions. Connection pools multiply. Cross-tenant analytics becomes a data pipeline instead of a query. At ten tenants this is comfortable. At a thousand it is a full-time job.

A schema per tenant. One database, one schema per tenant, search_path set per connection. Lighter than separate databases, still a fairly hard wall. But you inherit most of the migration fan-out pain, and Postgres itself starts to complain at scale — thousands of schemas means hundreds of thousands of tables, and system catalogue bloat makes planning and pg_dump slow in ways that are annoying to diagnose.

A shared table with tenant_id. One schema, one set of tables, a tenant column on everything. One migration, one connection pool, trivial cross-tenant reporting. This is what most SaaS backends run, and it is usually the right call.

What it demands in return is the honest part: you have traded a physical boundary for a logical one. The isolation is now a predicate — a thing that can be omitted. Choosing shared tables without a mechanism to enforce that predicate is taking the cheap option and skipping the payment.

Let Postgres refuse

Row-Level Security is the mechanism. You describe the visibility rule once, on the table, and the database applies it to every statement from that point on — including the query somebody writes next year without reading any of this.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
-- FORCE also applies the policy to the table's owner.
-- Without it, the owner role silently bypasses everything below.
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

Two clauses, two jobs. USING filters what you can read, update, or delete. WITH CHECK validates what you write — without it a tenant can happily insert rows stamped with somebody else's tenant_id, which is the mirror image of the leak and easier to miss.

The true in current_setting means "return NULL instead of erroring if unset." An unset tenant then yields NULL, the predicate is not true, and the query returns nothing. Failing closed to zero rows is the behaviour you want: a bug becomes an empty report, not somebody else's invoices.

Now the forgotten filter is a non-event.

-- The reporting query from the intro, with no tenant filter at all.
SELECT id, total, issued_at FROM invoices WHERE issued_at >= date_trunc('month', now());
-- Postgres rewrites it as if you had written:
--   ... AND tenant_id = current_setting('app.tenant_id', true)::uuid

The connection pool is where this goes wrong

RLS reads the tenant from a session variable, and this is the part that deserves slow reading, because it is the footgun that turns a security feature into a leak of its own.

Your API does not hold one connection per user. It borrows one from a pool, uses it, returns it. If you set the tenant with SET app.tenant_id, that setting lives on the connection, not on the request, and the connection goes back to the pool still carrying it. The next request — a different customer — borrows that same connection, and if anything on that path fails to set the tenant before querying, it inherits the previous request's identity.

The failure mode is nasty because it is intermittent. It depends on pool size, concurrency, and which handler happened to skip the setup. It will not reproduce locally with a pool of one and a single user clicking around.

SET LOCAL is the fix. It is scoped to the current transaction and reverts on commit or rollback, so nothing survives the return to the pool.

// Every tenant-scoped unit of work runs inside a transaction.
async function withTenant<T>(tenantId: string, fn: (tx: Tx) => Promise<T>): Promise<T> {
  return db.$transaction(async (tx) => {
    // SET LOCAL is transaction-scoped: it disappears on COMMIT or ROLLBACK.
    // set_config's third argument (is_local = true) is the parameterisable form —
    // SET LOCAL does not accept bind parameters, so never interpolate the id into SQL.
    await tx.$executeRaw`SELECT set_config('app.tenant_id', ${tenantId}, true)`;
    return fn(tx);
  });
}

Two constraints follow. Every tenant-scoped read now needs a transaction, including single-statement reads that would otherwise run in autocommit — slightly more round trips, and long-running transactions become a thing you watch.

And if you run a transaction-mode pooler such as PgBouncer, SET LOCAL is correct and SET is actively dangerous, because the pooler hands out a different backend per transaction. Anything session-scoped is a coin flip. This is one of the few places where I would rather pay for an extra BEGIN than reason about which connection I am on.

Make the application incapable of forgetting

RLS is the floor, not the whole building. Application code should not be able to run an unscoped query. In NestJS, the request-scoped tenant comes from the token, goes into an AsyncLocalStorage context, and the repository layer is the only thing that talks to the database.

// tenant.context.ts — one place holds the current tenant.
const storage = new AsyncLocalStorage<{ tenantId: string }>();
 
export const runWithTenant = <T>(tenantId: string, fn: () => T) =>
  storage.run({ tenantId }, fn);
 
export function currentTenant(): string {
  const ctx = storage.getStore();
  // Throwing beats defaulting. A missing tenant is a bug, not a case to handle.
  if (!ctx) throw new Error('No tenant in context');
  return ctx.tenantId;
}

// tenant.middleware.ts — set it once, at the edge.
@Injectable()
export class TenantMiddleware implements NestMiddleware {
  use(req: Request, _res: Response, next: NextFunction) {
    const tenantId = req.user?.tenantId;
    if (!tenantId) throw new UnauthorizedException();
    runWithTenant(tenantId, next);
  }
}

// tenant.repository.ts — the only path to the DB. No raw client is exported.
@Injectable()
export class TenantRepository {
  constructor(private readonly db: PrismaService) {}
 
  // Every call is wrapped, so the tenant is always set before any statement runs.
  run<T>(fn: (tx: Tx) => Promise<T>): Promise<T> {
    return withTenant(currentTenant(), fn);
  }
 
  invoicesForMonth(month: Date) {
    // No tenant_id in this query. RLS supplies it. Both layers agree.
    return this.run((tx) => tx.invoice.findMany({ where: { issuedAt: { gte: month } } }));
  }
}

Defence in depth means the repository and RLS are independent. Either one alone would prevent the leak in the intro. Together, a bypass requires two failures at once, which is a meaningfully different risk profile from one forgotten clause.

The five places the filter is actually missing

In my experience, the endpoint a developer is consciously writing is rarely the leak. The leak is in the code paths where "current tenant" is ambiguous or absent.

Background jobs. No request means no middleware, which means no tenant in context. A nightly billing job iterates over all invoices by design — and then someone copies its query into a per-tenant feature. Jobs must carry the tenant in the payload and set it explicitly, and the genuinely cross-tenant job should loop tenant by tenant rather than run unscoped.

Admin endpoints. These need to see everything, so someone connects them with a BYPASSRLS role. That role now exists in your codebase, and it will be reused — for a debugging script, a migration, a "temporary" internal tool. Keep it in a separate connection, separate config, separate module, and treat any diff that widens its use as a security review.

Exports and reports. Long queries with joins and CTEs, often written outside the repository layer because the ORM was awkward. This is the highest-risk category in most codebases: raw SQL, wide result sets, and a delivery mechanism that emails the file.

Cache keys. This one deserves its own sentence: invoice:summary:2026-08 is a cross-tenant leak with no database query involved at all. RLS cannot help you, because Redis is not Postgres. Tenant A warms the key, tenant B reads it, and every isolation control you built is bypassed by a string.

// The only cache key builder in the codebase. Tenant is not optional.
export const cacheKey = (...parts: string[]) =>
  ['t', currentTenant(), 'v2', ...parts].join(':');

Search indexes. Elasticsearch, Meilisearch, pgvector — a second store with its own filtering rules and no knowledge of your policies. Every document needs a tenant field, every query needs a mandatory filter applied by a wrapper rather than by the caller, and deletes need to be tenant-scoped too.

A test that would have caught it

Isolation is testable, and the test is short enough that there is no excuse for not having it.

it('does not leak rows across tenants', async () => {
  const a = await createTenantWithInvoice({ total: 100 });
  const b = await createTenantWithInvoice({ total: 200 });
 
  const seenByA = await runWithTenant(a.id, () => repo.invoicesForMonth(startOfMonth()));
 
  expect(seenByA).toHaveLength(1);
  expect(seenByA.map((i) => i.tenantId)).not.toContain(b.id);
});

Make it a shared helper and run it against every tenant-scoped repository method. The version that matters most runs the deliberately unfiltered query — proving RLS is on, not just that your repository is polite. Check the write side too: an insert carrying another tenant's id should be rejected by WITH CHECK, not silently accepted.

One caveat that has bitten me: the role your tests use must not be the table owner unless you set FORCE ROW LEVEL SECURITY, and it must not have BYPASSRLS. Otherwise the test passes for the wrong reason and you have a green suite guarding nothing.

What RLS costs you

I would rather name these than have you discover them in week three.

Query planning gets harder to reason about. The policy predicate is added to every scan, and the planner's estimates for current_setting(...) are not always what you want. Watch for sequential scans that used to be index scans. Which leads to indexes: every index on a tenant-scoped table should lead with tenant_id, because that column is now in the predicate of literally every query.

-- Leading with tenant_id serves both the policy and the query's own filter.
CREATE INDEX invoices_tenant_issued_idx ON invoices (tenant_id, issued_at DESC);

Debugging gets stranger. "The row exists, I can see it in psql, but the API returns 404" is nearly always a tenant setting problem, and it does not look like one until you have hit it a few times. Log the active app.tenant_id alongside slow queries; it pays for itself immediately.

And RLS still needs discipline in one place: role management. BYPASSRLS, superuser connections, and table ownership all quietly disable everything above. You have not eliminated the human factor. You have moved it from thousands of queries to a handful of roles — which is the entire point, because a handful of roles is something you can review.

The shape underneath

The pattern here is not really about tenants. It is that any invariant maintained by remembering will eventually be forgotten, and the fix is to move it to a layer where forgetting is not expressible.

You have seen the same move elsewhere. A unique constraint instead of checking before insert. A type instead of a comment saying the string is a user id. A foreign key instead of hoping the id points at something real.

So when you write down an invariant, ask which layer can refuse to break it. If the answer is "the developer," you have not written down an invariant. You have written down a hope, and hopes scale badly with headcount.