Re: How to check the role has been granted to which role. Help me to double check . Thanks.

Hi,
what about this:

SELECT p.rolname, m.rolname as member, g.rolname as grantor
FROM pg_authid p
INNER JOIN pg_auth_members am ON (p.oid = am.roleid)
INNER JOIN pg_authid m ON (am.member = m.oid)
INNER JOIN pg_authid g ON (am.grantor = g.oid)

You can use proper WHERE to filter results.

Regards,
Bartek

2012/3/31 leaf_yxj <leaf_yxj(at)163(dot)com>

> I want to check the role has been granted to which role. In my working
> environment, the all the normal is assigned to role group. when i issue dp,
> it only give me the role group privilege. So I need to check which user is
> in which user group. THe following is my sql to do that. Is there anybody
> has a better way to do it. Thanks. Grace
>
> select DISTINCT user, group_name, grantor, admin_option
> from
> (select usename AS user,roleid,admin_option from pg_user join
> pg_auth_members on ( pg_user.usesysid=pg_auth_members.member)) a,
> (select usename AS group_name,roleid from pg_user join pg_auth_members on
> (pg_user.usesysid=pg_auth_members.roleid)) b,
> (select usename AS grantor,roleid from pg_user join pg_auth_members on
> (pg_user.usesysid=pg_auth_members.grantor)) c
>
> where a.roleid=b.roleid
> and b.roleid=c.roleid;
>
> member | group_name | grantor | admin_option
> --------+------------+---------+--------------
> user1 | grace | gpadmin | f
> user_1 | grace | gpadmin | f
> (2 rows)
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/How-to-check-the-role-has-been-granted-to-which-role-Help-me-to-double-check-Thanks-tp5608906p5608906.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>