Auditing a Supabase app with its own public key

The anon key ships inside the app bundle. Anyone who downloads the app has it, which means every question that key is permitted to ask is a question the public is permitted to ask. Most developers on this stack know that in the abstract. Far fewer have sat down with the key and asked what it can actually answer.

So this is now a step on the list before any release I ship: extract the key from my own build, point it at the database with no session attached, and walk the whole surface. What follows is the checklist, and the two things it catches most often.

One table without row-level security

The first is a table in the public schema with row-level security never switched on. PostgREST publishes everything in that schema, so an unprotected table is a public endpoint. It is almost never a policy written wrongly. It is a policy never written at all, usually on a table added by an integration rather than by hand: a webhook event log, a sync journal, an audit trail.

Those are exactly the tables nobody thinks of as user data, and exactly the tables that turn out to hold user ids, product ids and raw payloads. Put together, a billing webhook log will happily enumerate who your paying customers are and what they bought.

The query to find them takes one statement, and it is worth running on every project rather than reasoning about which tables might need it. A hole in a table with four rows in it is the same hole it will be at forty thousand, and the number only travels in one direction.

-- every public table with RLS still switched offselect tablename from pg_tables t where schemaname = 'public' and not rowsecurity; -- and every function anon may executeselect p.proname, p.prosecdef  from pg_proc p join pg_namespace n on n.oid = p.pronamespace where n.nspname = 'public'   and has_function_privilege('anon', p.oid, 'execute');

For a table only a webhook writes to, the fix is RLS enabled with no policies at all, which reads like a mistake and is not. The service-role key bypasses RLS entirely, so the writer is unaffected, and no client path should be reading it in the first place. Leave a table comment saying so, aimed squarely at whoever is later tempted to add a client policy to make something work.

A two-way identity map, published as an API

The second is worse and much easier to miss, because it is not a table. Any app with invitations has to resolve an email address to a user id somewhere. That resolution is usually a database function, it is usually SECURITY DEFINER so it can see the auth schema, and EXECUTE on it is usually granted to PUBLIC because that is what the generator does.

PostgREST publishes every public function as an RPC. So do the helpers beside it, the ones written for RLS policies and never intended to be called directly. Chain three or four of those and you have an email address to user id lookup, the reverse, and whatever else those helpers return: billing tier, group membership. Between them, a two-way identity map over an entire user base, answerable without a session.

“A helper written for an RLS policy is internal right up until PostgREST decides it is an API.”

Neither of these is clever, and that is the point. Both are defaults nobody revisited: a table added by an integration before RLS was habit, and a grant that came with a template. Clever vulnerabilities are rare. Defaults are everywhere.

What I would tell anyone on the same stack

Do not audit the code. Audit the surface. Take the anon key out of your own bundle, point it at production, and go through every table and every function asking what it will tell you. It takes an afternoon and it is the only version of this exercise that finds anything, because reading the code tells you what you meant and the key tells you what you shipped.

The second habit is smaller and has caught more: assume every function in the public schema is a public API, because it is. If it should not be, it does not belong in public, and no amount of it being a helper changes that.