From: | "A(dot)M(dot)" <agentm(at)themactionfaction(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: flatten pg_auth_members |
Date: | 2010-06-25 16:28:24 |
Message-ID: | 26CB6EEE-D30F-474E-BC15-A0496E42F716@themactionfaction.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Nolan | 2010-06-25 16:47:39 | Re: Equivalent to "use database" in postgre |
Previous Message | Rob Wultsch | 2010-06-25 16:05:56 | Re: Need Some Recent Information on the Differences between Postgres and MySql |