Redshift: How to list all users in a group

Question

Getting the list of users belonging to a group in Redshift seems to be a fairly common task but I don't know how to interpret BLOB in grolist field.

I am literally getting "BLOB" in grolist field from TeamSQL. Not so sure this is specific to TeamSQL but I kind of remember thatI got a list of IDs there instead previously in other tool

Answer 1

This worked for me:

select usename 
from pg_user , pg_group
where pg_user.usesysid = ANY(pg_group.grolist) and 
      pg_group.groname='<YOUR_GROUP_NAME>';

Answer 2

SELECT usename, groname 
FROM pg_user, pg_group
WHERE pg_user.usesysid = ANY(pg_group.grolist)
AND pg_group.groname in (SELECT DISTINCT pg_group.groname from pg_group);

This will provide the usernames along with the respective groups.