Postgres RLS multi-tenancy: two traps that silently disable your policies

Most multi-tenant applications keep tenants apart with one line of code,
repeated forever:

SELECT * FROM documents WHERE organization_id = $1

That line is load-bearing. Forget it once — in a new endpoint, in a join, in
a hotfix at 2am, in a report someone added last quarter — and one customer
reads another's data. Nothing crashes. No test fails. You find out from a
support ticket, if you find out at all.

Postgres Row Level Security moves that rule into the database, where
forgetting it is not an option:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
  USING (organization_id = current_setting('app.current_org_id', true)::uuid);

The application sets one session variable per transaction, right after it has
decided which organization the request belongs to:

await client.query("BEGIN");
await client.query("SELECT set_config('app.current_org_id', $1, true)", [orgId]);
// every query in this transaction is now scoped, whether it says so or not

A forgotten WHERE now returns zero rows instead of somebody else's data.
The bug becomes a blank page rather than a breach.

That much is in every RLS tutorial. Below are the two things that decide
whether any of it actually works, both of which I got wrong.

---

Trap 1: your policies do not apply to the role you are probably using

Three kinds of role ignore row level security entirely:

Role	Exempt?
superuser	always — FORCE does not contain it
role with BYPASSRLS	always
the table's owner	under ENABLE; contained by FORCE

Now look at your connection string. In most tutorials, most ORM guides and
most docker-compose.yml files, the application connects with the same role
that created the tables and runs the migrations. That role is usually a
superuser, or at minimum the table owner.

Which means the policies you just wrote do nothing at all. They are in the
schema. They pass code review. They filter nothing.

Here is the same query, with the same policies, over two connections:

$ npm run leak

  as postgres (superuser — always exempt from RLS)
      Acme roadmap
      Acme salaries
      Globex acquisition memo
      3 rows — other tenants included

  as app_user (NOBYPASSRLS, owns nothing)
      Acme roadmap
      Acme salaries
      2 rows — Acme's only

Nothing about the policies changed between those two queries. Exemption is a
property of the role, not of the table.

The fix is a second role. Migrations keep running as the owner; the
application connects as one that owns nothing:

CREATE ROLE app_user LOGIN PASSWORD '...' NOBYPASSRLS;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Add FORCE ROW LEVEL SECURITY to the tables too. It removes the owner's
exemption, which is a useful seatbelt for the day someone points the app at
the wrong connection string. It will not save you from a superuser — nothing
will.

This split is cheap to do on day one and miserable to retrofit once you have
production data and a dozen services connecting.

---

Trap 2: set_config leaves an empty string behind, and pools remember

This one cost me an afternoon.

The symptom was an intermittent 500:

invalid input syntax for type uuid: ""

SQLSTATE 22P02. It only happened on requests that ran without a tenant
context — an organization switcher, a post-login redirect — and only after
some other request had already used that same physical connection. On a fresh
pool it never reproduced. Restarting the app "fixed" it for a while.

The cause is a detail of set_config(name, value, is_local). Passing true
scopes the setting to the current transaction, which is exactly what you want
on a pooled connection. But when that transaction ends, the custom GUC does
not become unset or NULL.

It becomes the empty string.

So the next request to reuse that connection without setting an org context
evaluates:

''::uuid

and raises 22P02. Note that the missing_ok = true second argument to
current_setting does not help here — the setting is not missing. It is
present, and it is empty.

The fix is small:

CREATE POLICY tenant_isolation ON documents
  USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

NULLIF turns '' back into NULL. NULL casts cleanly and matches no
rows, so the failure mode becomes "you see nothing" instead of a crash — and,
much more importantly, instead of any fallback that might show everything.
For an isolation policy, that is the direction you want it to fail in.

Two things worth noting about this bug:

It is invisible to unit tests. It needs a real connection pool against a
real server, and it needs a prior request on the same connection to have
set a context. Any test that mocks the query layer passes happily.

