I want to check the role has been granted to which role. In my working environment, the all the normal is assigned to role group. when i issue dp, it only give me the role group privilege. So I need to check which user is in which user group. THe following is my sql to do that. Is there anybody has a better way to do it. Thanks. Grace
select DISTINCT user, group_name, grantor, admin_option from (select usename AS user,roleid,admin_option from pg_user join pg_auth_members on ( pg_user.usesysid=pg_auth_members.member)) a, (select usename AS group_name,roleid from pg_user join pg_auth_members on (pg_user.usesysid=pg_auth_members.roleid)) b, (select usename AS grantor,roleid from pg_user join pg_auth_members on (pg_user.usesysid=pg_auth_members.grantor)) c
where a.roleid=b.roleid and b.roleid=c.roleid;
member | group_name | grantor | admin_option --------+------------+---------+-------------- user1 | grace | gpadmin | f user_1 | grace | gpadmin | f (2 rows)
