audit-labs/audit-tools

 1-- References:
 2--           : https://www.postgresql.org/docs/current/user-manag.html
 3--           : https://www.postgresql.org/docs/current/view-pg-roles.html
 4--           : https://www.postgresql.org/docs/current/catalog-pg-auth-members.html
 5
 6SELECT 
 7    r.rolname AS role_name,
 8    r.rolsuper AS is_superuser,
 9    r.rolinherit AS inherits_privileges,
10    r.rolcreaterole AS can_create_roles,
11    r.rolcreatedb AS can_create_db,
12    r.rolcanlogin AS can_login,
13    r.rolreplication AS can_replication,
14    r.rolconnlimit AS connection_limit,
15    r.rolvaliduntil AS valid_until,
16    ARRAY(
17        SELECT b.rolname
18        FROM pg_auth_members m
19        JOIN pg_roles b ON (m.roleid = b.oid)
20        WHERE m.member = r.oid
21    ) AS member_of
22FROM pg_roles r;