Re: flatten pg_auth_members

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 will recurse inherited roles so that each login role is associated once with any inherited roles (assuming all associated roles 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