Обсуждение: Re: [HACKERS] Role members
On Mon, May 21, 2007 at 02:21:52PM +0400, Akmal Akmalhojaev wrote: > Hello! I have the following question. Moving to -general. > For example I have a role ID1 with members ID2 and ID3. > Role ID2 has also members ID4 and ID5. It means that roles ID4 and ID5 are > members of ID1. > The question: Is there any function in PostgreSQL, that finds all the > members of role ID1 - even such members, as ID4 and ID5. Here's a function I've written in SQL: CREATE OR REPLACE FUNCTION get_roles_under(OID) RETURNS SETOF OID LANGUAGE sql AS $$ SELECT $1 UNION SELECT member FROM pg_catalog.pg_auth_members WHERE roleid = $1 UNION SELECT get_roles_over(roleid) AS "roleid" FROM pg_catalog.pg_auth_members WHERE roleid IN ( SELECT member FROM pg_catalog.pg_auth_members WHERE roleid = $1 ) $$; Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
On May 21, 2007, at 8:25 AM, David Fetter wrote: > On Mon, May 21, 2007 at 02:21:52PM +0400, Akmal Akmalhojaev wrote: >> For example I have a role ID1 with members ID2 and ID3. >> Role ID2 has also members ID4 and ID5. It means that roles ID4 and >> ID5 are >> members of ID1. >> The question: Is there any function in PostgreSQL, that finds all the >> members of role ID1 - even such members, as ID4 and ID5. > > Here's a function I've written in SQL: > > CREATE OR REPLACE FUNCTION get_roles_under(OID) > RETURNS SETOF OID > LANGUAGE sql > AS $$ > SELECT > $1 > UNION > SELECT > member > FROM > pg_catalog.pg_auth_members > WHERE > roleid = $1 > UNION > SELECT > get_roles_over(roleid) AS "roleid" > FROM > pg_catalog.pg_auth_members > WHERE > roleid IN ( > SELECT > member > FROM > pg_catalog.pg_auth_members > WHERE > roleid = $1 > ) > $$; Should that call to get_roles_over be a call to get_roles_under? -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)