SQL query to list group role along with its members

posted Apr 28, 2017, 8:12 AM by Sachchida Ojha
There is a table in the gp_toolkit schema that list this information.
sachi=#  
sachi=# \d gp_toolkit.gp_roles_assigned
View "gp_toolkit.gp_roles_assigned"
    Column    | Type | Modifiers 
--------------+------+-----------
 raroleid     | oid  | 
 rarolename   | name | 
 ramemberid   | oid  | 
 ramembername | name | 
View definition:
 SELECT pgr.oid AS raroleid, pgr.rolname AS rarolename, pgam.member AS ramemberid, pgr2.rolname AS ramembername
   FROM pg_roles pgr
   LEFT JOIN pg_auth_members pgam ON pgr.oid = pgam.roleid
   LEFT JOIN pg_roles pgr2 ON pgam.member = pgr2.oid;
sachi=# 
sachi=# select * from gp_toolkit.gp_roles_assigned;
raroleid | rarolename | ramemberid | ramembername 
----------+--------------+------------+--------------
88044 | admin | 16994 | sachi
10 | gpadmin | | 
96279 | gpmon | | 
16994 | sachi | | 
33477 | gpuser | | 
96285 | tev_bdasvc | | 
96286 | ccuser | 96287 | tom
96286 | ccuser | 96287 | scott

(10 rows)


sachi=#
Comments