Row-level security in PostgreSQL: where tenants leak

In which isolation model do you choose we end up at shared tables with row-level security: every row carries a tenant_id, and the database itself refuses the rows that do not belong to the current customer. That is the recommendation. This article is about what happens next.

Because turning RLS on takes five lines of SQL, and that is exactly what makes it dangerous. A policy that exists and does nothing looks identical, in a migration, to a policy that works. There is no warning, no slow query, no error — only rows that are visible while you believe they are not.

We walked through the five gaps we keep running into on an empty PostgreSQL 17.11, with two customers and two invoices. Everything below was executed, not reasoned about.

Why does row-level security sometimes not work?

Row-level security does not work when the connection is allowed to bypass the policy, and that happens more often than it looks. The owner of a table bypasses its own policy unless you turn on FORCE ROW LEVEL SECURITY, and a migration that runs as the same database user as the application makes that user the owner. Superusers and roles with BYPASSRLS bypass it always, even with FORCE. On top of that, RLS leaks along three routes that have nothing to do with roles: a session variable that lingers on a connection, a foreign key that looks past the policy, and a table that was added later and never got one.

The setup, and what the database makes of it

This is the whole recipe, on one table:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE  ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
    USING      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid)
    WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

USING decides which rows are visible, WITH CHECK which rows may be written. Give only USING and PostgreSQL applies the same expression as WITH CHECK — usually what you want, but not always, and it is better to write it down than to know it.

The application then sets one variable per connection: set_config('app.tenant_id', '…', false). What happens next is not a filter afterwards. The planner puts the policy expression into the query itself, ahead of your own conditions. On our table of 200,000 invoices, 2,003 of which belong to one customer, SELECT count(*) FROM invoices produced this plan:

Aggregate
  ->  Bitmap Heap Scan on invoices (actual rows=2003)
        Recheck Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id', true), ''))::uuid)
        ->  Bitmap Index Scan on invoices_tenant_idx (actual rows=2003)
              Index Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id', true), ''))::uuid)

The policy expression became an Index Cond. That is the most important thing to know about what RLS costs: with an index on tenant_id the policy is not an extra scan, it is the scan. Without that index it really is a full table scan, because the policy expression comes first and your own WHERE only afterwards. When we added WHERE amount > 900, the policy stayed the Index Cond and our own condition became a Filter after it. The documentation describes exactly this: the policy expression is evaluated for each row “prior to any conditions or functions coming from the user’s query”, with leakproof functions as the only exception.

Gap 1: the table owner

This is the gap that is open most often, and the only one whose consequences are total: no isolation, anywhere.

We enabled RLS on invoices and ran the query as the role that had created the table:

Look at the first row. The documentation says that a table with RLS on and no policy gets a default-deny: “no rows are visible or can be modified”. For the owner that does not hold — the owner simply sees everything. The safety valve you are counting on does not exist for precisely that role.

And the owner is almost always your application. If your migrations run as the same database user as your application code — the default in just about every framework — then that user owns every table it created, and your policy does nothing. At booxx that is not the case, for two reasons: migrations run on a separate connection with DDL rights, the application connects as a role created with NOSUPERUSER NOBYPASSRLS, and all 48 tables carrying a policy have both ENABLE and FORCE.

Check this first. One line per table, and without that line the rest of this article is not even relevant.

Gap 2: the empty context that is not empty

The usual way to write the policy expression is current_setting('app.tenant_id', true). That second argument is missing_ok: without it the function raises an error when the setting does not exist, with it you get NULL. And tenant_id = NULL is not true, so a connection without tenant context sees nothing. That is how it should be: inconvenient, but wrong in the safe direction.

Except that NULL only holds in one of three states. Measured:

Once the setting has existed even once, “empty” is no longer NULL but an empty string. And ''::uuid is not NULL but an error: invalid input syntax for type uuid: "". So a policy with a direct cast works fine until the first transaction that tidily clears its context, and after that it throws a database error on every query against that table. Not a leak, but an outage — and one you rarely meet on a test environment with a single connection.

Hence the nullif(…, '') in the recipe above. With it, all three states returned zero rows. If you store tenant_id as text you can get the same result by explicitly testing for <> '' before comparing; that is what booxx does.

What you should not write in any variant is the inverted form — “if there is no context, show everything”. It reads like a kindness towards background jobs and seeders, and it is the door itself.

Gap 3: the connection that changes tenant

The session variable belongs to a connection, not to a request. That is fine as long as one request has one connection. Put a pooler in between that hands out a connection per transaction — PgBouncer in transaction mode, and most managed “pooler” endpoints — and the backend switches under your feet. Two outcomes, and the second is the serious one: the variable is gone and your queries return zero rows, or the connection still carries the tenant of the previous request and your queries return somebody else’s rows.

This is also exactly why SET LOCAL and a session variable cannot be mixed. set_config(…, true) applies only within the running transaction; set_config(…, false) for the rest of the session. Pooling is only possible if you use the first form and your entire unit of work sits inside one explicit transaction. That is a rebuild of your data layer, not a setting.

