As you probably noticed, Redshift is a pain to extract permissions from. Handling arrays raises all sorts of errors...
Because I am managing the security of Redshift with an automated program, and it relies on both groups and user grants, I need to know how the user got the privilege: through group or custom user grant.
Using all the online resources, I have created a view that shows all of the above. I know this is not a question, but it was the only way I found of sharing the code and "giving back" to the community...
Does anyone have anything to add, improvements to suggest?
SELECT nspname schema_name
, relname table_name
, pg_group.groname user_group
, pg_user.usename username
, case when charindex('U',split_part(split_part(array_to_string(nspacl, '|'),usename,2 ) ,'/',1)) > 0 then 'user'
when charindex('U',split_part(split_part(array_to_string(nspacl, '|'),groname,2 ) ,'/',1)) > 0 then 'group'
end as schema_usage
, case when charindex('C',split_part(split_part(array_to_string(nspacl, '|'),usename,2 ) ,'/',1)) > 0 then 'user'
when charindex('C',split_part(split_part(array_to_string(nspacl, '|'),groname,2 ) ,'/',1)) > 0 then 'group'
end as schema_obj_create
, case
when charindex('r',split_part(split_part(array_to_string(relacl, '|'),usename,2 ) ,'/',1)) > 0 then 'user'
when charindex('r',split_part(split_part(array_to_string(relacl, '|'),groname,2 ) ,'/',1)) > 0 then 'group'
end as table_select
, case
when charindex('w',split_part(split_part(array_to_string(relacl, '|'),usename,2 ) ,'/',1)) > 0 then 'user'
when charindex('W',split_part(split_part(array_to_string(relacl, '|'),groname,2 ) ,'/',1)) > 0 then 'group'
end as table_update
, case
when charindex('a',split_part(split_part(array_to_string(relacl, '|'),usename,2 ) ,'/',1)) > 0 then 'user'
when charindex('a',split_part(split_part(array_to_string(relacl, '|'),groname,2 ) ,'/',1)) > 0 then 'group'
end as table_insert
, case
when charindex('d',split_part(split_part(array_to_string(relacl, '|'),usename,2 ) ,'/',1)) > 0 then 'user'
when charindex('d',split_part(split_part(array_to_string(relacl, '|'),groname,2 ) ,'/',1)) > 0 then 'group'
end as table_delete
, case
when charindex('x',split_part(split_part(array_to_string(relacl, '|'),usename,2 ) ,'/',1)) > 0 then 'user'
when charindex('x',split_part(split_part(array_to_string(relacl, '|'),groname,2 ) ,'/',1)) > 0 then 'group'
end as table_reference
FROM pg_user
left join pg_group
on array_to_string(grolist, '|') like '%'||pg_user.usesysid||'%'
inner join pg_namespace
on array_to_string(nspacl, '|') like '%'||usename||'%'
or array_to_string(nspacl, '|') like '%group '||groname||'%'
left join pg_class AS objs
on relnamespace = pg_namespace.oid
and (array_to_string(relacl, '|') like '%'||usename||'%'
or array_to_string(relacl, '|') like '%group '||groname||'%')
