List all functions that can be executed by a user

Question

I want a complete list of every possible function that a user can execute.

If I connect as the user in psql, I can use \df to get a list of functions in the database. But that does not list functions like MAX(), COUNT(), pg_advisory_lock(), etc. It also lists functions that the user cannot execute in the first place.

Answer 1

Well, the list is quite extensive, but you can achieve this with the following query:

SELECT oid::regproc
FROM pg_proc
WHERE has_function_privilege(oid,'execute');

The has_function_privilege function, with two arguments, check if the function (provided as the first argument) has the privilege (from the second argument) to the current user. You can also use the variant with three arguments and inform the user as the first:

SELECT oid::regproc
FROM pg_proc
WHERE has_function_privilege('your_user_name',oid,'execute');

Of course we can improve the output as psql does (adapted from the psql's query):

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.has_function_privilege(p.oid, 'execute')
ORDER BY 1, 2, 4;