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

SELECT * FROM documents WHERE organization_id = $1

Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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: ""

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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.