[Studio] Functions can be shown as revoked while remaining executable through PUBLIC

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Studio's Exposed functions state can report a function as revoked even though anon can still execute it through PostgreSQL's PUBLIC EXECUTE privilege.

I reproduced the mismatch end-to-end with PostgREST:

Studio access state:
  anon_execute = false
  status = revoked

PostgreSQL effective privilege:
  has_function_privilege('anon', function_oid, 'EXECUTE') = true

PostgREST:
  POST /rest/v1/rpc/<function>
  → HTTP 200
  → current_user = anon

So the access state shown by Studio can disagree with the privilege PostgreSQL/PostgREST actually enforces.

To Reproduce

This reproduces with the current default-privilege behavior used by Studio.

1. Create a function without an explicit ACL:

create function public.studio_privilege_repro()
returns json
language sql
as $$
  select json_build_object(
    'ok', true,
    'current_user', current_user
  );
$$;

1. Check the effective privilege:

select has_function_privilege(
  'anon',
  'public.studio_privilege_repro()',
  'EXECUTE'
);

This returns true. proacl is NULL, so PostgreSQL falls back to its built-in function ACL, which grants EXECUTE to PUBLIC.

1. Check Studio's Exposed functions state for the same function. The query reports:

anon_execute = false
auth_execute = false
srv_execute = false
status = revoked

1. Call the function through PostgREST as the anonymous role:

POST /rest/v1/rpc/studio_privilege_repro

Result:

HTTP 200

{
  "ok": true,
  "current_user": "anon"
}

The RPC also appears in PostgREST's OpenAPI output.

Expected behavior

Studio's Exposed functions state should reflect whether the Data API roles can effectively execute a function.

If anon can invoke the function through the Data API, Studio should not classify it as revoked for anon.

Screenshots

Not applicable. This was reproduced from the Studio SQL used by Exposed functions plus a PostgREST RPC, not from a Dashboard screenshot.

System information

• OS: Linux (WSL2)
• Browser (if applies): N/A (not reproduced through the Studio UI)
• Version of supabase-js: N/A
• Version of Node.js: N/A
• PostgreSQL: 16.15
• PostgREST: 16.1
• Supabase: current master

Additional context

Studio derives function access from the explicit ACL using roughly:

aclexplode(...)
→ grantee
→ pg_roles

PostgreSQL represents the PUBLIC ACL entry with grantee OID 0, and PUBLIC is not a row in pg_roles.

As a result, access inherited through PUBLIC is not attributed to anon, authenticated, or service_role, even though PostgreSQL considers those roles able to execute the function.

For comparison:

select has_function_privilege(
  'anon',
  function_oid,
  'EXECUTE'
);

does account for effective privileges, including access through PUBLIC.

After explicitly revoking execution from PUBLIC:

revoke execute
on function public.studio_privilege_repro()
from public, anon, authenticated;

the result changes to:

has_function_privilege = false
proacl = {postgres=X/postgres}

POST /rest/v1/rpc/studio_privilege_repro
→ HTTP 401
→ 42501: permission denied for function studio_privilege_repro

So the successful request above is caused by the effective PostgreSQL privilege, not by PostgREST bypassing function permissions.

The underlying PostgreSQL default-privilege behavior is already discussed elsewhere:

• #43884 — unexpected default EXECUTE privileges on functions
• #49338 — ineffective schema-scoped REVOKE EXECUTE ... FROM PUBLIC in the hardening docs
• #49366 — docs fix; also notes that Studio generates the same schema-scoped revoke but intentionally leaves that product behavior out of scope
• Discussion #45329 — broader discussion around automatic Data API privileges

This issue is intentionally narrower.

It is not proposing whether Supabase should globally revoke PostgreSQL's built-in PUBLIC EXECUTE privilege.

The issue here is that Studio reports the function as revoked / not exposed while the Data API role can effectively execute it.

No production or existing project database was used.