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

Uhhuh

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>';

Spermary

As of Dec 16, 2022 the query above does not work. – Luckily

I just ran this today as a superuser and it worked perfectly. So maybe the previous commenter was just running it without sufficient permissions? – Corking

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.

Trumpeter

Answer 3

this worked better for me:

SELECT 
    pu.usename, 
    pg.groname 
FROM 
    pg_user pu
left join pg_group pg
    on pu.usesysid = ANY(pg.grolist)
order by pu.usename

Bandy