How to find out what are the privileges granted to a specific group in redshift

Question

I am using amazon AWS Redshift (8.0.2 version). I have created a group, granted the 2 permissions below, and added one user to that group.

If I check pg_group, I can see the users who are members of this group. But I would also like to see the permissions granted to that group.

For e.g.

redshift=# create group group1;
CREATE GROUP

redshift=# grant select on public.table_mar19_test2 to group group1;
GRANT

redshift=# alter group group1 add user user001;
ALTER GROUP

redshift=# select * from pg_group

    groname    | grosysid |  grolist
---------------+----------+-----------
 group1        |      101 | {148}
 (1 rows)

Is there any sql query to find out the select grant that was given to this group? Multiple grants from multiple databases are granted to one single group and I would like to see which grant is from which database in the cluster.

Answer 1

the below view creates a simplified view of all user and group privs

CREATE OR REPLACE VIEW dict.dba_tab_privs 
AS 
 SELECT derived_table1.schemaname, 
        derived_table1.objectname, 
        derived_table1.usename username,  
        'USER' usertype,
        derived_table1.select_flag, 
        derived_table1.insert_flag, 
        derived_table1.update_flag, 
        derived_table1.delete_flag, 
        derived_table1.reference_flag
   FROM ( SELECT objs.schemaname, objs.objectname, usrs.usename, 
                CASE
                    WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'select'::text) THEN 1
                    ELSE 0
                END AS select_flag, 
                CASE
                    WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'insert'::text) THEN 1
                    ELSE 0
                END AS insert_flag, 
                CASE
                    WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'update'::text) THEN 1
                    ELSE 0
                END AS update_flag, 
                CASE
                    WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'delete'::text) THEN 1
                    ELSE 0
                END AS delete_flag, 
                CASE
                    WHEN has_table_privilege(usrs.usename, objs.fullobj::text, 'references'::text) THEN 1
                    ELSE 0
                END AS reference_flag
           FROM ( SELECT pg_tables.schemaname, 't'::character varying AS obj_type, pg_tables.tablename AS objectname, (pg_tables.schemaname::text + '.'::text + pg_tables.tablename::text)::character varying AS fullobj
                   FROM pg_tables
                  UNION 
                 SELECT pg_views.schemaname, 'v'::character varying AS obj_type, pg_views.viewname AS objectname, (pg_views.schemaname::text + '.'::text + pg_views.viewname::text)::character varying AS fullobj
                   FROM pg_views) objs, 
                   ( 
                      SELECT pg_user.usename 
                      FROM pg_user
                      ) usrs
          ORDER BY objs.fullobj) derived_table1
  WHERE (derived_table1.select_flag + derived_table1.insert_flag + derived_table1.update_flag + derived_table1.delete_flag + derived_table1.reference_flag) > 0
  and schemaname not in ('information_schema','pg_catalog')
union all
select schemname ,
       objectname ,
       username ,
       usertype ,
       CASE WHEN CHARINDEX('r', char_perms ) > 0 THEN 1 else 0 end select_flag,
       CASE WHEN CHARINDEX('a', char_perms ) > 0 THEN 1 else 0 end insert_flag,
       CASE WHEN CHARINDEX('w', char_perms ) > 0 THEN 1 else 0 end update_flag,
       CASE WHEN CHARINDEX('d', char_perms ) > 0 THEN 1 else 0 end delete_flag,
       CASE WHEN CHARINDEX('x', char_perms ) > 0 THEN 1 else 0 end references_flag/*,
       CASE WHEN CHARINDEX('R', char_perms ) > 0 THEN 1 else 0 end rule_flag,
       CASE WHEN CHARINDEX('t', char_perms ) > 0 THEN 1 else 0 end trigger_flag,
       CASE WHEN CHARINDEX('X', char_perms ) > 0 THEN 1 else 0 end execute_flag,
       CASE WHEN CHARINDEX('U', char_perms ) > 0 THEN 1 else 0 end usage_flag,
       CASE WHEN CHARINDEX('C', char_perms ) > 0 THEN 1 else 0 end create_flag,
       CASE WHEN CHARINDEX('T', char_perms ) > 0 THEN 1 else 0 end temporary_flag*/
from
(
    select namespace schemname,
           item objectname,
           groname username,
           'GROUP' usertype,
           SPLIT_PART( SPLIT_PART( ARRAY_TO_STRING( RELACL, '|' ), pu.groname, 2 ) , '/', 1 ) char_perms
    from
    (
    SELECT      use.usename AS subject
                        ,nsp.nspname AS namespace
                        ,cls.relname AS item
                        ,cls.relkind AS type
                        ,use2.usename AS owner
                        ,cls.relacl
            FROM        pg_user     use 
            CROSS JOIN  pg_class    cls
            LEFT JOIN   pg_namespace nsp 
            ON          cls.relnamespace = nsp.oid 
            LEFT JOIN   pg_user      use2 
            ON          cls.relowner = use2.usesysid
            WHERE       cls.relowner = use.usesysid
            AND         nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
            ORDER BY     subject
                        ,namespace
                        ,item ) 
    JOIN    pg_group pu ON array_to_string(relacl, '|') LIKE '%'|| pu.groname ||'%' 
)   

Answer 2

Since grants are per object you need to query permissions of all objects. Here you can see the relacl that lists permissions and the grant statement generated to grant those permissions:

select relacl , 
'grant ' || substring(
            case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',select ' else '' end 
          ||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',update ' else '' end 
          ||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',insert ' else '' end 
          ||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',delete ' else '' end 
          ||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',rule ' else '' end 
          ||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',references ' else '' end 
          ||case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',trigger ' else '' end 
          ||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',execute ' else '' end 
          ||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',usage ' else '' end 
          ||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',create ' else '' end 
          ||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',temporary ' else '' end 
       , 2,10000)
|| ' on '||namespace||'.'||item ||' to "'||pu.groname||'";' as grantsql
from 
(SELECT 
 use.usename as subject, 
 nsp.nspname as namespace, 
 c.relname as item, 
 c.relkind as type, 
 use2.usename as owner, 
 c.relacl 
FROM 
pg_user use 
 cross join pg_class c 
 left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
 left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE 
 c.relowner = use.usesysid  
 and  nsp.nspname   NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
ORDER BY 
 subject,   namespace,   item 
) join pg_group pu on array_to_string(relacl, '|') like '%'||pu.groname||'%' 
where relacl is not null
  and pu.groname='group1'
order by 2