Convert a public RPC to service_role-only

From → To (what you’re converting)

From: A Postgres function can be executed by PUBLIC/anon/authenticated via Supabase RPC.

To: Function EXECUTE is restricted and only backend code can call it using service_role.

Safety notes for Convert a public RPC to service_role-only (order of operations)

Conversions are safest when you change application call paths before you revoke access.

1. Add the backend endpoint that will replace the direct Supabase call.
2. Switch the frontend to call your backend endpoint (feature flag if needed).
3. Revoke direct client privileges (grants, bucket access, or RPC execute).
4. Verify: direct access fails; backend endpoint succeeds.

Conversion logic (step-by-step)

1. Inventory functions that perform sensitive reads/writes.
2. Discover all function signatures (including overloads).
3. Revoke EXECUTE from PUBLIC, anon, and authenticated per signature.
4. Grant EXECUTE to service_role (server-only).
5. Replace client supabase.rpc calls with backend endpoints.
6. Add input validation, authorization, and rate limiting server-side.
7. Verify direct client RPC calls fail and backend calls succeed.

Example conversions

Example 1

Input: admin_export RPC is callable by authenticated users.

Output: Direct calls fail; /api/admin/export triggers backend execution only for admins.

Notes: Endpoint audits export requests and limits frequency.

Example 2

Input: A helper function has multiple overloads; one remains callable.

Output: All overloads are revoked; the audit query is added to the checklist.

Notes: Prevents regression in future migrations.

Verification checklist for Convert a public RPC to service_role-only

• Reproduce the risky behavior once before changing anything (so you can prove the fix).
• After the conversion, the same direct access test fails (401/403).
• The user-facing feature still works through backend endpoints.
• A scan/checklist re-run no longer flags the exposure.
• You recorded the rule so future migrations don’t reintroduce direct access.

Related converters (what to do next)

• Convert a public table to backend-only access → /conversions/public-table-to-backend-only — A conversion playbook to remove direct client table access and route everything through a secure backend layer.
• Convert a public Storage bucket to private + signed URLs → /conversions/public-bucket-to-private-signed-urls — A conversion playbook to stop public downloads and move to private buckets with backend-generated signed URLs.

Related templates

• Lock down RPC: revoke EXECUTE from public roles → /templates/rpc-functions/lock-down-rpc-execute

Related glossary terms

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

Common pitfalls during conversions

• Revoking access before the backend endpoint is deployed (causes outages and rollback pressure).
• Leaving one legacy client call path in place (exposure still exists).
• Assuming “authenticated users” are authorized without ownership/tenancy checks.
• Fixing only one environment and forgetting staging/prod (drift).

If you must keep some client access during Convert a public RPC to service_role-only

Sometimes you can’t move everything server-side immediately. If you keep any client access temporarily, make it an explicit, reviewed exception.

• Keep the exception narrow: one operation, one resource, one documented reason.
• Add an expiration date and a follow-up task to remove the exception once the backend path is ready.
• Add extra verification: tests for cross-user/tenant access and direct access attempts that must fail outside the exception.
• Prefer least privilege (specific grants/policies) over broad authenticated access.

This keeps “temporary” from becoming permanent drift.

What to document after the conversion (prevents regressions)

• Which resource you converted (table/bucket/function) and the exact change in access model.
• Which direct access test proves the conversion worked.
• Which backend endpoint now owns the privileged operation and what authorization it enforces.
• Which drift guard you will re-run after migrations.