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
