Set privileges for stored functions in bulk

Administrative Snippets

Set privileges for stored functions in bulk

Works with PostgreSQL

8.4

Written in

SQL

Depends on

Nothing

The query below will generate "GRANT EXECUTE" statements for every stored function in the given namespace to a specified role.

By changing the select statement at the bottom it can be used for other statements that require the function name with the parameter list.

SELECT 'GRANT EXECUTE ON FUNCTION ' || oid::regprocedure || ' TO MyTargetRole;'
FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'MyNameSpace');