SaaS architecture
PostgreSQL row-level security for SaaS: what we measured
Row-level security for a multi-tenant SaaS on PostgreSQL 17, result by result: what leaks, what it costs, how to set the tenant, and where each test is.
Row-level security is the part of a multi-tenant PostgreSQL database that is easiest to switch on and hardest to be sure of. In under four weeks we tested it piece by piece — the policy, the pooler, views, writes, exports, partitions, six frameworks, three error messages and the settings and roles underneath — each time on a database we threw away afterwards, each time writing down what the database actually returned.
This page is the index to those articles. It lists the main question of each article with its result in one line, a route from the symptom you see to the article that explains it, and three things row-level security does not do for you. Nothing here is new; every number is copied from the article it links to, where the setup and the method are.
Is row-level security enough to keep tenants apart in PostgreSQL?
For reading and writing rows, yes, on conditions that we measured one by one. Three decide most cases. The table has FORCE ROW LEVEL SECURITY and the application connects as a role that is not a superuser and has no BYPASSRLS: without FORCE, the role that owns the table saw 2 of 2 rows whatever the policy said. The tenant is set per transaction, with SET LOCAL or set_config(…, true) inside an explicit transaction: after a plain SET behind PgBouncer in transaction mode, the next client, which set no tenant of its own, counted the largest tenant’s 267,023 invoices. And no view on those tables is owned by a role that bypasses the policy: a plain view owned by a BYPASSRLS role returned all three tenants’ rows, with and without FORCE. security_invoker = true takes the owner out of the question; it returned one tenant in every test.
Two more belong on the list: every table with a tenant_id has row-level security and a policy at all, and no policy has WITH CHECK (true) — with it, tenant 2 inserted a row for tenant 3.
It is not a complete answer. Foreign keys and unique constraints are checked without the policy, sequence values are shared between tenants, and a materialized view cannot have a policy at all. Those three are further down.
What did we measure about row-level security?
Every number below is from PostgreSQL 17.10 or 17.11, measured between 18 September and 13 October 2026; the timed tests ran with default settings. Where a row gives timings, they come from one machine and are meant as proportions.
| Question, and where it is written up | What we measured |
|---|---|
| Why is row-level security on and still not working? | Five gaps. The widest: the table owner saw 2 of 2 rows as long as FORCE was off, even with no policy at all |
| What does a policy cost? | Nothing measurable in its simplest form: 11.0 against 11.0 ms to count 267,023 invoices, 0.30 against 0.34 ms for a median tenant’s latest 50 |
| Who bypasses the policy? | A superuser and a BYPASSRLS role always; the owner and every member of the owner role on a table without FORCE. A member of a BYPASSRLS role counted 0 rows until it ran SET ROLE; the owner turned FORCE off again and counted all 30 |
What does current_setting return when the tenant is not set? | NULL in a session that never named the setting, an empty string once it has been set and reset; a direct cast then fails with invalid input syntax for type integer: "", a policy with nullif(…, '') returns 0 rows |
| Does a helper function in the policy matter? | A PL/pgSQL function left VOLATILE took 1,860 to 1,920 ms on every query; declared STABLE, or wrapped in (select …), 0.29 to 0.30 ms for the latest 50 |
| What does a membership subquery in the policy cost? | tenant_id in (select … from memberships …) took 79 to 99 ms on every query, also for a tenant with 534 invoices |
| Why is my index no longer used? | lower(customer_email) = lower($1) went from 0.21 ms to 34–45 ms for the largest tenant, because lower() is not leakproof; a stored generated column brought it back to 0.25 ms |
| Does the tenant leak through PgBouncer? | With SET or set_config(…, false) in transaction mode: yes, the next client, which set no tenant of its own, counted the largest tenant’s 267,023 invoices. With SET LOCAL inside an explicit transaction: 0 rows |
What does SET LOCAL do outside a transaction? | Nothing, apart from the warning SET LOCAL can only be used in transaction blocks; set_config(…, true) does not even warn |
| A role per tenant or a session variable? | A session variable for almost every SaaS. Over a shared login, a session switched to role t5 could SET ROLE t6 and read tenant 6’s 10,000 invoices, and the first SET ROLE in a session took 19 ms with 1,000 roles and 211 ms with 10,000. Logged in as t5 itself it was refused, at the price of a PgBouncer pool per tenant |
| Do views respect the policy? | A view owned by the table owner returned all three tenants until the table had FORCE; a view owned by a BYPASSRLS role returned all three with and without FORCE; security_invoker = true returned one tenant |
USING or WITH CHECK? | With WITH CHECK (true) tenant 2 inserted a row for tenant 3 without an error. With no policy for the command, updates and deletes of its own rows reported UPDATE 0 and DELETE 0, also without an error |
| Why does a query return no rows, or all of them? | Five checks in order. With row_security = off the silence becomes the error query would be affected by row-level security policy for table |
Can pg_dump export one tenant? | Yes, as the application role, with --enable-row-security and the tenant in PGOPTIONS. The dump also carries the shared sequence values, and its default COPY format is refused when the application role restores it; with --rows-per-insert=1000 the largest tenant (60,000 invoices, 240,000 lines) restored in 11.3 s |
| A partition per tenant under RLS? | With the tenant only in the policy, one small query took 2.0 ms with 100 partitions, 77 ms with 1,000 and 241 ms with 3,000; with WHERE tenant_id = … added, 0.23 to 0.27 ms |
What raises new row violates row-level security policy? | Six causes in our tests, and four spellings of the message that mean different things |
What raises permission denied for table? | Not a policy but a missing grant. UPDATE … WHERE and DELETE … WHERE were refused without SELECT; a table the migration role created after the grant was refused until ALTER DEFAULT PRIVILEGES FOR ROLE named that role; a policy with a subquery raised it for a table the query never mentioned |
What raises infinite recursion detected in policy? | A policy that has to read the table it protects. Six ways out: the same count took 0.09 ms with one and 6.6 seconds with another |
The six framework articles answer one question six times; they are in the section on setting the tenant, below.
Which article do you need? Start from the symptom
Row-level security rarely tells you what is wrong: most of its failures look like a valid result. This is the route we would take, from what you see to where it is measured.
What does the statement do that it should not?
- Returns no rows
Does
select current_setting('app.tenant', true), with the name of your own setting and run in the same transaction as the query, return the tenant you set?- NoThe tenant is not set where the query runsIt was never set on this path, it was set outside a transaction, or a commit ended it. See the PgBouncer article, or the article for your framework.
- YesNext check: does a policy cover this command and this role?Check 3 of the debugging article, with the catalog query.
- Returns another tenant's rows
Is the role the table owner or a member of the owning role, on a table without
FORCE; a superuser; or a role withBYPASSRLS?- YesThe policy does not apply to this roleGap 1 of “where tenants leak”.
- NoNext checks: the connection, a view, the tableA session-level setting behind a pool (PgBouncer and framework articles), a view whose owner bypasses the policy (views article), or a table that has no row-level security at all (gap 5 of “where tenants leak”).
- Reports
UPDATE 0orDELETE 0on a row the same request can read, no errorFiltered, not refusedNo policy for the command, or no select policy while theWHEREnames a column: the “USING vs WITH CHECK” article. If the request cannot read the row either, start at “returns no rows”. - Raises an errorThree messages have their own article
new row violates row-level security policy,infinite recursion detected in policy, andquery would be affected by row-level security policy(debugging article). Others we met:invalid input syntax for type uuid: ""(gap 2 of “where tenants leak”),COPY FROM not supported with row-level security(pg_dump article),out of shared memory(partition article). - Is slowLook at how the policy is written, and at the query's own conditionsThe performance article; with a partition per tenant, the partition article; slow for one tenant only, behind a pool with prepared statements, the PgBouncer article.
In the first two branches the order is that of the checks in the debugging article. “Next check” means exactly that: we list what we would look at next, not the only possible cause.
How do you set the tenant so that it does not leak between requests?
Inside one transaction that holds every query of the request, with set_config('app.tenant', …, true) as its first statement — and again after every commit. That kept tenants apart in all six stacks we tested: Go with pgx 5.11, Django 5.2, SQLAlchemy 2.1, Prisma 7, Laravel 13 and Drizzle ORM 0.45. SET LOCAL does the same in plain SQL, which is what the PgBouncer article measured; we did not test it through the frameworks. In Go, a pgx batch with set_config(…, true) as its first statement held as well, in one round trip.
What differed per framework is how the obvious version failed. The six side by side — what leaked, what returned no rows, what committed a failed request and what held — are in one table under gap 3 of where tenants leak. Two results are worth knowing even if your framework is not in that list. A transaction-local setting made outside a transaction returned no rows at all in Django, Laravel, Prisma, Drizzle and Go — the setting ended with the statement that made it; in SQLAlchemy it lasted until the first commit. And in Django and Laravel the framework turns an exception into a response before the middleware sees it, so a wrapper that commits “when nothing was thrown” committed the writes of a failed request.
This keeps one request’s tenant away from the next request. It does not stop SQL that runs on the connection from picking another tenant: with a session variable, and with SET ROLE over a shared login, a statement on that connection switched tenant in our test. That is in a role per tenant vs a session variable.
What does row-level security not protect?
Three things we measured, none of them a bug.
Constraints look past the policy. The PostgreSQL documentation says it directly: “Referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row security”. In our test a line attached to another tenant’s invoice was accepted, and the difference between “succeeded” and “error” tells a tenant that a row exists. What closes it is keys that carry the tenant: unique (tenant_id, id) and a foreign key on (tenant_id, invoice_id). That is gap 4 in where tenants leak.
Sequences are shared. A one-tenant export made with pg_dump --enable-row-security contained only that tenant’s rows, and the current values of the shared sequences — which say how many invoices and lines all tenants have together. Restored into a live database by a superuser, those values also set the counters back; for the application role the setval was refused. Both are in exporting one tenant with pg_dump.
Materialized views have no policy. ENABLE ROW LEVEL SECURITY on a materialized view is refused, and the view returned whatever its owner’s policy let through at the last refresh: all tenants, nothing, or one other tenant’s totals. That is in PostgreSQL views and row-level security.
What we did not measure
Row-level security on PostgreSQL 18, apart from the partition test, which we repeated on 18.3. Poolers other than PgBouncer. Tables larger than memory, and a cold cache. Frameworks other than the six above. And whether you need row-level security at all: the alternative, isolation in the application with tests that inspect the source, is the last section of where tenants leak.
Why the simplest policy only costs nothing when the index starts with tenant_id is in PostgreSQL indexes for multi-tenant SaaS. Row-level security is one of six decisions a multi-tenant product locks into its database; the other five are in multi-tenant SaaS on PostgreSQL: six decisions.
All measurements are our own, on PostgreSQL 17.10 and 17.11, between 18 September and 13 October 2026; versions of PgBouncer, drivers and frameworks are in the article each row links to. The documentation quote is from Row Security Policies, read on 12 October 2026.