It gets worse under load, not better. More concurrency means more
connection reuse means more chances to hit a recycled connection. It looks
like a flaky bug in staging and a real one in production.

---

What RLS does not do

Worth being explicit, because it is easy to oversell:

• It does not authorize. RLS filters rows once you have set an org context. Deciding whether this user may enter that organization at all is application code, and it has to run before the context is set.
• It is a second line of defense. Keep writing the WHERE clause. The point is that forgetting it stops being catastrophic.
• It costs something. Policies are predicates on every query. Index your organization_id columns and read your plans.
• Bootstrap queries need an exception. Looking up a session, or an invite by its token, happens before you know the tenant. Those go through the owner connection deliberately — a short, named, auditable list.
• WITH CHECK is not optional. A USING clause alone controls reads. Without WITH CHECK, a tenant can write rows into another tenant it cannot read.

---

A runnable version

I extracted the pattern into a small MIT repo while debugging all of the
above — four tables, three SQL files, no framework:

https://github.com/wenceslauAndre/postgres-rls-multi-tenancy

npm install && cp .env.example .env
docker compose up -d
npm run setup
npm run leak    # the two-role output above
npm test        # seven assertions against a live server

The test suite asserts both traps, plus isolation, WITH CHECK containment,
and the pre-context membership read that an org switcher needs. All of them
are properties of the database rather than of application code, which is why
they run against a real server instead of a mock.

If you know whether the revert-to-empty-string behaviour for custom GUCs is
documented explicitly somewhere, I would genuinely like to know — I found it
by bisecting, not by reading.

---

Disclosure: I also sell TenantForge, a multi-tenant
SaaS starter built on this pattern. The repo above is MIT and standalone —
nothing in it is a teaser.

Comments

[Mads Hansen]

Excellent write-up, especially the live-pool test rather than mocked query tests.

One more invariant worth testing is connection binding: BEGIN, set_config(..., true), and every tenant query must run on the same physical connection. Some ORM abstractions make it surprisingly easy to set context through one pool checkout and execute the next query through another.

A useful randomized integration test is:

• reuse one connection after tenant A commits or rolls back;
• run a request with no tenant and assert zero rows;
• reuse it for tenant B and assert only B;
• cancel a transaction mid-request and repeat.

I’d also fail application startup if the runtime role is rolsuper or rolbypassrls, and log current_user plus row_security as deployment evidence. That turns the role split from documentation into an executable invariant.

[André Wenceslau]

The startup check is the best idea in this thread and I'm implementing it.
Asserting the role in a test proves it on my machine; refusing to boot
proves it on the deployment that actually matters. Logging current_user and
row_security as startup evidence is the part I wouldn't have thought of — it
turns "we did the split" into something you can grep for during an incident.

On connection binding: the helper here takes an explicit PoolClient rather
than the pool, specifically so you can't set context on one checkout and
query on another. But you're right that that's a convention enforced by a
type signature, not an invariant, and it evaporates the moment someone wraps
it in an abstraction that hands out a connection per statement.

Worth adding for anyone reading: in this setup the failure mode is
fail-closed. The context is transaction-local and reverts to '', so a query
that escapes the binding returns nothing rather than another tenant's rows.
Anyone using session-level set_config (third arg false) does not get that
rope, and there the leak is real rather than merely confusing.

The A-then-B-on-one-connection assertion is a genuine gap. I test reuse
after a tenant transaction against the no-context case, but never switch
tenants on a live connection and assert containment. Adding it, and the
rollback path too — right now only the WITH CHECK test exercises a rollback,
and only incidentally.

Cancel-mid-transaction I hadn't considered at all. Presumably the GUC is
discarded with the aborted transaction, but "presumably" is the exact word
that earned me the empty-string bug, so I'll go verify rather than assume.

[Rahul S]

