A Tenant Leak Does Not Throw an Error. It Just Returns the Wrong Rows.

Most bugs announce themselves. A tenant leak does not. The query runs, the status code is 200, the page renders, and somebody in one company is looking at another company's payroll.

That is what makes multi-tenancy different from the rest of the work. There is no stack trace to grep for and no error rate to alert on. The system is behaving exactly as written. It was written wrong.

This is how we build isolation into multi-tenant products so that a mistake has to get past three independent layers before it reaches a user, and so that every build actively tries to break it.

The two ways tenant data actually escapes

In every leak we have looked at, the cause was one of two things.

The first is a missing filter. Somebody wrote a query without the tenant predicate. Usually it is not the obvious code. It is the admin export, the cron job, the "quick" reporting endpoint, the findById helper that takes a primary key and nothing else.

// Looks harmless. It is the whole bug.
async function getPayslip(id: string) {
  return db.one('SELECT * FROM payslips WHERE id = $1', [id]);
}

An id is globally unique, so this never throws. It just returns whatever row matches, for whoever asks.

The second is a re-derived tenant. The request arrives with a verified tenant, and then forty lines deeper, some function reads the tenant again out of a request body, a query string, or a header, because that was convenient at the time.

// The tenant was already established. This throws it away.
async function listEmployees(req: Request) {
  const tenantId = req.body.tenantId ?? req.query.tenantId;
  return db.many('SELECT * FROM employees WHERE tenant_id = $1', [tenantId]);
}

Nobody writes this on purpose. It appears a year later, inside a feature that had a legitimate reason to accept a tenant parameter, and it is a direct object reference to every other tenant in the system.

Both of these are failures of discipline, and discipline does not survive a team of eight and a two-year roadmap. So the rules below are structural. They do not ask anyone to remember anything.

Rule 1: resolve the tenant once, at the edge, and carry it

Tenant resolution happens exactly once per request, as early as possible, from the authenticated session. After that it is read-only context. It is never re-derived from anything the caller supplied.

In Node, AsyncLocalStorage is the right tool for this, because it survives await boundaries without being threaded through every signature.

import { AsyncLocalStorage } from 'node:async_hooks';

type RequestContext = { tenantId: string; userId: string };

export const context = new AsyncLocalStorage<RequestContext>();

export function tenantMiddleware(req, res, next) {
  const session = req.session; // already authenticated
  if (!session?.tenantId) return res.status(401).end();

  context.run({ tenantId: session.tenantId, userId: session.userId }, next);
}

export function currentTenant(): string {
  const ctx = context.getStore();
  if (!ctx) throw new Error('No tenant context on this execution path');
  return ctx.tenantId;
}

AsyncLocalStorage lives in node:async_hooks, was added in v13.10.0 and v12.17.0, and was marked stable in v16.4.0. getStore() returns undefined outside a run() call, which is exactly the behaviour you want: the throw in currentTenant() means any code path that somehow escaped the middleware fails loudly instead of quietly running unscoped.

Worth saying plainly: a background job, a queue consumer, or a migration script has no request and therefore no context. Those paths must open their own context explicitly, one tenant at a time. "It is only a cron job" is how the admin export got written without a filter.

Rule 2: let the database refuse, not just the application

Application-level scoping is layer one. It is also the layer most likely to have a hole in it, because it is the layer humans edit every day. Postgres row level security gives you a second layer that does not care how careless the query was.

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
ALTER TABLE employees FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON employees
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

Three details in there carry most of the weight.

Enabling RLS is fail-closed. The Postgres documentation is unambiguous: "If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified." So the dangerous state is not "RLS on, policy wrong". It is "RLS never enabled on the table somebody added last sprint".

FORCE is not optional. By default, "table owners normally bypass row security", and a table owner "can choose to be subject to row security with ALTER TABLE ... FORCE ROW LEVEL SECURITY". Without FORCE, an application connecting as the owning role sails straight past every policy you wrote, and your test suite happily agrees that isolation works.

The second argument to current_setting is what makes it safe. With missing_ok set to true, the function returns NULL instead of throwing when the setting was never set. That matters because tenant_id = NULL evaluates to NULL, which is not true, so the policy returns no rows. Forget to set the tenant and you get an empty result, not an error and not everybody's data.

There is one more property that is easy to miss. A policy "will be evaluated for each row prior to any conditions or functions coming from the user's query". So a leaky user-defined function in a WHERE clause cannot see rows the policy already excluded.

Rule 3: set the tenant per transaction, never per connection

The natural-looking move is to set the tenant right after the connection is checked out of the pool. Do not do that. A pooled connection is handed to the next request when yours is done, and if anything short-circuits before the reset, the next request inherits your tenant.

Bind it to the transaction instead, so the database tears it down for you.

