CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
kee
kee

Reputation: 11629

Redshift: How to list all users in a group

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

Upvotes: 35

Views: 87280

Answers (3)

Felipe Coxa Rodrigues
Felipe Coxa Rodrigues

Reputation: 41

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

Upvotes: 4

Nav
Nav

Reputation: 1394

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

Upvotes: 76

Srikant
Srikant

Reputation: 437

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.

Upvotes: 36

Related Questions