How to Actually Prove Postgres Row-Level Security Works

A multi-tenant system usually starts with a WHERE clause. Every query filters on tenant_id, every engineer knows to add it, and for a while that's enough. Then the codebase grows past the size where one person reviews every query, a new endpoint ships without the filter, and the failure mode isn't a crash — it's tenant A quietly reading tenant B's rows through an endpoint that returns 200 OK. That's the specific risk that turns a demo into a liability the moment a multi-tenant RAG platform starts serving more than one paying customer, and it's why isolation has to live below the application code a future engineer might get wrong.

An application-layer filter is a convention, not a control

The tenant_id = ? clause is enforced by discipline: every developer, on every query, forever, including the query someone writes late at night to unblock a demo. Discipline doesn't scale, and a missing filter doesn't show up in a diff review as an obvious gap — it shows up as a row that shouldn't be there. Moving isolation into the database means a query that forgets the filter can't return the wrong tenant's data, because the database itself won't hand over rows the current session isn't allowed to see, regardless of what the application code asked for.

A policy keyed to a session variable, not a login

Postgres row-level security policies filter rows against a plain SQL predicate, evaluated on every SELECT, UPDATE, and DELETE, no matter which query path reaches the table. Rather than tie a policy to which database login is connected — impractical when every tenant shares the same connection pool — the predicate reads a session-scoped setting, issued once at the start of each transaction from a value already authenticated upstream: SET LOCAL app.tenant_id to the current tenant's id. The policy itself is just a comparison against that setting, evaluated transparently on every row, whether the query above it remembered to filter or not.

The bleed-guard: an unset session variable must fail closed

SET LOCAL is what makes this safe under connection pooling: it's scoped to the transaction and reverts automatically the moment that transaction ends, so a pooled connection handed to the next tenant's request starts with no inherited value rather than whatever the previous tenant set. But "no inherited value" has to resolve to something specific inside the policy, and this is where an isolation design either fails safe or fails open. Reading the setting with its missing-value flag set returns NULL when it was truly never touched in the session, but some connection-bootstrapping paths instead leave it as an empty string, and a policy that doesn't handle that case might error out — or coerce into something that matches an unintended row. Wrapping the read in nullif against an empty string collapses both the NULL case and the empty-string case to the same NULL, and a comparison against NULL is never true under ordinary SQL three-valued logic. The policy excludes every row. A request that forgot to set the tenant context doesn't get a stranger's document back — it gets nothing, which is a bug the first QA pass will catch, not a breach a customer reports.

The role has to be unable to cheat

None of the policy logic matters if the role running the query owns the table or carries the bypass-RLS attribute — Postgres exempts both from row-level security entirely, on every query, silently. The application's database role is created explicitly as LOGIN NOBYPASSRLS, and migrations run under a separate owning role the application never authenticates as. That split means a future engineer adding a fast path, a reporting query, or an admin script under the application's own credentials can't accidentally reach across tenants no matter what the query says, because the role itself has no bypass to fall back on.

An empty result is not proof — a positive control is

Here's the trap: an isolation test that only checks "tenant A can't see tenant B's row" will happily pass against a completely broken table — a typo'd table name, a query that returns zero rows for every tenant, a join that silently drops everything. Zero rows looks identical whether isolation is working or the query is simply wrong. The fix is to seed known data for two tenants and assert both directions in the same test: under tenant A's session context, tenant A's own row comes back — exactly the row expected, proving the query mechanism actually works — and tenant B's row count is zero. Only the pair together rules out the false pass; the negative assertion alone proves nothing you can act on.

What this buys a multi-tenant RAG platform specifically

The stakes are sharper in retrieval than in a typical CRUD table, because a leak in a RAG system doesn't surface as a wrong number on a dashboard — it surfaces as one tenant's private document appearing, verbatim, in another tenant's answer. Chunk and embedding tables carry the same policy and the same bleed-guard as every other tenant-scoped table, so a retrieval query that omits a filter — easy to do when a query is assembled dynamically from a vector similarity search plus metadata conditions — still can't return another tenant's chunks. The database enforces the boundary the retrieval code is supposed to respect, instead of trusting that every code path remembered to.