Re: A good way to test for group membership? - Mailing list pgsql-general

From Arian Prins
Subject Re: A good way to test for group membership?
Date
Msg-id 3BFA14A9.EC69E82F@zonnet.nl
Whole thread Raw
In response to A good way to test for group membership?  (Neal Lindsay <neal.lindsay@peaofohio.com>)
List pgsql-general
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);
______________________



pgsql-general by date:

Previous
From: Gilles DAROLD
Date:
Subject: OpenLDAP / POSTGRESQL is working know
Next
From: Tom Lane
Date:
Subject: Re: email time warps