export async function withTenantTx<T>(fn: (tx: Tx) => Promise<T>): Promise<T> {
  const tenantId = currentTenant();

  return db.tx(async (tx) => {
    // is_local = true  ->  dies with this transaction
    await tx.none("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
    return fn(tx);
  });
}

set_config(name, value, true) is the function form of SET LOCAL: per the docs, "if is_local is true, the new value will only apply during the current transaction". There is no reset to forget, no finally block to get wrong, and a rollback cleans it up like everything else.

Where the connection pooler bites

If PgBouncer sits between the application and Postgres, the pooling mode changes what session state means. Its own feature map lists these as never compatible with transaction pooling:

This is the strongest practical argument for rule 3. A session-level SET is listed as "Never" under transaction pooling, because the server connection underneath you can change between statements. Inside an explicit transaction the pooler holds one server connection for the life of that transaction, and SET LOCAL expires with it, so the tenant setting cannot outlive the work it belongs to. The PgBouncer docs do not call out SET LOCAL specifically, so if you run a pooler, verify this against your own configuration rather than taking it on trust.

Rule 4: the application role must not own the tables

This one is short and it undoes everything above when you get it wrong.

Superusers and roles carrying the BYPASSRLS attribute "always bypass the row security system when accessing a table". Table owners bypass it too unless you used FORCE. So the role your application connects as should own nothing, inherit nothing, and hold no BYPASSRLS.

CREATE ROLE app_runtime LOGIN PASSWORD :'pw' NOBYPASSRLS;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_runtime;

-- migrations run as a different role that DOES own the tables
CREATE ROLE app_migrator LOGIN PASSWORD :'mpw';

Then assert it, because a later migration can quietly change it:

SELECT rolname, rolsuper, rolbypassrls
FROM pg_roles
WHERE rolname = 'app_runtime';
-- expect: f, f

Rule 5: USING hides rows, WITH CHECK stops you writing them

USING is the read filter. WITH CHECK governs rows being inserted or modified. If you give a policy only a USING clause, Postgres "implicitly provides a WITH CHECK clause identical to its USING clause" for the commands that need one, which is usually what you want.

Where this bites is a policy written only FOR SELECT. Reads are then isolated and writes are not, so an insert can create a row belonging to another tenant that the author of the insert can never see again. The row is real, it is in the other tenant's exports, and nothing in your logs points at it.

Write both clauses explicitly. It costs one line and it removes an argument from every future code review.

Rule 6: prove isolation on every build

The only isolation you can trust is the kind something actively tries to break. This test belongs in CI, not in a quarterly review.

test('tenant B cannot read tenant A rows by primary key', async () => {
  const a = await seedTenant('A');
  const b = await seedTenant('B');

  const row = await asTenant(a, (tx) =>
    tx.one('INSERT INTO employees (tenant_id, name) VALUES ($1, $2) RETURNING id',
           [a, 'Ada']));

  const leaked = await asTenant(b, (tx) =>
    tx.oneOrNone('SELECT * FROM employees WHERE id = $1', [row.id]));

  expect(leaked).toBeNull();          // not "throws", not "403" — no row
});

test('every tenant-scoped table has RLS enabled and forced', async () => {
  const bad = await db.many(`
    SELECT c.relname
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'app'
      AND c.relkind = 'r'
      AND EXISTS (SELECT 1 FROM information_schema.columns
                  WHERE table_schema = 'app'
                    AND table_name = c.relname
                    AND column_name = 'tenant_id')
      AND (c.relrowsecurity = false OR c.relforcerowsecurity = false)`);

  expect(bad).toEqual([]);            // catches the table added last sprint
});

The second test is the one that pays for itself. Nobody forgets isolation on the table they are thinking about. They forget it on the table they added at 6pm on a Friday, and no amount of code review reliably catches a missing ALTER TABLE.

The part that is not about queries at all

Two operational facts tend to arrive late and expensively.

Migrations fan out. With a database per tenant, a schema change is not one migration, it is N of them, and it will fail somewhere in the middle. An all-or-nothing migration across hundreds of databases is neither atomic nor finishable, so the runner has to be resumable, idempotent, and able to report which tenants are on which version. Build that before you need it.

One tenant can stall another. A large export, an unbounded report, a runaway join — in a shared deployment these consume shared resources, and the tenant who did nothing wrong is the one who files the ticket. Reporting queries belong on a read replica, long jobs belong in a queue with per-tenant concurrency limits, and bulk imports should be idempotent and reversible because somebody will upload the same file twice.

A checklist before shipping a tenant-scoped feature

1. Tenant resolved once, from the authenticated session only.
2. No function anywhere accepts a tenant id as a parameter from the caller.
3. New tables with a tenant_id have ENABLE and FORCE ROW LEVEL SECURITY.
4. Every policy has both USING and WITH CHECK.
5. current_setting('app.tenant_id', true) — the true is present.
6. Tenant is set with SET LOCAL / set_config(..., true), inside the transaction.
7. The runtime role is not a superuser, has no BYPASSRLS, and owns no tables.
8. A cross-tenant read test runs in CI and fails the build.
9. A catalog test asserts RLS on every tenant-scoped table.
10. Background jobs open an explicit tenant context per tenant.
11. Reporting runs on a replica; bulk imports are idempotent.

None of this is clever. That is the point. Isolation should be the boring part of the system, enforced in three places that would all have to fail together, and checked by something that runs whether anyone remembers to think about it or not.

We are the engineering team at INNERLUXES. We build multi-tenant platforms and integrate them with existing enterprise systems, which is where most of the above was learned the slow way.