Supabase’s powerful PostgREST layer lets developers expose PostgreSQL functions as HTTP endpoints, but a hidden default can turn those endpoints into open doors for attackers.
A SECURITY DEFINER RPC function in Supabase is defined as a database function that executes with the privileges of its owner rather than the caller.
Understanding Supabase RPC and Default Privileges
When you create a function in PostgreSQL, the system automatically grants EXECUTE to the PUBLIC role. In Supabase, the anon (unauthenticated) and authenticated roles inherit this grant through the PostgREST layer, meaning any client can call the function unless you explicitly revoke the permission.
The Danger of Leaving PUBLIC EXECUTE on SECURITY DEFINER
A function marked SECURITY DEFINER runs with the rights of its owner—often the postgres superuser. If the function remains executable by PUBLIC, an anonymous request can gain owner‑level access, potentially exposing or modifying sensitive data.
According to a scan of 100 random Supabase projects, 12% of SECURITY DEFINER functions were callable by the anonymous role, contributing to 22% of overall data leaks in those projects.
How to Properly Revoke PUBLIC Access
Follow these steps to close the gap:
- Identify all SECURITY DEFINER functions:
SELECT proname FROM pg_proc WHERE prosecdef = true; - Revoke EXECUTE from PUBLIC, anon, and authenticated roles:
REVOKE EXECUTE ON FUNCTION your_func() FROM PUBLIC, anon, authenticated; - Grant explicit permissions only to trusted roles or service accounts.
- Set default privileges for future functions so they do not inherit PUBLIC EXECUTE:
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
Best Practices for Secure RPC Design
Implement these safeguards to reduce risk:
- Least‑privilege grants: Only grant EXECUTE to roles that truly need it.
- Input validation: Sanitize all parameters inside the function to prevent SQL injection.
- Role separation: Use dedicated service roles instead of the default postgres owner for SECURITY DEFINER functions.
- Audit regularly: Periodically run queries to list functions with PUBLIC EXECUTE and verify their purpose.
Frequently Asked Questions
What is the difference between SECURITY DEFINER and SECURITY INVOKER?
SECURITY DEFINER runs with the privileges of the function’s owner, while SECURITY INVOKER (the default) runs with the caller’s privileges, making the latter safer for most use cases.
Can revoking PUBLIC alone fix the issue?
Revoking from PUBLIC is essential, but you must also revoke from anon and authenticated because Supabase grants identical default privileges to both roles.
How does Supabase’s default role mapping affect security?
Both anon and authenticated start with the same default‑privilege grants, so a function left executable by PUBLIC becomes reachable by every signed‑up user as well as unauthenticated callers.
Should I use SECURITY DEFINER at all?
Use it only when a function truly requires elevated privileges, and always combine it with strict REVOKE statements and role‑based access controls.
How can I audit my existing functions for exposure?
Run a query like SELECT n.nspname, p.proname FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE prosecdef = true AND has_function_privilege(p.oid, 'EXECUTE') = true; to list all SECURITY DEFINER functions that are still executable by PUBLIC.
Neptune Infotech can help you audit and harden your Supabase backend, ensuring your applications stay secure while scaling efficiently.
