Now to grant all access for roles on all of the functions in the database, you can use a query such as the following:

SELECT 'GRANT ALL ON FUNCTION ' || quote_ident(n.nspname) || '.' || quote_ident(p.proname) || ' (' || pg_get_function_identity_arguments(p.oid) || ') TO john_doe;'
FROM pg_language AS l
JOIN pg_proc AS p
ON p.prolang = l.oid
JOIN pg_namespace AS n
ON p.pronamespace = n.oid
WHERE l.lanname = 'plpgsql'

After running this query, you will have all of the GRANT statements that are needed to give the user john_doe all access to the functions in the public schema. The real key was using the pg_get_function_identity_arguments() function which is in the pg_catalog schema. I have found that it is definitely a good idea to explore that schema.

Categories: BlogSQL

Leave a Reply

Your email address will not be published. Required fields are marked *