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
