DEV Community: André Wenceslau

Postgres RLS multi-tenancy: two leaks that survive correct policies

André Wenceslau — Wed, 19 Aug 2026 01:03:55 +0000

I wrote an earlier post
about two traps that silently switch Postgres row level security off:
connecting as a role that is exempt from policies, and a transaction-local
GUC that reverts to an empty string on a pooled connection.

Both of those are failures of the policy layer. You fix them and the policies
start doing their job.

This post is about the harder category: two ways data crosses the tenant
boundary while every policy is working exactly as written. Both were
raised by a reader named Rahul S in the comments on that post, both survive a
correct two-role split, and neither goes through the read path — which is why
you will not catch them by testing SELECT.

Assume you have done everything right. The app connects as a role that owns
nothing and holds neither superuser nor BYPASSRLS. Every tenant table has
ENABLE and FORCE ROW LEVEL SECURITY. Every policy has USING and
WITH CHECK, both wrapped in NULLIF(current_setting(...), '').

Here is what still gets out.

Leak 1: `SECURITY DEFINER` hands the exemption straight back

A SECURITY DEFINER function runs with the privileges of the function's
owner, not the caller's. That is the entire point of the feature, and it
is useful. It is also the role exemption from the first post, arriving through
a door that does not look like a database connection at all.

Who owns your functions? Whoever ran the migration that created them. Which is
your migration role. Which is usually a superuser.

Superusers are unconditionally exempt from row security, and FORCE does not
contain them — FORCE only removes the owner's exemption. So inside that
function body, RLS is simply off.

Consider a helper that looks completely harmless:

CREATE FUNCTION document_count() RETURNS bigint
  LANGUAGE sql
  SECURITY DEFINER
AS $$ SELECT count(*) FROM documents $$;

Called by your properly contained application role, against the seed data in
the repo below — two documents belonging to Acme, one to Globex:

  as app_user · org context = Acme · documents has FORCE = true
  document_count() is owned by postgres (superuser = true)

      SELECT count(*) FROM documents   2  Acme's only, filtered
      SELECT document_count()          3  every tenant's

Same session. Same role. Same policies. The direct query is filtered and the
function is not, because the function body executes as its owner.

Read the two lines above the result, because together they are the whole
condition. documents has FORCE ROW LEVEL SECURITY enabled — and it does
not help. FORCE removes the owner's exemption; this function's owner is a
superuser, and nothing removes that one.

The corollary is worth having: if your tables are owned by a non-superuser
role and you use FORCE, a SECURITY DEFINER function owned by that role
stays contained. Whether this is a footnote or a breach comes down entirely to
who owns the function.

This is worse than it looks, for two reasons.

It does not look like data access. The obvious version of this bug is a
function that returns rows. The realistic version is an audit trigger, a
updated_at maintenance function, a search helper, an RPC endpoint someone
exposed through PostgREST. Nobody reviewing "add an audit trigger" is thinking
about tenant isolation, and the function does not appear anywhere near your
connection configuration.

It survives every test in the first post. Your role is contained. Your
startup assertion passes. SELECT is filtered. The isolation suite is green.
The exemption is inside a function that the suite never calls.

Finding them

Ask the catalog rather than grepping migrations, because migrations lie about
what is actually in the database:

SELECT n.nspname AS schema,
       p.proname AS function,
       pg_get_userbyid(p.proowner) AS owner
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.prosecdef
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

For each row, one of three things has to be true: it does not touch
tenant-scoped tables, it filters by organization itself, or it does not need
to be SECURITY DEFINER at all. SECURITY INVOKER is the default and is
almost always what you want.

If a function genuinely needs elevated privileges, give it an owner that is
not a superuser — a role that owns only what the function needs, with
FORCE on the tables so ownership alone does not exempt it.

And regardless: always pin the search path.

CREATE FUNCTION log_access(doc_id uuid) RETURNS void
  LANGUAGE plpgsql
  SECURITY DEFINER
  SET search_path = public, pg_temp   -- not optional
AS $$ ... $$;

Without it, a caller who can create objects in a schema earlier on the search
path can shadow a table or operator the function references, and have your
elevated function execute their definition. That is a separate escalation path
that happens to live on the same feature.

Leak 2: constraints are an oracle, by design

This one is not a mistake in your setup. It is documented behaviour, and it is
load-bearing for the database's correctness. From the Postgres documentation
on row security:

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.

Read that again with a tenant boundary in mind. A unique index cannot enforce
uniqueness if it can only see the rows you are allowed to read. So it sees all
of them. So it reports collisions with rows you cannot read.

Take a schema that looks fine:

CREATE TABLE projects (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid NOT NULL REFERENCES organizations(id),
  slug            text NOT NULL UNIQUE      -- the oracle
);

