Re: A good way to test for group membership?

From: Arian Prins <prinsarian(at)zonnet(dot)nl>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: A good way to test for group membership?
Date: 2001-11-20 08:30:33
Message-ID: 3BFA14A9.EC69E82F@zonnet.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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);
______________________

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Arian Prins 2001-11-20 08:39:34 Re: cant alter/create tables via odbc
Previous Message Thomas T. Thai 2001-11-20 08:25:53 postgresql-7.2b2 NetBSD/Alpha 1.5W passed