Roles assigned to each user POSTGRES

Question

i'm trying to make a query that shows the users and all the roles that they have, i already know how to ask about the roles of one particular user:

 SELECT oid, rolname FROM pg_roles WHERE
 pg_has_role( 'name_of_user', oid, 'member');

Any idea how to do it?

score 3 · Accepted Answer · answered Jan 31 '17 at 19:39

USERS are themselves ROLES that can login. CREATE USER is actually just an alias. What you probably want is some kind of query that psql issues with \du as a starting point. You can see what psql issues with -E,

********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
ORDER BY 1;
**************************

score 1 · Answer 2 · answered Jan 31 '17 at 20:24

Okay, i found the way to do it, if anyone is interested:

SELECT r.rolname as "Users",    ARRAY(SELECT b.rolname
     FROM pg_catalog.pg_auth_members m
     JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
     WHERE m.member = r.oid) as "Assigned roles"
FROM pg_catalog.pg_roles r
ORDER BY 1;

Roles assigned to each user POSTGRES

2 Answers2