Auditing a Supabase project in one afternoon

You don't need tooling to know where your Supabase security stands - you need four hours and the right questions in the right order. This is the complete manual audit: every catalog query, how to read each result, and the outside probes that turn findings into proof.

RowShield exists because this audit is worth automating and monitoring. But the manual version matters for different reasons: it teaches you what the automation watches, it works today with zero setup, and when a scan does flag something, understanding the query behind the finding means understanding the fix. This article is the audit we run by hand — scheduled as an afternoon, structured so each hour's output feeds the next.

Everything below reads or probes your own project using your own access. Every SQL statement runs against current Postgres; all were executed during this article's preparation. By the end you'll have a findings document with evidence attached — not a vibe about your security, a list with proof per line, sorted by what deserves fixing first.

The afternoon plan

Bring to the session: your project's SQL editor access, the public anon key, two test accounts if the app has authentication (create them through signup if not), and a document open for findings. Nothing else — no special tooling, no credentials beyond what you already hold.

The order matters more than the clock. Inventory before reading prevents anchoring on whatever the app's UI shows; structural reading before probing tells you which probes matter most; probes last because they confirm rather than explore. Teams that start with probing often stop at the first scary result and never learn what else the catalog was trying to tell them.

A word on scope before starting: this audit covers authorization posture — what's reachable, by whom, under what rules — rather than code vulnerabilities, dependency patching, or infrastructure hardening. Those belong to other checklists. The authorization layer earns its own audit because it changes on every deploy and fails in ways nothing else surfaces; treat this afternoon as the recurring core, with other security reviews layered around it at their own cadences.

Hour one: inventory the protection surface

Open the SQL editor and run five queries. First, tables and their RLS flags:

select c.relname,
       c.relrowsecurity as rls_enabled,
       c.relforcerowsecurity as force_rls
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r'
order by c.relname;

Second, every policy in full:

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

Third, grants — which client-facing roles can do what:

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

Fourth, privilege-elevating functions:

select p.proname, p.proconfig
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.prosecdef;

Fifth, views and their invocation semantics:

select c.relname, c.reloptions
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'v';

If the project uses Storage, add its buckets and object policies to the pile (storage.buckets, then pg_policies filtered to schemaname = 'storage'). Export all of it — copy to a document, literally. Hour two reads from this artifact, and hour four's write-up cites it as evidence.

To make the reading concrete, here is the first query's output from the fabricated example project used across RowShield articles, annotated the way you should annotate your own:

      relname      | rls_enabled | force_rls
-------------------+-------------+-----------
 documents         | t           | f
 notifications     | f           | f      <-- FINDING: open table
 workspace_members | t           | f
 workspaces        | t           | f

Every column earns its keep. rls_enabled = false means the API serves that table to anyone holding your public key — no policy discussion needed, exposure exists today. force_rls = false is normal (owner access for migrations) but worth knowing per table: it names which tables would still bypass policies if someone connects as the owner. Annotate every anomaly inline as you go; memory doesn't survive to hour two.

Hour two: read what you found

The inventory answers six questions, each mapped to a verdict:

Reading technique for the policy dump: group rows mentally by (tablename, cmd) and read each group as one OR expression, since permissive policies combine by union. A group whose members disagree about scope isn't contradictory — it's as wide as its widest member. Note any identity comparison that references something other than (select auth.uid()): request parameters and client-shaped columns don't count as verified identity.

A worked mini-read shows the method. Suppose some project's dump contains, for one table:

policyname            | permissive | cmd    | qual                          | with_check
----------------------+------------+--------+-------------------------------+-----------
invoices_select_owner | PERMISSIVE | SELECT | uid() = owner_id              |
invoices_select_any   | PERMISSIVE | SELECT | true                          |
invoices_insert_own   | PERMISSIVE | INSERT |                               | false

Three lines, three verdicts. The any tautology makes the owner policy redundant for reads — every authenticated user matches every row already, so the careful clause contributes nothing (and its presence may mislead reviewers into thinking reads are scoped). The insert policy's check of literal false means inserts always fail — likely a debugging leftover that broke a feature someone "fixed" elsewhere, worth investigating before deleting. Two findings from three rows, both with evidence attached, neither requiring any judgment beyond the reading rules above.

Mark each finding high/medium/low as you go — high for anything exposing data now (open tables, unconstrained writes), medium for structure that will misbehave under pressure (tautologies, unhygienic definer functions), low for hygiene (naming, comments, bare auth calls). Severity triage during reading beats severity debate later.

Also record the absence findings while reading: tables with zero policies despite being enabled, buckets with no policies at all, views nobody can explain. Absence findings age differently from defect findings — they're often intentional interim states that quietly became permanent — and they're precisely what a fresh reader catches that the schema's author no longer sees.

Hour three: probe from outside

Structural findings predict exposure; probes prove it. Two batteries, both framed entirely within your own project.

The anonymous sweep. With only your public key, GET every exposed table:

curl -s "https://YOUR-PROJECT.supabase.co/rest/v1/TABLE_NAME" \
  -H "apikey: $ANON_KEY"

Empty array: pass. Rows: a finding, already half-documented from hour two. Also try one insert attempt per sensitive table expecting rejection — anonymous writes are rarer than reads but strictly worse. If your model includes deliberate public reads (published listings and the like), verify their filters instead: draft rows must stay absent, internal columns must stay out of responses.

