PostgreSQL, display role members

Question

Is there a way to visualize which users are mebers of a role in PostgreSQL / pgAdminIII, like

role: council_stuff
members:
    Harry
    Ben
    Steve
    Melinda

Answer 1

To get all members of all roles:

SELECT r.rolname as username,r1.rolname as "role"
FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
JOIN pg_roles r1 ON (m.roleid=r1.oid)                                  
WHERE r.rolcanlogin
ORDER BY 1;

Add r1.rolname='council_stuff' to filter on only that one.

Be aware that users themselves are "roles" in postgresql parlance (there used to be "users and groups" in older versions, but this have been unified into roles).

Here the difference is made between a user and a role with rolcanlogin boolean column, which corresponds o the interpretation that a user is a role that has the LOGIN permission.

The two JOIN can be turned into LEFT JOIN to add the users who aren't member of any role:

SELECT r.rolname as username,r1.rolname as "role"
FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid)                                  
WHERE r.rolcanlogin
ORDER BY 1;