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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