Globex owns the slug project-atlas. Acme goes looking for it, then tries to
use it:

  as app_user · org context = Acme · Globex owns the slug 'project-atlas'

      SELECT ... WHERE slug = 'project-atlas'   0 rows  correctly invisible
      INSERT ... slug = 'project-atlas'
          23505 duplicate key value violates unique constraint "projects_slug_key"
          constraint: projects_slug_key

Both halves are working as designed. The SELECT returns zero rows — the
policy is doing its job, and Acme genuinely cannot read that row. Then the
unique index, which is not subject to the policy, reports the collision.

Acme now knows with certainty that some other tenant has a project called
project-atlas. The read path is filtered perfectly. The row leaked through
the error.

And it is enumerable. Walk a wordlist, and you map another tenant's project
names one 23505 at a time. Do it against a stripe_customer_id column and you
confirm whether a specific company is a customer. Do it against an invite
email and you learn who works where.

Foreign keys leak in the other direction: a reference to a row you cannot read
resolves successfully, so a successful insert tells you a row exists. Same
oracle, inverted.

Closing it where you can

Where the value only has to be unique per tenant — and this is most values —
put the tenant in the constraint:

UNIQUE (organization_id, slug)    -- not UNIQUE (slug)

Swapping the constraint on the same table, with the same data and the same
policy, and repeating the exact insert that just failed:

  after UNIQUE (organization_id, slug) replaces UNIQUE (slug):
      INSERT succeeded — no information crossed the boundary

The collision now happens only against rows in the caller's own tenant, which
they are allowed to know about. Globex still has its project-atlas and Acme
still cannot see it — but Acme can now have one too, and learns nothing by
asking. The channel closes completely, and it costs one column in an index.

This is the entire fix, and it has to be a habit rather than an audit, because
retrofitting it means backfilling values that are currently colliding across
tenants.

Where you cannot

Some values genuinely have to be globally unique: a subdomain, a public
username, an external system's customer id. Schema design cannot help you
there, and I want to be straight about that rather than pretend otherwise.

What you can do is narrow the channel:

Never return the constraint error verbatim. Catch 23505 and reply with something that does not distinguish "taken by you" from "taken by someone else" — for a subdomain, "that subdomain is not available" is honest and says less than the raw error.
Rate-limit the endpoint that produces the check. An oracle you can query three times a minute is a very different threat from one you can query three thousand.
Decide whether it is actually a secret. Subdomains are usually public by design; a Stripe customer id is not. The mitigation should follow the sensitivity, not the mechanism.

None of that closes it. It is a real, accepted limit of doing multi-tenancy in
a shared schema, and it is the strongest argument I know for schema-per-tenant
when your uniqueness requirements are genuinely global.

What this changes about testing

The pattern shared by both leaks is that the read path is fine. A test
suite that asserts "tenant A cannot select tenant B's rows" passes in both
cases, because in both cases tenant A genuinely cannot select them.

So the assertions have to target the side channels directly:

call every SECURITY DEFINER function as the contained app role, with a tenant context set, and assert it returns only that tenant's data
insert a colliding value across a tenant boundary and assert you get a clean application-level failure rather than a raw constraint violation
assert the catalog itself: that no SECURITY DEFINER function outside an allowlist is owned by a superuser

That last one is the same move as refusing to boot on an exempt role — turning
a thing you got right once into a thing that cannot silently stop being right.

Runnable

Both of these are a command in the MIT repo, so you do not have to take any of
the output above on trust:

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 role exemption from the first post
npm run side-channels    # every block of output in this post
npm test                 # nine assertions against a live server

npm run side-channels is where the two outputs above come from. It builds
the SECURITY DEFINER function and the globally-unique constraint, shows them
leaking, swaps the constraint to prove the fix closes it, and drops everything
it made — the schema in sql/ models the correct pattern, so the
anti-patterns are built at runtime rather than shipped in the reference
schema for someone to copy.

The memberships table there carries UNIQUE (organization_id, user_id) with
a comment explaining why the organization_id is in the constraint. That is
the entire lesson from leak 2, sitting where someone copying the schema will
actually read it.

The open question

I do not have a good answer for the globally-unique case, and I have not found
one written down. Deferring the constraint does not help — the check still
runs, just later. A mediating table with its own policy moves the oracle
rather than removing it, since the mediating table needs the global unique.

If you have solved this without going schema-per-tenant, I would like to hear
how. It is the one part of this pattern where I know the answer is "you
cannot", and I would be glad to be wrong.

Both traps in this post came from Rahul S, in the comments on the previous
one. Neither was in the first version of my repo. Both survive a setup that is
otherwise completely correct, which is exactly the kind of gap you do not find
by staring at your own work.

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.

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

André Wenceslau — Sat, 25 Jul 2026 21:02:34 +0000

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: