create view v_grants as select obj_name, obj_type, grantee, grantor, case when grantee is null then null else case when acc like '%r%' then true else false end end select_priv, case when grantee is null then null else case when acc like '%a%' then true else false end end insert_priv, case when grantee is null then null else case when acc like '%w%' then true else false end end update_priv, case when grantee is null then null else case when acc like '%d%' then true else false end end delete_priv, case when grantee is null then null else case when acc like '%x%' then true else false end end reference_priv, case when grantee is null then null else case when acc like '%t%' then true else false end end trigger_priv, case when grantee is null then null else case when acc like '%X%' then true else false end end execute_priv from ( select obj_name, obj_type, split_part(priv,'=',1) grantee, split_part(split_part(priv,'=',2), '/',2) grantor, split_part(split_part(priv,'=',2), '/',1) acc from ( select n.nspname || '.' || c.relname obj_name, case c.relkind when 'r' then case when relstorage = 'x' then 'external table' else 'table' end when 'v' then 'view' when 'S' then 'sequence' end obj_type, priv from pg_catalog.pg_class c left join (select oid, regexp_split_to_table(array_to_string(relacl, ','), ',') priv from pg_class) privs on privs.oid = c.oid left join pg_catalog.pg_namespace n on n.oid = c.relnamespace where c.relkind in ('r', 'v', 'S') union all select n.nspname || '.' || p.proname obj_name, 'function' obj_type, priv from pg_catalog.pg_proc p left join (select oid, regexp_split_to_table(array_to_string(proacl, ','), ',') priv from pg_proc) privs on privs.oid = p.oid left join pg_catalog.pg_namespace n on n.oid = p.pronamespace ) a ) b ; |
|
|