Both of these got me too. A third one that's really Trap 1 sneaking back in through a side door: SECURITY DEFINER. The moment a query routes through a definer function or trigger — an audit trigger, a helper RPC, a PostgREST/Supabase rpc — the body executes as the function's owner, and per your own exemption table, if that owner is a superuser or has BYPASSRLS, RLS is simply off inside it. You can do the two-role split perfectly and then hand the exemption right back through a function nobody thinks of as a "connection." Worth grepping the schema for SECURITY DEFINER and checking each one filters org itself, because FORCE won't contain those two roles either.

The other thing RLS structurally can't close is leakage through constraints. A UNIQUE on any globally-scoped column — an email, an external slug, a Stripe customer id — is checked below the policy layer, so a cross-tenant insert that collides comes back as a unique-violation instead of succeeding, and tenant A has just confirmed tenant B already owns that value. The SELECT path is filtered perfectly and the row still leaks through the error. If a value only needs to be unique per tenant, put the tenant in the constraint — UNIQUE (organization_id, email) — otherwise the uniqueness itself is a cross-tenant oracle.

[André Wenceslau]

Both of these are sharper than what's in the post, and the SECURITY DEFINER
one hadn't occurred to me at all. You're right that it's Trap 1 through a
different door: the two-role split secures the connection, and a definer
function quietly reopens it at the function boundary. That's going in the
repo's README.

Worth adding for anyone doing this: if you do need SECURITY DEFINER, pin
SET search_path on it as well, or you've introduced a second escalation
path that has nothing to do with RLS.

On the constraint oracle — I went looking after your comment, and this one
actually is documented, in a single sentence on the RLS page:

Referential integrity checks, such as unique or primary key constraints
and foreign key references, always bypass row security to ensure that
data integrity is maintained.

Which is broader than UNIQUE. Foreign keys are in there too, so an FK
pointing at a tenant-scoped table is the same oracle running the other
direction: the reference resolves against rows the caller can't read, so a
successful insert confirms existence exactly like a violation does.

Your fix is right where it applies — put the tenant in the constraint and
uniqueness stops being global. The awkward case is a value that genuinely
has to be unique across tenants: a Stripe customer id, or an email on a
shared identity table. There the oracle isn't removable by schema design.
Best I have is not surfacing the constraint error verbatim, which narrows
the channel without closing it. If you've got something cleaner I'd take it.

Amusing contrast: that behaviour gets one explicit sentence in the docs,
and the empty-string GUC thing from the post still doesn't seem to be
written down anywhere. Opposite problem.

Both are in the README now, and you're credited by name in it —
they were real gaps, not nitpicks.

[Chizee]

Great write-up. Trap 2 is verbatim the bug I hit building a PostgREST + RLS stack earlier this year, except our GUC was request.jwt.claim.sub. Same intermittent invalid input syntax for type uuid: "", only on pooled connections after a previous request had set a claim, never reproducible on a fresh pool. Same root cause: when the transaction-local value expires, the custom GUC slot falls back to its reset value, and a custom GUC that was never globally SET resets to '' — not NULL. So missing_ok = true can't save you: the setting isn't missing, it's emptied. NULLIF(current_setting(...), '') has been in every policy I've written since.

On your docs question: Trap 1 is documented — PostgreSQL's own Row Security Policies section lists superusers, BYPASSRLS roles, and table owners as exempt, with FORCE covering only the owner. Trap 2, though — the empty-string placeholder persisting for the rest of the session even after a rollback — I never found that written down either. We reproduced it, documented it in our repo's README, and moved on, because it's exactly the kind of thing that eats someone's next afternoon.

Two adds from our testing:

1. Test as the real app role, not your login. Logged in as the table owner, everything looks like it works while filtering nothing — the role exemption makes the policy invisible.
2. USING without WITH CHECK lets a tenant write rows into a tenant it can't read — easy to miss when your test data is all one tenant.

Solid piece, and the runnable repo layout is the right way to teach this.