[Execute to Public]
Postgres RPC Exposure Audit
Most dashboard functions could be called by anyone holding the public key. Measured in production, revoked, and turned into an invariant.
The hub's data goes through Postgres functions called from the browser with a publishable key. Most are SECURITY DEFINER, so they bypass row-level security.
Found it in a merge review, measured it, wrote the fix and the audit query.
Postgres grants EXECUTE on new functions to PUBLIC. A migration that “revoked from anon” did nothing, because anon inherits from PUBLIC — and role checks existed only in the front end.
01 · Measure, don't describe
curl against production with only the publishable key: which functions answer and what they return. Some returned customer contact data.
02 · Revoke with full signatures
A DO block over pg_proc revokes from PUBLIC and anon for every function in the schema, with exact argument lists.
03 · Verify
401 for anonymous calls, no regression for signed-in users.
04 · Grant ≠ authorization
Revokes decide who can call; an internal guard decides what each role sees. The FX setter, which already had a guard, became the template.
05 · Make it permanent
The audit query (SECURITY DEFINER + executable by anon + no internal guard) became a data-health invariant.
what you're seeing
A Postgres function that returns contacts, with toggles for its grants and protections. On the right, what four callers (anonymous with the public key, member, master, owner) get back. Below, the audit query that finds exposed functions.try this
- 1.Click “REVOKE FROM anon” — anonymous still gets 200. That's the PUBLIC trap.
- 2.Now revoke from PUBLIC and anon: anonymous is out, but the member still reads master-only rows.
- 3.Turn on the internal role guard: each role sees only its tier.
real vs simplified
The grant inheritance, SECURITY DEFINER bypassing RLS and the audit logic are real Postgres behavior. The function and its data are fake and masked.! brokeA migration whose name said “without anon” didn't do what it said.
→ changedEvery security migration is verified with an anonymous request, not by reading it.