Listing every RLS policy: pg_policies, column by column

The query

pg_policies is the readability view over the pg_policy catalog. Ordered by table it doubles as documentation for the access model you actually shipped:

SELECT schemaname, tablename, policyname, cmd, permissive, roles, qual, with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

What each column tells you

cmd names the command the policy governs — SELECT, INSERT, UPDATE, DELETE or ALL. roles lists the roles it applies to, rendered as an array; a policy targeting service_role is decoration, because that role carries BYPASSRLS regardless. permissive separates the normal case from RESTRICTIVE policies, which narrow rather than grant. qual is the USING expression as text — the filter on rows you can see or change — and with_check is the clause validating rows you try to write. On ALL and UPDATE policies, read both: Postgres falls back to qual when with_check is absent, which is convenient until read and write conditions were meant to differ.

One reading habit makes the listing productive: for each table, state aloud who should touch it and confirm a policy exists for that role and command. The gaps — the table everyone can apparently read but no policy mentions, the write command nobody constrained — are the audit; the rows that look right rarely teach anything.

Three patterns worth hunting

First, conditions that are constant true: the policy exists, the flag is green, and nothing is filtered. Second, write commands with an empty with_check, which accept inserts and updates attributed to anyone. The text search below catches the obvious cases — read the output rather than counting rows, because it also matches expressions that merely contain the word:

SELECT tablename, policyname, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'public'
  AND (qual ILIKE '%true%' OR with_check ILIKE '%true%'
       OR (cmd IN ('INSERT', 'UPDATE', 'ALL') AND with_check IS NULL));

Policies on tables with RLS switched off

Policies can exist on a table whose relrowsecurity flag is false, where they are consulted by no one — decoration left behind when someone toggled RLS off or created the table without enabling it. Joining pg_policies to pg_tables exposes the mismatch:

SELECT p.schemaname, p.tablename, p.policyname, p.cmd
FROM pg_policies p
JOIN pg_tables t
  ON t.schemaname = p.schemaname AND t.tablename = p.tablename
WHERE p.schemaname = 'public'
  AND t.rowsecurity = false;

Diffing the output over time

Saved next to your migrations, this output becomes a changelog of intent versus reality; compared between weeks, it shows policies appearing, vanishing or being rewritten — the texture of drift before it becomes an incident. Stating scope plainly: RowShield does not automate your diff ritual, though its scheduled scans snapshot deparsed policies on every run and alert on transitions between them, which is the same comparison with the remembering outsourced. RowShield reads pg_catalog metadata only, never rows, and is an independent product not affiliated with or endorsed by Supabase. Run a free audit at rowshield.dev/audit to start.

Frequently asked

Where are policies stored in Postgres?

In the pg_policy catalog. pg_policies is the readability view over it, rendering roles and condition expressions as text so the query above needs no joins into system tables.

Why do I see policies on a table I thought was unprotected?

Policies can exist while RLS is disabled; they are consulted only when the flag is on. The join query above finds exactly that inert state, which reads as protection in reviews while guarding nothing.

Does the view include system schemas?

It lists every schema, which is why the examples filter to public. Platform schemas such as auth and storage manage their own policies, and mixing them into your audit mostly manufactures noise.