Row-level security leaks when the connection is pooled

Why the bug survives

The test everybody writes is this one, and it passes:

expect(read(pool, "acme").every((r) => r.tenant === "acme")).toBe(true);
expect(read(pool, "globex").every((r) => r.tenant === "globex")).toBe(true);

Two tenants, correct both times. Isolation proven. What it exercises is the case where somebody always sets the tenant — and every request does. The things that do not are the sweeper, the webhook handler, the cron.

The two failures

They are the same mistake, and which one you get depends on which connection the pool hands you.

A used connection leaks the previous tenant:

read(pool, "acme");            // a normal request
read(pool, "acme");            // another
const leak = read(pool, null); // the job — sets nothing

expect(leak.every((r) => r.tenant === "acme")).toBe(true);

It got the connection back as the last request left it. Nothing threw, nothing logged, and the policy was never bypassed — it was applied, to the wrong tenant.

A fresh connection leaks all of them:

read(pool, "globex");          // this took connection #1
const rows = read(pool, null); // this takes connection #2, which nobody has set

expect(rows).toHaveLength(3);  // both tenants

The policy compares against nothing, so it excludes nothing.

That is why it reproduces once and then hides. From where you are standing it is not deterministic: it depends on pool size, on traffic, on who happened to be before you.

The fix is where, not what

const before = conn.current;
try {
  if (tenant === null) throw new NoTenantInContext();
  conn.current = tenant;
  return conn.select();
} finally {
  conn.current = before;
}

Three properties, and the third is the one people skip:

1. Set it inside the same unit of work as the query. Not once per request, not on checkout — a connection can be handed to something else between those two moments.
2. Fail closed. No tenant, no query. A job with nothing to set does not get "everything" and does not get "the last one"; it gets stopped, with a message naming what is missing.
3. Restore it after. The property is not "we set it correctly" — it is that nothing is inherited, so forgetting becomes loud instead of silent.

And it costs the ordinary case nothing: normal traffic reads exactly what it read before.

About the pool in this repository

It is a stand-in, and it is faithful to the one thing that matters: a session variable belongs to the connection, not to your request, and the pool hands the same connection to the next caller with whatever you left on it. Everything runs with no database and no credentials.

Against real PostgreSQL the shape is identical — SET/set_config on a pooled connection, a policy reading current_setting('app.current_tenant') — and the code under test does not change. What changes is select(), which becomes a query, and conn.current = …, which becomes set_config(..., true) inside the transaction.

One real-world detail worth carrying over: use the transaction-local form. The session-level form is exactly the version that survives into the next borrower, which is the bug on this page.

And a second: the table owner bypasses its own policies unless the table is set to force them. A test that connects as the owner passes while proving nothing — which is the same failure as this repository, one level up, wearing a lab coat.

Where this comes from

niiko is multi-tenant with row-level security on every table, and the workers run outside any request. The rule there is written as a hard one — the tenant is applied per unit of work, transaction local, fail closed — because of exactly this.

License

Apache-2.0 — see LICENSE. This is a demonstration, not a package. Copy what you need.

---

Built by Vorluno — a software studio from Panamá.