CREATE RULE problem/question requesting workaround - Mailing list pgsql-general

From Chris Travers
Subject CREATE RULE problem/question requesting workaround
Date
Msg-id 1070787718.4455.57.camel@localhost.localdomain
Whole thread Raw
Responses Re: CREATE RULE problem/question requesting workaround  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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 by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: postgresql locks the whole table!
Next
From: Jochem van Dieten
Date:
Subject: Re: functions/operators with 2 sets as arguments