Anyone using the second form — by far the most common — therefore has to be able to prove their connection is session-bound, and that is awkward: in a connection string a pooler looks like an ordinary database server. At booxx there is a probe for that, running once per web request and once per background job: set a throwaway variable in one round trip and read it back in a second, separate round trip. If it does not come back, the application refuses to start. That is a few lines of code for the class of failure no test warns you about.

Gap 4: the foreign key that looks past the policy

This gap is in the documentation and rarely quoted: “Referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row security”. It even says what that leads to — “covert channel” leaks.

Here is what that looks like in practice. Customer 1 sees one invoice, id 1; invoice id 2 belongs to customer 2 and is invisible. Then, as customer 1:

Two things at once. The first row is not a leak but corrupt data: there is now a line belonging to customer 1 attached to an invoice of customer 2, and neither of them will ever see it. The second and third rows are a leak: the difference between “succeeded” and “error” tells you whether a row exists that you are not allowed to see. With an incrementing id or an invoice number series, that is enough to count them.

The fix is for the keys themselves to carry the tenant:

-- unique within the tenant, not across tenants
CREATE UNIQUE INDEX invoices_number_per_tenant ON invoices (tenant_id, number);

-- and the reference takes the tenant along
ALTER TABLE invoices      ADD CONSTRAINT invoices_tenant_id_unique UNIQUE (tenant_id, id);
ALTER TABLE invoice_lines ADD FOREIGN KEY (tenant_id, invoice_id)
                          REFERENCES invoices (tenant_id, id);

Measured after that change: attaching the line to customer 2’s invoice now raises a key error, using the same invoice number as customer 2 simply works, and your own invoice with your own line is fine. The difference between “succeeded” and “error” no longer says anything about another customer.

Gap 5: the table that came later

Gaps 1 through 4 are in the design. This one is in time. A table added today and in production tomorrow has no policy unless somebody remembered — and a table without ENABLE ROW LEVEL SECURITY has no protection at all, not even a default-deny.

That is not a matter of discipline but of measurability. This query returns every table with a tenant_id that is missing something:

SELECT c.relname AS table_name,
       c.relrowsecurity      AS rls_enabled,
       c.relforcerowsecurity AS forced,
       count(p.polname)      AS policies
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'tenant_id' AND NOT a.attisdropped
LEFT JOIN pg_policy p ON p.polrelid = c.oid
WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY c.relname, c.relrowsecurity, c.relforcerowsecurity
HAVING NOT c.relrowsecurity OR NOT c.relforcerowsecurity OR count(p.polname) = 0
ORDER BY c.relname;

No rows is good. In our test database it found the two tables we had added on purpose: one without RLS, and one with RLS on but no policy.

Do not put that query in a runbook but in your test suite, and let it derive the table list from the schema instead of from a list somebody maintains. That difference is not theoretical: at booxx the first version of that test listed eleven tables while thirty-eight were under RLS, and none of the ledger and banking tables were among them. A hand-maintained list tells you exactly what you already knew.

How do you prove that it works?

With three tests, and the first is the only one that really matters:

1. Write a row as customer A, switch to customer B, and count. Not through your ORM — deliberately with a raw query that skips the scope, because that is the mistake you are trying to catch. The answer has to be zero.
2. Query with no tenant context. Zero as well, and without a database error — that is gap 2.
3. Let the schema report on itself. The query above, as a test, over every table with a tenant column, with an explicit list of exceptions you have to defend.

The first test is what turns a forgotten scope in application code from a data leak into an empty list. That is the whole promise of RLS, and it is precisely the class of mistakes code review does not catch: a raw query for a report that was too slow, a background job with no signed-in user, an import that goes around the ORM, a relation that walks one step too far. We work those four out in multi-tenant from day one.

When you do not need RLS

RLS is a second lock, not a first one. The scoping in your application stays exactly where it is; this catches what slips through.

And it is not the only way to organise that catch. Our other product, Pilot-Next, runs without RLS: there the isolation lives in the application layer, and the catch is in architecture tests that inspect the source itself — a route may not take its tenant from the request, and a bulk write on a table without its own tenant column has to scope on its relation. That works, and it has a different character: it fails during the build rather than during the query, and it only covers what you thought to test. RLS covers everything that reaches the database, including what you did not think of — and costs you the five gaps above.

So the trade-off is not “better or worse” but “where do you want the mistake to show up”. If money or somebody else’s administration lives in your database, we want the database itself to refuse. For anyone still making that choice, the wider overview is in which isolation model do you choose.

---

Want to know whether the policy in your database is actually on? The audit query from gap 5 and the owner table from gap 1 together take ten minutes. If something comes out of that you did not expect, or if the isolation still lives entirely in the application code of a product that grew beyond the plan — send us an email. We build SaaS products where this is on from the first migration.

Measured on 18-09-2026 in PostgreSQL 17.11, on an empty database with two customers. The documentation quoted: Row Security Policies and Configuration Settings Functions, both read on 18-09-2026.