How can I revoke execution of a PostgreSQL function?

[steve-chavez]

All functions access is PUBLIC by default, this means that any role can execute it. To revoke execution, there are 2 steps required:

• Revoke function execution (foo in this case) from PUBLIC:

revoke execute on function foo from public;

• Revoke execution from a particular role (anon in this case):

revoke execute on function foo from anon;

Now anon should get an error when trying to execute the function:

begin;
set local role anon;
select foo();
ERROR:  permission denied for function foo

[babhishek21]

Please note that the role/users like anon and authenticated are custom roles made by Supabase, and are hence different from the role group public.

We noticed that for any new function that we made, postgres would automatically give execute privileges to the following roles:

• public (also shown as empty space in psql)
• anon
• authenticated
• postgres
• service_role

You can verify this behavior by looking up permissions for your function (source: SO answer):

SELECT f.proname AS name,
       f.proargtypes AS signature,
       f.proacl AS permissions
FROM pg_catalog.pg_proc AS f
   JOIN pg_catalog.pg_namespace AS s
      ON f.pronamespace = s.oid
WHERE f.proname = 'myfunction'
  AND s.nspname = 'myschema';

To limit permissions correctly we had to revoke privileges from multiple users, not just public:

revoke execute on function foo from public, anon, authenticated;