postgresql query to show the groups of a user

Question

If I create a user in a group, like:

create role user_1 login inherit in role group_1; 

later, with which query could I retrieve to which group(s) a user belongs to?

Answer 1

Just to give a copy&pastable solution - On PostgreSQL (tested 8.4 and 9.3) you can do:

select rolname from pg_user join pg_auth_members on (pg_user.usesysid=pg_auth_members.member) join pg_roles on (pg_roles.oid=pg_auth_members.roleid) where pg_user.usename='USERNAME'; 

where USERNAME is the name of the login role you are interested in.

Answer 2

From the psql command line:

\dg 

or

\du