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



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to