Now-Next

SaaS architecture

Debugging row-level security in PostgreSQL

Row-level security not working in PostgreSQL: why a query returns no rows, or all of them. Five checks and two catalog queries, with the output.

Chris van Eijk, Now-Next · · 7 min read

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.

Why is row-level security not working in PostgreSQL?

It shows one of three symptoms, and each has a short list of causes. All tenants’ rows come back when the role bypasses the policy — a superuser, a role with BYPASSRLS, or the table’s owner or a member of the owning role on a table without FORCE ROW LEVEL SECURITY — or when the table has no row-level security at all. No rows come back when the tenant is not set in the transaction the query runs in, or when no policy covers that command for that role. An update or delete reports UPDATE 0 or DELETE 0 when the table has policies for other commands and none for that one. The five checks below find out which it is, in that order.

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 for SELECT and INSERT only.
  • docs: RLS enabled, one policy with WITH CHECK (true).
  • pages: RLS enabled, one policy TO web — a role the application does not log in as.
  • rlimit: RLS enabled, only a RESTRICTIVE policy for SELECT.
  • tags and files: a tenant_id column 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 — what membership of each kind of role passes on, and what FORCE does and does not stop, is measured in who bypasses row-level security in PostgreSQL; 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_namerlsforcedselinsupddelrestrictivecheck_truepolicies
invoicesttttttffall to public
notestfttffffinsert to public, select to public
tagsffffffff
docstfttttftall to public
filesffffffff
pagestfttttffall to web
rlimittffffftfselect to public

Every bold value is a bug we planted, and each did what the catalog predicts:

  • notes has no update or delete policy: as tenant 2, UPDATE and DELETE of its own row reported UPDATE 0 and DELETE 0, no error.
  • docs has WITH CHECK (true): tenant 2 inserted a row for tenant 3.
  • tags and files have a tenant column and no protection at all.
  • pages has a policy only for the role web: the application, logged in as app with tenant 2 set, counted 0 of its 2 rows. For a role without an applicable policy the table is default-deny.
  • rlimit has only a restrictive policy: app counted 0 of 2 rows. A restrictive policy can only narrow what a permissive one allows; on its own it allows nothing. That is why sel counts 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. When the write does fail, it is with one message for every cause; which cause it is — the tenant, a missing insert or select policy, an upsert, a restrictive policy — is in new row violates row-level security policy: the causes.

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.

What a query answers after set row_security = off, by who runs it

Which role runs the query?

  • The application role

    Does the table have row-level security enabled?

    • YesError: query would be affected by row-level security policy for table "…"Also when the policy would remove no row (WHERE tenant_id = 2), when the command has no policy (UPDATE on notes) and when no policy applies to the role (pages).
    • NoThe rows, as usualtags: 3 rows.
  • The table owner

    Is row-level security enabled and forced on the table?

    • NoEvery rownotes: all 15.
    • YesThe same error, with a hintinvoices: the hint names ALTER TABLE NO FORCE ROW LEVEL SECURITY.
  • A superuser or a BYPASSRLS roleNo effectFrom the documentation: the setting "has no effect on roles which bypass every row security policy".

The error says that row-level security applies to this role on this table, not that it removed rows. In a join it names one table only.

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.

What does “query would be affected by row-level security policy for table” mean?

PostgreSQL raises ERROR: query would be affected by row-level security policy for table "…" when the setting row_security is off and the role that runs the query is subject to row-level security on that table. It does not mean that a policy removed rows: in the test above it came for a query the policy would have left untouched, and for a table where no policy applied to the role at all. It means that row-level security applies to you there, and that with row_security = off PostgreSQL refuses to answer rather than answer with fewer rows.

The place most people meet it is pg_dump, which turns row_security off by default. In our test, a dump run as the application role stopped with this error instead of producing a partial backup. Which way out is right depends on what the dump is for. For one tenant’s rows, run pg_dump --enable-row-security as the application role with the tenant set; what that produces, and what it does without a tenant, is measured in pg_dump of one tenant under row-level security. For a backup of everything, the dump has to run as a role the policies do not apply to. The table’s owner is such a role only while the table is not forced: on a table with FORCE ROW LEVEL SECURITY the owner got the same error, with the hint shown under check 4.

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.

On Supabase the same checks apply, with its own roles on top: see Supabase RLS policy not working? and, for uploads, Supabase Storage RLS.

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.

Let's talk

What are we building?

One conversation is enough to know whether we fit. Tell us what you have in mind — we will tell you how fast it can happen, and whether we are the right people for it.

Start the conversation