Postgres list all roles with permission to execute a function

Question

I would like to list my functions & stored procedures along with the roles that are able to execute them. I can get similar information on tables via

SELECT grantee
    , CONCAT(table_schema, '.', table_name) schema_table
    , CASE WHEN COUNT(privilege_type) = 7 THEN 'ALL' ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ') END granted_privileges
FROM information_schema.role_table_grants
GROUP BY table_name, table_schema, grantee
ORDER BY grantee, table_schema, table_name;

I would like the equivalent of this for functions & stored procedures. I am using Postgres v15. Thanks for your help

Laurenz Albe · Accepted Answer · 2024-01-31 07:33:48Z

You'll have to query the system catalog pg_proc directly:

SELECT p.oid::regprocedure AS function_or_procedure,
       a.privilege_type,
       CASE WHEN a.grantee = 0 THEN 'PUBLIC'::text
            ELSE a.grantee::regrole::text
       END AS grantee,
       a.is_grantable
FROM pg_proc AS p
   CROSS JOIN LATERAL aclexplode(
                         coalesce(
                            p.proacl,
                            '{=X/postgres}'::aclitem[]
                         )
                      ) AS a;

The query is taking into account that the default privileges are "execute for everybody".

Stack Exchange Network

Postgres list all roles with permission to execute a function

1 Answer 1

Your Answer

Hot Network Questions

Postgres list all roles with permission to execute a function

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions