We have migrated to new Google sites!
Post date: Dec 11, 2012 5:6:23 PM
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
;