Recently I had to find a way to dynamically generate a list of users and their roles at work, but I couldn't quickly find the SQL I needed to generate the list. However, I could look at the system tables for Postgres and figured out how I could reverse engineer this and map the users back to the roles they were assigned. This code only lists one role/user per line, but you could revise it so you do this as a sub-query and return a list (array) of roles (all roles on one line).
SELECT r.rolname AS "role", u.rolname AS "username", u.rolsuper AS "SUPER" FROM pg_authid u, pg_authid r, pg_auth_members m WHERE m.roleid = r.oid AND m.member = u.oid ORDER BY 2, 1 ;
Of course, if you wanted to you could list only those users who had a single role and change the sort order:
SELECT r.rolname AS "role", u.rolname AS "username", u.rolsuper AS "SUPER" FROM pg_authid u, pg_authid r, pg_auth_members m WHERE m.roleid = r.oid AND m.member = u.oid AND r.rolname in ( 'dba', '' ) ORDER BY 2, 1 ;
I cheated a little in the above by including an empty role name (the double ticks at line 13) so I can add additional roles (or comment them out as appropriate) and the inclusion of the '' is a cheat so all roles are included. I could just have easily searched for a role name of "ZZZ" or some other arbitrary value (but I know I will also never have a blank or empty role name).