Dynamic script to grant all tables and functions for a schema to another user or role

Note: we have listed sample SQLto generate grant statements for tables and functions

-- script will generate grant statement for all tables

select 'grant all on table '||schemaname||'.'||tablename||' to <grantee>;' from pg_tables where schemaname in ('schemaname') order by schemaname, tablename;

-- script will generate grant statement for all functions

select 'grant execute on function '||fschema||'.'||fname||'('||param||')'||' to <grantee>;'
from
(
select distinct fschema,fowner,fname,
--flanguage,proisagg,proargnames,proacl,
array_to_string(array_agg(t.typname) over (partition by fschema,fname),',') as param
from
(
select
proname as fname,nspname as fschema, a.rolname as fowner, l.lanname as flanguage, proisagg, proargnames,proacl,
unnest(proargtypes) as coltype
from pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN pg_catalog.pg_authid a on a.oid=p.proowner
LEFT JOIN pg_catalog.pg_language l on l.oid=p.prolang
where nspname like 'schemaname'
) as a LEFT OUTER JOIN pg_catalog.pg_type t on t.oid=a.coltype
order by fschema,fowner,fname) as b

Comments