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

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.

Comments

[Mads Hansen]

Excellent distinction between policy failure and boundary failure. I would add two controls around the constraint oracle: normalize application errors so callers cannot distinguish “occupied by another tenant” from other invalid input, and rate-limit repeated conflicts so the endpoint cannot become an enumeration API. The schema fix is even stronger where semantics allow it: make uniqueness tenant-scoped, e.g. UNIQUE (organization_id, slug), and use composite tenant-aware foreign keys so a child cannot reference a parent in another tenant. I’d test the negative paths explicitly—direct SELECT, SECURITY DEFINER calls, duplicate inserts, and FK probes—because a green read-isolation suite proves surprisingly little here.