flatten pg_auth_members

["A.M." <agentm@xxxxxxxxxxxxxxxxxxxxx>]

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

Thanks for any assistance,
M



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general