Thread: Groups

I'm trying to extract all the groups to which a user belongs from
pg_group.

Being that arrays are not my forte, I'm getting frustrated trying to get
it to work.  It seems that I can extract the proper group if the user
happens to be in a certain position in the array, but if that person is
not, it will not show up in the query.

Where am I going wrong?  How do I find all the groups to which a user
belongs no matter the number of users in any particular group?

Hope this makes sense.

            -=Kurt=-

Kurt wrote:
> I'm trying to extract all the groups to which a user belongs from
> pg_group.

If you're using 7.3.x, this should work:

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;
     SELECT INTO high
       replace(split_part(array_dims(grolist),'':'',2),'']'','''')::int
       FROM pg_group WHERE grosysid = rec.grosysid;

     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' STABLE STRICT;

CREATE VIEW groupview AS SELECT * FROM expand_groups();

regression=# select * from expand_groups();
  grosysid | groname | usesysid | usename
----------+---------+----------+---------
       100 | g1      |      100 | user1
       100 | g1      |      101 | user2
       101 | g2      |      100 | user1
       101 | g2      |      101 | user2
       101 | g2      |      102 | user3
(5 rows)

regression=# select groname from expand_groups() where usename = 'user1';
  groname
---------
  g1
  g2
(2 rows)

regression=# select groname from expand_groups() where usename = 'user3';
  groname
---------
  g2
(1 row)

This will hopefully be easier in 7.4 (patch submitted but not yet applied):

regression=# SELECT g.groname FROM pg_shadow s, pg_group g WHERE
s.usesysid = any (g.grolist) and s.usename = 'user1';
  groname
---------
  g1
  g2
(2 rows)

regression=# SELECT g.groname FROM pg_shadow s, pg_group g WHERE
s.usesysid = any (g.grolist) and s.usename = 'user3';
  groname
---------
  g2
(1 row)

HTH,

Joe