Thread: looking up members of a group

Hi all,

I am looking for a way to determine wether or not a user is a member of a
group...

if I do: SELECT * FROM pg_group;
I get something like this:
  groname  | grosysid |        grolist
----------+----------+-----------------------
  users    |      103 | {100,102}
  admins   |      101 | {103,1}
  guests   |      100 | {101}
  customer |      102 | {104,105,106,107,108}

that's pretty ok, and it appears that the members of the group are in an
array... arrays in a database... it's always been a blast to me, always
having trouble working with them, so I don't use arrays often... but here I
must *grin*

Anyway... I want to know if a user 'john' is member of 'admins', to
determine wether or not to show an administrative button on the website. I
was thinking of creating a function in PL/SQL, but I still have not the
hang of that... but it would be nice to do a:
SELECT * FROM is_member(john,admin); where it would return a boolean saying
yes or no...
or
SELECT * FROM is_member(john); where it would return a column with all the
groups john is member of...

maybe someone can help me out?
TIA,
Michiel

Michiel Lange wrote:
> I am looking for a way to determine wether or not a user is a member of
> a group...
>
> if I do: SELECT * FROM pg_group;
> I get something like this:
>  groname  | grosysid |        grolist
> ----------+----------+-----------------------
>  users    |      103 | {100,102}
>  admins   |      101 | {103,1}
>  guests   |      100 | {101}
>  customer |      102 | {104,105,106,107,108}
>
> that's pretty ok, and it appears that the members of the group are in an
> array... arrays in a database... it's always been a blast to me, always
> having trouble working with them, so I don't use arrays often... but
> here I must *grin*
>
> Anyway... I want to know if a user 'john' is member of 'admins', to
> determine wether or not to show an administrative button on the website.

In general, one good place to look for prewritten plpgsql is here:
http://www.brasileiro.net:8080/postgres/cookbook/

You didn't mention your version, but in 7.3 and up this will work:

8<----------------------------------------------------------------------
CREATE TYPE group_view AS (grosysid int4, groname name, usesysid int4,
usename name);

CREATE OR REPLACE FUNCTION expand_groups() RETURNS SETOF group_view AS '
DECLARE
   rec record;
   groview record;
   low int;
   high int;
BEGIN
   FOR rec IN SELECT grosysid FROM pg_group LOOP
     SELECT INTO low
       replace(split_part(array_dims(grolist),'':'',1),''['','''')::int
       FROM pg_group WHERE grosysid = rec.grosysid;
     IF low IS NULL THEN
       low := 1;
       high := 1;
     ELSE
       SELECT INTO high
         replace(split_part(array_dims(grolist),'':'',2),'']'','''')::int
         FROM pg_group WHERE grosysid = rec.grosysid;
       IF high IS NULL THEN
         high := 1;
       END IF;
     END IF;

     FOR i IN low..high LOOP
       SELECT INTO groview g.grosysid, g.groname, s.usesysid, s.usename
         FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i]
         WHERE grosysid = rec.grosysid;
       RETURN NEXT groview;
     END LOOP;
   END LOOP;
   RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE VIEW groupview AS SELECT * FROM expand_groups();
8<----------------------------------------------------------------------

regression=# SELECT * FROM groupview WHERE groname = 'admins';
  grosysid | groname | usesysid | usename
----------+---------+----------+----------
       102 | admins  |        1 | postgres
       102 | admins  |      103 | john
(2 rows)


See the manual for more on plpgsql:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql.html

And there is a good article on table functions on techdocs:
http://techdocs.postgresql.org/guides/SetReturningFunctions

HTH,

Joe