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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
