Re: How to get the users name from users group?

2010/2/19 Jignesh Shah <jignesh(dot)shah1980(at)gmail(dot)com>:
> Hello All,
>
> I have created role "database-users" and inserted some of users inside it. Could you tell me which query to use for listing out those users name? I went through pg_users, pg_group but no luck.
>
> CREATE ROLE database-users
>   NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
>
> CREATE ROLE "dbuser1" LOGIN
>   NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
> GRANT database-users TO "dbuser1";
>
> CREATE ROLE "dbuser2" LOGIN
>   NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
> GRANT database-users TO "dbuser2";
>
> How to query database-users to list the dbuser1 and dbuser2?

I think you're looking for:
SELECT rolname from pg_authid a INNER JOIN pg_auth_members m ON
m.member=a.oid WHERE m.roleid=(SELECT oid FROM pg_authid WHERE
rolname='database-users')

or something similar to that.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/