Обсуждение: flatten pg_auth_members

Hello,

I am trying to make a query which will flatten pg_auth_members into a table with two columns "user" and "group" which
willrecurse inherited roles so that each login role is associated once with any inherited roles (assuming all
associatedroles are inherited). 

This query does not do what I want, but I can't quite wrap my head around the recursion part:

WITH RECURSIVE usergroups(user_id,group_id) AS (
    SELECT am.member AS user_id,am.roleid AS group_id FROM pg_auth_members AS am
    UNION
    SELECT am.member AS user_id,am.roleid AS group_id FROM usergroups AS u,pg_auth_members AS am WHERE
am.roleid=u.group_id
)
SELECT r.user_id,r.group_id FROM usergroups AS r;

For a role inheritance tree of "bob (1)"->"manager(2)"->"employee(3)", I would like to see:

user | group
1 | 2
1 | 3

Thanks for any assistance,
M

On Jun 23, 2010, at 6:01 PM, A.M. wrote:

> Hello,
>
> I am trying to make a query which will flatten pg_auth_members into a table with two columns "user" and "group" which
willrecurse inherited roles so that each login role is associated once with any inherited roles (assuming all
associatedroles are inherited). 
>
> This query does not do what I want, but I can't quite wrap my head around the recursion part:
>
> WITH RECURSIVE usergroups(user_id,group_id) AS (
>     SELECT am.member AS user_id,am.roleid AS group_id FROM pg_auth_members AS am
>     UNION
>     SELECT am.member AS user_id,am.roleid AS group_id FROM usergroups AS u,pg_auth_members AS am WHERE
am.roleid=u.group_id
> )
> SELECT r.user_id,r.group_id FROM usergroups AS r;
>
> For a role inheritance tree of "bob (1)"->"manager(2)"->"employee(3)", I would like to see:
>
> user | group
> 1 | 2
> 1 | 3

Hm- I wasn't able to figure out the WITH RECURSIVE construct, so I used a cartesian product instead:

SELECT DISTINCT
am1.member,
(SELECT a2.rolname FROM pg_authid AS a2 WHERE a2.oid=am1.member),
am2.roleid,
(SELECT a3.rolname FROM pg_authid AS a3 WHERE a3.oid=am2.roleid)
FROM pg_auth_members AS am1,pg_auth_members AS am2 WHERE pg_has_role(am1.member,am2.roleid,'MEMBER')
UNION
SELECT am1.member,
(SELECT a2.rolname FROM pg_authid AS a2 WHERE a2.oid=am1.member),
am1.member,
(SELECT a2.rolname FROM pg_authid AS a2 WHERE a2.oid=am1.member)
 FROM pg_auth_members AS am1;


Cheers,
M