Interpreting responses is mechanical once you've seen each shape:

Two practical notes. First, keep requests modest — a handful of rows per table proves exposure without bulk-downloading anything, which matters both ethically and for your own logs. Second, run probes against production, not staging: staging answers whether your pipeline produces protection, production answers whether protection currently exists, and they differ more often than teams expect.

The two-account battery. With test accounts Alice and Bob, run the five adversarial probes — anonymous read, forged write, cross-tenant read, ownership transfer, silent delete — exactly as specified in the tenant-isolation playbook. Each probe has a binary expected outcome; record actual outcomes beside expectations. Where hour two predicted defects, these probes supply behavioral proof; where probes fail unexpectedly, hour two's inventory explains why. The two halves of the audit corroborate each other, and disagreements between them are themselves findings — they mean something mediates access outside the obvious path.

Budget roughly twenty minutes per sensitive table for the full battery, less once practiced. Prioritize tables by what a breach of them means — user credentials and personal data outrank configuration tables — so if the afternoon compresses, the most important tables already ran their probes. The battery is also the audit's most transferable artifact: the same five requests run against any future project, unchanged.

Hour four: storage, functions, keys

Three remaining surfaces, each with its own quick check.

Buckets: list them with visibility flags. Public buckets get reviewed as published content — is everything inside genuinely meant to be URL-accessible? Private buckets get their own policy review, path-scoping patterns especially (the own-folder pattern and its neighbors). Check for the orphan case too: buckets with no policies at all, which behave as write-voids — safe from anonymous access but unusable by legitimate users, usually signaling an abandoned feature.

Functions: for each definer function from hour one, call it as a restricted user and compare results to what that user could derive through policies alone. Pin search_path wherever missing. Confirm execute privileges match intent.

Keys: grep built assets and environment files for service-grade material, then fetch your deployed site's JavaScript and search it too:

grep -r "sb_secret_" dist/ .next/static/ build/ 2>/dev/null
grep -ri "service_role" .env* --include="*" 2>/dev/null

Key material anywhere browser-reachable converts this afternoon into an incident-response morning — rotate first, investigate after (rotation guidance).

Then spend the last thirty minutes writing. Per finding: what's exposed, catalog or probe evidence, proposed fix as concrete SQL, affected features for QA awareness. Sort high-to-low. That document is the audit's deliverable — and next quarter's baseline. It is also the step most easily dropped once the scary finding is fixed, which is how a repeatable program degrades back into an ad-hoc scramble with nothing to compare against.

Where manual ends and monitoring begins

An afternoon audit is a snapshot; snapshots age. Migrations ship weekly, dashboards invite hotfixes, restores replace state silently — every channel that makes audits necessary also makes them stale. Re-running this whole sequence monthly is realistic; after every deploy is not, which is precisely the gap automation fills.

The mapping between this article's steps and continuous coverage is direct: hour one's inventory becomes CI assertions; hour three's probes become scheduled external scans; hour four's key checks become build-time scanning. RowShield automates exactly those translations — the free scan covers the outside-probe layer immediately, catalog monitoring extends it — while the judgment calls (is this public bucket intentional? should editors see drafts?) remain yours regardless of tooling.

Run this audit manually once and you'll understand every automated finding forever after. That's not a consolation prize for lacking tooling — it's the reason the tooling's findings deserve trust. And when the next audit comes around, compare against the last one's document: deltas between snapshots are the drift narrative in miniature, each line either a deliberate change you can name or a finding that arrived uninvited.

Common questions

Is one afternoon really enough?

For small-to-medium projects — say, up to forty tables — yes, comfortably: most time goes to reading and writing up, not querying. Larger schemas split naturally across days by schema area, with the same per-area flow. What doesn't fit in an afternoon is fixing; this produces the prioritized list, and fixes schedule from there.

Do I need production access, or is staging enough?

Both, ideally — but production is where truth lives. Staging validates upcoming changes; production holds the accumulated drift of every manual edit and restore since. If forced to choose one, audit production first; staging diverges from it in ways that matter less than the reverse.

What if I find something alarming mid-afternoon?

Handle by class. Exposure-that-is-happening-now (open tables, leaked keys): pause the audit, contain, resume — the containment patterns are short and this document's earlier sections link them. Everything else waits for the write-up; alarming-but-contained findings lose nothing from a day's delay in fixing.

How do I audit a project I can't run locally?

Everything in hours one and two needs only SQL editor access to the live project — read-only catalog queries, safe on production. Hour three's probes hit public endpoints by design. Only fixes require the usual deploy discipline; an audit itself never modifies state, which is worth stating explicitly when requesting access to someone else's project.

Should the audit include checking my auth configuration too?

Yes as a fifth quarter-hour: confirm email confirmation requirements, redirect allowlists, and whether unused OAuth providers are disabled. This article scopes to the database and surfaces because that's RowShield's home turf, but an afternoon audit that touches keys might as well confirm the identity layer's basic posture — most of it is reading settings screens.

---

Prefer the automated starting point? Run the free scan — paste your app URL for the outside-probe layer of this audit instantly, then bring the results to your manual session.

RowShield is an independent product and is not affiliated with, endorsed by, or sponsored by Supabase, Inc.