3 SQL Queries to Audit Your Supabase RLS Policies Using pg_policies

Scheduled to be published simultaneously on Cosoado Lab Blog: https://cosoado-lab.com/blog/pg-policies-rls-audit-query/

TL;DR

You can grasp the entire RLS status of your Supabase project with these three queries.

Query	Purpose	Risk Level
A	List all policies (including USING / WITH CHECK)	—
B	Tables with RLS enabled but 0 policies	🔴 High (data invisible to everyone)
C	Tables with RLS disabled	🟡 Medium (all rows exposed without authentication)

---

Why auditing is necessary

When I was building BoardLink, I added a SELECT policy to the boards table so that "users can only see their own boards." The next day, when I logged in with a different account, the list returned zero results. I could confirm there were rows in the Supabase Dashboard's Table Editor, but the app showed nothing.

It took me 40 minutes to track down the cause: I had mistakenly set to authenticated to to anon in the RLS policy. However, that was when I realized the hard truth: I had no way to look across the table list and all policies at once. As the number of tables grows, it becomes impossible to keep track of which policy is attached to which table. The Dashboard's policy screen only shows them table-by-table, making it unsuitable for a high-level project overview.

You can get the big picture with just one SQL editor window by using the pg_policies view.

Structure of the pg_policies view

According to the official PostgreSQL documentation (53.15. pg_policies), pg_policies contains the following columns:

Column	Type	Content
schemaname	name	Schema name
tablename	name	Table name
policyname	name	Policy name
permissive	text	PERMISSIVE or RESTRICTIVE
roles	name[]	Applicable roles (empty array = PUBLIC)
cmd	text	SELECT / INSERT / UPDATE / DELETE / ALL
qual	text	USING expression (may be NULL)
with_check	text	WITH CHECK expression (may be NULL)

Policies where either qual or with_check is NULL often lead to unintended security holes. I covered this in detail in my other article, The Traps of WITH CHECK and USING.

Query A: List all policies with USING / WITH CHECK expressions

select
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual       as using_expr,
  with_check as with_check_expr
from pg_policies
where schemaname = 'public'
order by tablename, cmd;

I have this registered as a favorite in the Supabase SQL editor. My routine is to run this after adding a policy to ensure that the using_expr is present for rows where cmd = 'UPDATE'. Be careful: if roles is {}, it means the policy applies to PUBLIC (everyone, regardless of authentication).

Query B: Find tables where RLS is enabled but there are 0 policies

This was the mistake I made. You run ALTER TABLE xxx ENABLE ROW LEVEL SECURITY to enable RLS, but forget to add the corresponding policy. By PostgreSQL specification, a table with RLS enabled and 0 policies returns 0 rows to all users except for superusers and those with the BYPASSRLS role. Since there is no error, it is hard to detect.

select t.tablename
from pg_tables t
left join pg_policies p
  on p.schemaname = t.schemaname
  and p.tablename = t.tablename
where t.schemaname = 'public'
  and t.rowsecurity = true
  and p.policyname is null;

pg_tables.rowsecurity has been available since PostgreSQL 9.5. Since Supabase managed instances use 15.x as of 2024, this is fine (Supabase — Row Level Security Documentation).

In my case, the connections table was in this state, and I didn't notice for two days in production until I received an inquiry saying, "I matched, but the list is empty." I should have noticed earlier.

Query C: Find tables where RLS is disabled

select tablename, tableowner
from pg_tables
where schemaname = 'public'
  and rowsecurity = false;

This is a list of tables where all rows can be accessed via the Supabase anon key. While it is fine to intentionally disable RLS on some tables during development, make sure to check this before going to production. Sometimes internal tables used for migration management show up, so it is better to leave a comment for intentional exclusions so you don't get confused later.

Combining all three into a single query

select
  t.tablename,
  t.rowsecurity                        as rls_on,
  count(p.policyname)                  as policy_count,
  array_agg(distinct p.cmd order by p.cmd)
    filter (where p.cmd is not null)   as commands
from pg_tables t
left join pg_policies p
  on p.schemaname = t.schemaname
  and p.tablename = t.tablename
where t.schemaname = 'public'
group by t.tablename, t.rowsecurity
order by t.tablename;

Here is how to interpret the results:

rls_on	policy_count	Meaning
true	0	🔴 Danger: returns 0 rows to everyone
true	1 or more	✅ Normal
false	—	🟡 Review required: all rows exposed without RLS

Pitfall: The SQL Editor bypasses RLS

The Supabase SQL Editor executes as the postgres role. Because the postgres role has the BYPASSRLS attribute, there is a counter-intuitive trap: you might see all rows in the SQL editor, but the app receives zero results.

To verify policy behavior, either use your actual client SDK (such as supabase-js) or test by temporarily switching roles like this:

set local role authenticated;
set local "request.jwt.claim.sub" = '<user-uuid>';
select * from boards;
reset role;

Replace <user-uuid> with the UUID of the user you want to verify (you can look this up in auth.users).

Summary

• Use pg_policies to list all policies across the entire project.
• pg_tables.rowsecurity = true with zero rows in pg_policies → Returns 0 rows to everyone, a trap with no errors.
• Tables with rowsecurity = false have all rows exposed without RLS.
• Since the SQL Editor has BYPASSRLS, always verify behavior via the SDK or by using set local role.

I cover the distinction between USING / WITH CHECK in Supabase RLS: You can hijack other users' data if you omit USING in an UPDATE policy and performance optimization for auth.uid() in Rewriting auth.uid() in one line to avoid calling it every row in Supabase RLS. Together with these three queries, you will have all the major RLS pitfalls covered.

---

SparMate — Martial arts training partner matching
NetaPair — Finding a comedy partner
BoardLink — Finding board game/TRPG friends
Cosoado Lab