Lock down RPC: revoke EXECUTE from public roles

What “Lock down RPC: revoke EXECUTE from public roles” solves (and when it matters)

Prevents publicly executable Postgres functions from being called directly from the browser, which can bypass intended application logic.

Use this when you have admin-like functions, exports, backfills, billing actions, or any RPC that returns sensitive data.

Before you apply “Lock down RPC: revoke EXECUTE from public roles” (safety prerequisites)

• List every place your frontend currently calls Supabase directly for this surface (table, Storage, or RPC).
• Decide the backend endpoint(s) you will add so the app still works after you revoke client access.
• Confirm which environment you’re changing (dev/staging/prod) and plan to repeat the same verification in each.
• If this is production: plan a staged rollout (feature flag, canary) so you can roll back app behavior quickly without reopening exposure.

Download / copy‑paste instructions

Run the signature-discovery query first, then apply the REVOKE/GRANT SQL for the exact schema + args. Update your app so only server code calls the function.

Template variations (choose the safest one that fits)

Lock down one function signature

When to use: When you have a single function and want the minimal change.

-- Discover the signature
SELECT n.nspname AS schema,
       p.proname AS function,
       pg_get_function_identity_arguments(p.oid) AS args
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'admin_export';

-- Replace schema + args below
REVOKE EXECUTE ON FUNCTION public.admin_export(args) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION public.admin_export(args) FROM anon;
REVOKE EXECUTE ON FUNCTION public.admin_export(args) FROM authenticated;
GRANT EXECUTE ON FUNCTION public.admin_export(args) TO service_role;

Lock down all overloaded variants

When to use: When the function name is overloaded and you need to catch every signature.

-- Query all overloads by name and schema, then apply REVOKE/GRANT per signature
-- Do not assume one change covers all variants

Implementation steps (practical)

1. Inventory your RPC functions and identify which ones are sensitive.
2. Discover exact function signatures using pg_proc + pg_namespace.
3. Revoke EXECUTE from PUBLIC, anon, and authenticated for each signature.
4. Grant EXECUTE to service_role only (or verify it already has access).
5. Replace any client-side supabase.rpc calls with a backend endpoint.
6. Add input validation, authorization, and rate limiting to that endpoint.
7. Re-test from anon/auth: the function call should fail directly.

Verification checklist for “Lock down RPC: revoke EXECUTE from public roles” (make sure the fix is real)

1. Run the signature query and confirm you covered all overloads.
2. Attempt to call the function via client credentials: it should fail.
3. Call the function from server code using service_role: it should succeed.
4. Confirm the backend endpoint enforces authorization checks.
5. Re-scan and ensure RPC exposure issues disappear.
6. Review logs for denied function calls after the change.
7. Document the rule: browser never calls RPC directly.

Common pitfalls (what breaks in real projects)

• Applying SQL but leaving the frontend calling Supabase directly (the exposure remains).
• Locking down one surface (tables) but forgetting Storage/RPC exposure.
• Fixing in dev but not re-checking staging/prod (config drift).
• Not adding verification steps, so regressions ship unnoticed.
• Treating service_role like a convenience key instead of a server-only secret.

Rollback plan for “Lock down RPC: revoke EXECUTE from public roles” (without reopening exposure)

If the app breaks after you lock down direct access, the safest rollback is to restore application behavior (backend endpoints) — not to re‑grant direct client access.

A practical sequence:

1. Restore or hotfix the backend endpoint to return the needed data for authorized users.
2. Add temporary logging for denied direct access so you can spot missing code paths.
3. Only if absolutely necessary: create a narrowly scoped, time-limited policy for a specific table/bucket/function and remove it after the backend path is fixed.

Related glossary terms

• RPC EXECUTE Grants → /glossary/rpc-execute-grants
• Service Role Key → /glossary/service-role-key

Related integrations

• Supabase RPC with service_role only → /integrations/supabase-rpc-service-role