SaaS architecture
Debugging row-level security in PostgreSQL
Why does a query under row-level security return no rows, or all of them? Five checks and two catalog queries, tested in PostgreSQL 17, with the output.
Row-level security fails quietly. A policy that does not match returns no rows instead of an error; a role that bypasses the policy returns all of them; an update that the policy filters reports UPDATE 0. None of these is an error, so with default logging nothing in the logs says which of them happened. Debugging it means asking the database five questions, in order, and one of them turns the silence into an error message.
We set up the usual mistakes on purpose — a forced and an unforced table, a policy for only some commands, a policy for the wrong role, a WITH CHECK (true) — and ran each check against them. The most useful one is a single setting: with row_security = off, a query on a table where row-level security applies to you fails with query would be affected by row-level security policy for table "invoices" instead of quietly returning fewer rows.
The setup
PostgreSQL 17.10 in a throwaway container, measured on 4 October 2026. Tables owned by owner_role, queried by the application role app, which has SELECT, INSERT, UPDATE and DELETE on all of them, and by member, a login role that is a member of owner_role. Every policy uses the tenant from a session variable, tenant_id = nullif(current_setting('app.tenant', true), '')::int.
invoices: RLS enabled and forced, one policy for all commands.notes: RLS enabled, not forced, policies forSELECTandINSERTonly.docs: RLS enabled, one policy withWITH CHECK (true).pages: RLS enabled, one policyTO web— a role the application does not log in as.rlimit: RLS enabled, only aRESTRICTIVEpolicy forSELECT.tagsandfiles: atenant_idcolumn and no RLS at all.
Check 1: who am I, and do I bypass the policy?
select current_user, rolsuper, rolbypassrls
from pg_roles where rolname = current_user;
For app this returned f, f; for the postgres superuser t, t. A superuser or a role with BYPASSRLS sees every row of every tenant, and so does the table’s owner unless the table has FORCE ROW LEVEL SECURITY. The query above does not show that last case, and “owner” includes members of the owning role: member also returned f, f, and still counted all 15 rows of notes. This lists the tables where the current role counts as the owner and the policy is not forced:
select relname from pg_class
where relkind in ('r', 'p') and relrowsecurity and not relforcerowsecurity
and pg_has_role(current_user, relowner, 'USAGE');
For member it returned docs, notes, pages and rlimit; for app, nothing. If “all tenants” is your symptom, this is the first answer; the owner case is the first gap in row-level security in PostgreSQL: where tenants leak. The same holds one step removed for views, which read their tables with the rights of the view’s owner — see PostgreSQL views and row-level security.
Check 2: is the tenant actually set here?
select current_setting('app.tenant', true);
Run it in the same transaction as the query that returns nothing. A setting made with set_config(…, true) or SET LOCAL is gone after the transaction, and a pool in transaction mode may run your query in a different session than the one where you set it — with no tenant, or with someone else’s; that case is measured in PgBouncer and row-level security. An empty string and NULL both make the policy above match nothing; which of the two you get depends on whether the setting was ever made in that session.
Check 3: does a policy cover this command and this role?
This query lists, for every table with a tenant_id column, whether RLS is on and forced, which commands have a permissive policy, whether there are restrictive policies, whether any WITH CHECK is true, and each policy with its command and roles:
select c.oid::regclass as table_name,
c.relrowsecurity as rls,
c.relforcerowsecurity as forced,
coalesce(bool_or(p.polcmd in ('r', '*') and p.polpermissive), false) as sel,
coalesce(bool_or(p.polcmd in ('a', '*') and p.polpermissive), false) as ins,
coalesce(bool_or(p.polcmd in ('w', '*') and p.polpermissive), false) as upd,
coalesce(bool_or(p.polcmd in ('d', '*') and p.polpermissive), false) as del,
coalesce(bool_or(not p.polpermissive), false) as restrictive,
coalesce(bool_or(pg_get_expr(p.polwithcheck, p.polrelid) = 'true'), false) as check_true,
string_agg(case p.polcmd when 'r' then 'select' when 'a' then 'insert'
when 'w' then 'update' when 'd' then 'delete' else 'all' end
|| ' to ' ||
(select string_agg(case when r = 0 then 'public'
else pg_get_userbyid(r)::text end, '+')
from unnest(p.polroles) r),
', ' order by p.polname) as policies
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_policy p on p.polrelid = c.oid
where c.relkind in ('r', 'p')
and n.nspname not in ('pg_catalog', 'information_schema')
and exists (select from pg_attribute a
where a.attrelid = c.oid and a.attname = 'tenant_id' and not a.attisdropped)
group by c.oid, c.relrowsecurity, c.relforcerowsecurity
order by 1;
On our test database:
| table_name | rls | forced | sel | ins | upd | del | restrictive | check_true | policies |
|---|---|---|---|---|---|---|---|---|---|
| invoices | t | t | t | t | t | t | f | f | all to public |
| notes | t | f | t | t | f | f | f | f | insert to public, select to public |
| tags | f | f | f | f | f | f | f | f | |
| docs | t | f | t | t | t | t | f | t | all to public |
| files | f | f | f | f | f | f | f | f | |
| pages | t | f | t | t | t | t | f | f | all to web |
| rlimit | t | f | f | f | f | f | t | f | select to public |
Every bold value is a bug we planted, and each did what the catalog predicts:
noteshas no update or delete policy: as tenant 2,UPDATEandDELETEof its own row reportedUPDATE 0andDELETE 0, no error.docshasWITH CHECK (true): tenant 2 inserted a row for tenant 3.tagsandfileshave a tenant column and no protection at all.pageshas a policy only for the roleweb: the application, logged in asappwith tenant 2 set, counted 0 of its 2 rows. For a role without an applicable policy the table is default-deny.rlimithas only a restrictive policy:appcounted 0 of 2 rows. A restrictive policy can only narrow what a permissive one allows; on its own it allows nothing. That is whyselcounts permissive policies only.
What a missing command policy and WITH CHECK (true) do to every kind of write is measured in USING vs WITH CHECK on writes.
Check 4: make the silence an error
set row_security = off;
select count(*) from invoices;
For the application role this gave:
ERROR: query would be affected by row-level security policy for table "invoices"
The PostgreSQL documentation describes the setting as controlling “whether to raise an error in lieu of applying a row security policy”. It never switches a policy off: for a role that row-level security applies to, the query fails instead. It failed even with WHERE tenant_id = 2, where the policy would not have removed a single row, so it tells you that RLS applies, not that it filtered. An UPDATE on notes, which has no update policy at all, failed the same way, and so did pages, where no policy applies to app — default-deny counts. A query on tags, which has no RLS, returned its 3 rows normally. In a join the error names only one table (notes join invoices reported notes), so test tables one at a time. For the table owner it depends on FORCE: on the unforced notes the owner counted all 15 rows, on the forced invoices it got the same error with a hint:
HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
The setting “has no effect on roles which bypass every row security policy”, so for a superuser or a BYPASSRLS role it changes nothing — which is check 1 again. pg_dump turns it off by default, and in our earlier test a dump by an application role failed with exactly this error instead of quietly producing a partial backup; more on that in pg_dump of one tenant under row-level security.
Check 5: look at the plan
EXPLAIN shows the policy as part of the query. As app, a query for invoice 3 of the current tenant used the primary key with the policy folded in:
Index Cond: ((tenant_id = (NULLIF(current_setting('app.tenant'::text, true), ''::text))::integer) AND (id = 3))
As a superuser, the same query had only Index Cond: (id = 3) — no policy, because superusers bypass it. If the policy expression is missing from the plan of a role that should be subject to it, you are looking at a bypass (check 1) or a table without RLS (check 3). A plan with One-Time Filter: false and no table scan at all — what app got for pages — means no policy applies to your role. Whether the policy also costs index use is in row-level security performance in PostgreSQL.
Does disabling RLS remove the policies?
No. After ALTER TABLE invoices DISABLE ROW LEVEL SECURITY the application without any tenant set counted all 15 rows, but the policy was still in pg_policy. After ENABLE again it counted 0, and FORCE was still set. The documentation: “policies can exist for a table even if row-level security is disabled. In this case, the policies will not be applied and the policies will be ignored.” That makes DISABLE a quick way to test whether RLS is the cause — and a dangerous thing to leave in a migration, because the policies in the schema still look correct.
What we did not measure
Restrictive policies combined with permissive ones, policies that read a membership table, partitioned tables (the query also lists each partition, with its own RLS setting, which only matters when a partition is queried directly), and the pg_policies view, which shows the same information with the roles as names. The test used a handful of rows; the checks depend on the catalog, not on the size of the tables.
This belongs to the decisions that are cheap early and expensive later; the rest of that list is in multi-tenant SaaS on PostgreSQL: six decisions.
Sources: the PostgreSQL 17 documentation on client connection defaults (row_security), ALTER TABLE (DISABLE ROW LEVEL SECURITY) and CREATE POLICY (roles, default PUBLIC), read on 4 October 2026. All measurements are our own, on PostgreSQL 17.10, on 4 October 2026.