Set privileges for stored functions in bulk

From PostgreSQL wiki

Revision as of 09:31, 30 March 2011 by Garyknights (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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');
Personal tools