CREATE RULE problem/question requesting workaround
От | Chris Travers |
---|---|
Тема | CREATE RULE problem/question requesting workaround |
Дата | |
Msg-id | 1070787718.4455.57.camel@localhost.localdomain обсуждение исходный текст |
Ответы |
Re: CREATE RULE problem/question requesting workaround
|
Список | pgsql-general |
Hi all; I am using PostgreSQL 7.4 on RedHat 9, though I don't think that is important to this problem. I am attempting to write a rule that will check to see whether certain conditions are right for a SELECT query and abort the query of those connections are not right. In this case, I am trying to do additional permissions enforcement. I have written a function to do the checking, but when I create the rule, I get an error. So here is the example (the actual user-defined function is not important-- if it just returns true, I get the same error): CREATE OR REPLACE FUNCTION sh_fail_nonsu_admin(BOOL) RETURNS BOOL AS' DECLARE allow_admin ALIAS FOR $1; is_admin BOOL; BEGIN IF (SELECT sh_is_superuser()) THEN RETURN TRUE; ELSEIF allow_admin == TRUE THEN SELECT INTO is_admin admin FROM owners WHERE login = sh_get_eff_user(); IF is_admin == TRUE THEN RETURN TRUE; END IF; END IF; RAISE EXCEPTION ''PERMISSION DENIED''; RETURN FALSE; END; ' LANGUAGE PLPGSQL; Basically the purpose of the function is to check whether the user is a superuser of the application (and thus able to bypass my shared-hosting environment security system and rely on database perms instead. As you can see it merely checks a few options and then returns true or raises an exception. Now, the problem is that when I try to create the rule (something like) CREATE OR REPLACE RULE hermes_sh_perm AS ON SELECT TO hermes_shared_hosting.auth_shadow DO select sh_fail_nonsu_admin(FALSE) I get the following error: ERROR: rules on SELECT must have action INSTEAD SELECT If I try to write it as an unqualified join: CREATE OR REPLACE RULE hermes_sh_perm AS ON SELECT TO hermes_shared_hosting.auth_shadow DO INSTEAD SELECT * from hermes_shared_hosting.auth_shadow, sh_fail_nonsu_admin(FALSE) I get the following error: ERROR: SELECT rule's target list has too many entries I have tried to rewrite the rule as a CASE statement as well, but again I get the same problem. Is there any way to try to enforce a rewrite rule in such a away as to do the select query as normal, but if necessary raise an exception (to abort the select)? Of course triggers don't work on select, so I am wondering what else can be done. Best Wishes, Chris Travers
В списке pgsql-general по дате отправления: