Обсуждение: How to get the users name from users group?

Поиск
Список
Период
Сортировка

How to get the users name from users group?

От
Jignesh Shah
Дата:
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?

Thanks in advance,
Jack

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

От
Magnus Hagander
Дата:
2010/2/19 Jignesh Shah <jignesh.shah1980@gmail.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
listingout 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/

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

От
Jignesh Shah
Дата:
Exactly the same!

Thanks a ton.

On Fri, Feb 19, 2010 at 3:58 PM, Magnus Hagander <magnus@hagander.net> wrote:
2010/2/19 Jignesh Shah <jignesh.shah1980@gmail.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/