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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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;
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.
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;