Thread: A good way to test for group membership?

Hello all,

    I am creating a PHP-based time-tracking program for my company.  I use
user groups to restrict access to tables and I don't want to look somewhere
else to tell if the current user is, say, a manager.  I currently use dummy
tables with select permissions given to certain groups and attempt select
statements on them, but there must be a better way. How does PostgreSQL do
it internally?

-Neal Lindsay

Hello Neal,

Neal Lindsay schreef:

>
> I currently use dummy
> tables with select permissions given to certain groups and attempt select
> statements on them, but there must be a better way. How does PostgreSQL do
> it internally?

I have made the following PlPgsql function which returns true if a user is member
of a group:
_________________________
CREATE FUNCTION groupmember (int, int) RETURNS BOOLEAN AS
'
DECLARE
list pg_group.grolist%TYPE;
aanw bool := false;
i int4 := 1;
group ALIAS FOR $1;
user ALIAS FOR $2;
BEGIN
  SELECT grolist into list from pg_group where grosysid = $1;

  WHILE ((list[i] IS NOT NULL) AND (aanw=false)) LOOP
    IF (list[i] = $2) THEN
       aanw := true;
    END IF;
    i := i + 1;
  END LOOP;

  RETURN aanw;
END;' LANGUAGE 'PlPgSQL';

Have Fun!
Arian.
______________________

And then you could even make this view:
______________________
CREATE VIEW users_by_group (grosysid, usesysid) AS
SELECT pg_group.grosysid, pg_user.usesysid FROM pg_group, pg_us
er WHERE groupmember(pg_group.grosysid, pg_user.usesysid);
______________________