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);
______________________