From: | "A(dot)M(dot)" <agentm(at)themactionfaction(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | flatten pg_auth_members |
Date: | 2010-06-23 22:01:44 |
Message-ID: | 2DA1B4C4-5420-424B-BEA1-A33E7D036A3C@themactionfaction.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kenichiro Tanaka | 2010-06-24 01:29:39 | Re: SQL error: could not connect to database |
Previous Message | Joshua D. Drake | 2010-06-23 21:50:15 | Re: No PL/PHP ? Any reason? |