5 · The afternoon audit

The question this chapter answers: if I had one afternoon and nothing but SQL, how would I find every hole in my own project?

This is the complete manual method — no tools beyond a SQL connection, worded for auditing your own project. It takes an afternoon on a modest schema and it is the same method automation repeats on every scan; doing it by hand once is what makes you able to judge any tool's output afterward.

Ground rules

Run against a development copy or a fresh snapshot if you have one; otherwise run as-is — every query below is read-only against the catalog. Do not fix while you audit. Fixing mid-audit loses the second finding behind the first. Write everything down, then remediate in one reviewed migration.

Step 1: inventory the tables and their RLS state

select n.nspname as schema,
       c.relname as table_name,
       c.relrowsecurity as rls_enabled,
       c.relforcerowsecurity as force_rls
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind in ('r', 'p')      -- ordinary and partitioned tables
  and n.nspname = 'public'
order by c.relname;

Read the result in three groups:

• rls_enabled = false — open. Reachable by any role holding a grant, which on many projects includes anon and authenticated by default (Supabase docs). Each row here is Pattern 1 from Chapter 4.
• rls_enabled = true, no policies (Step 2 will confirm) — locked. Usually a bug report waiting, occasionally an intentional internal table.
• rls_enabled = true with policies — the interesting middle: quality of protection still unknown.

If your app uses schemas beyond public, repeat for each; adjust the filter rather than dropping it, because system schemas (auth, storage, realtime, extensions) ship RLS-off tables by design and will bury you in noise.

Step 2: read every policy

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

For each table, ask four questions:

1. Does every command users perform have a policy? A missing update policy means updates fail entirely; a missing insert policy blocks inserts — good or bad depending on intent.
2. Is any condition constant true, or true regardless of row and caller? (qual = 'true' jumps out; so does with_check being null on an insert or all policy.)
3. Do multiple permissive rows share a table and command? Their union is the real rule — derive it explicitly, in writing.
4. Does every policy name its role, or does any default to public?

A useful sprawl detector:

select tablename, cmd,
       count(*) filter (where permissive = 'PERMISSIVE') as permissive_count,
       count(*) filter (where permissive = 'RESTRICTIVE') as restrictive_count
from pg_policies
where schemaname = 'public'
group by tablename, cmd
having count(*) filter (where permissive = 'PERMISSIVE') > 1
order by tablename;

Every row returned is a union someone should be able to state out loud. Rows that nobody can explain are findings.

Step 3: grants — the check before the checks

Policies assume grants are already narrow. Verify:

select table_name, grantee, privilege_type
from information_schema.role_table_grants
where table_schema = 'public'
  and grantee in ('anon', 'authenticated', 'PUBLIC')
order by table_name, grantee, privilege_type;

Compare each table's grants against what your client code actually does. anon holding anything beyond a deliberately public read is a finding. authenticated holding delete on a table your app never deletes from is surface area. And remember Chapter 1's ordering: a missing grant produces the same 42501 as a hostile policy, so grants also explain "it broke" tickets that get misread as RLS failures.

Step 4: the surfaces around the tables

Three quick queries close the gap between "tables audited" and "project audited":

-- Views: owner-rights unless security_invoker is set (Postgres 15+).
select c.relname as view_name, c.reloptions
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'v' and n.nspname = 'public';

-- Functions exposed through the API surface.
select p.proname,
       p.prosecdef as security_definer,
       coalesce(p.proconfig::text, '(default search_path)') as config
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public'
order by p.proname;

-- Storage buckets and their visibility.
select id, ("public") as is_public
from storage.buckets
order by id;

A view without security_invoker = true runs with its creator's privileges and can hand over rows its underlying policies withhold (Supabase docs). Every security_definer = true function deserves one line in your notes: why definer, is the search path pinned, who can execute it. Every bucket needs the same sentence about visibility.

The leak signatures, condensed

By now the patterns have names. When you find one, you know its fix class:

Reading results honestly

Two disciplines separate a real audit from a ritual. First, the catalog describes configuration, not behavior — Chapter 6's proofs exist because text can look right and behave wrong. Second, absence of findings is not proof of safety: a query run with the wrong schema filter, or against a stale copy, reports peace of mind it has not earned. When you finish, you should be able to say precisely which queries you ran, against what, when. That sentence is the difference between "we checked" and "we checked it in a way that would have caught this."

What one afternoon cannot do

This audit is a photograph. It was true at 2 p.m.; the next migration changes the negative. Run it after significant schema work, before launches, and on a calendar cadence you actually keep — and treat anything recurring as evidence that the check belongs in CI (Chapters 6 and 10) or in continuous monitoring rather than in anyone's memory.

Check on your project

1. Save the four catalog queries above into a file named audit.sql in your repository, so the next person inherits the method instead of rediscovering it.
2. Run Steps 1–3 today and record three numbers: open tables, tautological or union-widened policies, tables where grants exceed actual usage.
3. For each finding, write the one-sentence user-facing consequence ("any signed-out visitor could list comments") before writing the fix. Consequences prioritize better than severities.
4. Schedule the rerun: pick the trigger (post-migration, pre-release, monthly) while the afternoon is fresh.