Re: Correction to previous post - Permission on views

This is a correction to the previously posted bug - please ignore the previous posted bug as this is the corrected version:

PostgreSQL 7.4.2 on i386-redhat-linux-gnu, compiled by GCC 2.96

I have a simple VIEW such as the following:

CREATE OR REPLACE VIEW vu_tbl_useracc AS
SELECT ua.id_user, ua.id_cmpy, ua.id_contrib, ua.dt_edited, ua.id_editedby
FROM tbl_useracc ua;

GRANT SELECT, INSERT ON TABLE vu_tbl_useracc TO GROUP grp_cisx_admin;

CREATE OR REPLACE RULE rul_i02 AS
ON INSERT TO vu_tbl_useracc DO INSTEAD
INSERT INTO tbl_useracc (id_user, id_cmpy, id_contrib) VALUES (new.id_user, new.id_cmpy, new.id_contrib);

If I attempt to INSERT into the view all works as expected.

Now when I add the following rule:

CREATE OR REPLACE RULE rul_i01 AS
ON INSERT TO vu_tbl_useracc
WHERE new.id_contrib = 1 DO SELECT raise_exception('Cannot insert Companies to access for CISX Users'::text) AS error;

Now whenever I attempt to INSERT into the view I get the following error.
ERROR: permission denied for relation vu_tbl_useracc

The error goes away if I grant both INSERT and UPDATE permissions to the above group.

I didn't have this problem on version 7.3.4 which is what I have upgraded from.

Regards
Donald Fraser

"Donald Fraser" <demolish(at)cwgsy(dot)net> writes:
> CREATE OR REPLACE RULE rul_i01 AS
> ON INSERT TO vu_tbl_useracc
> WHERE new.id_contrib = 1 DO SELECT raise_exception('Cannot insert Companies to access for CISX Users'::text) AS error;
> ERROR: permission denied for relation vu_tbl_useracc

Ah. This is a known bug which is fixed for 7.5, but there doesn't seem
to be any fix possible in the 7.4 series (without initdb which we don't
want to require). It's a variant of Tim Burgess' problem:
http://archives.postgresql.org/pgsql-bugs/2003-02/msg00038.php
basically that the permissions checker assumes that the current command
(here SELECT) is indicative of the type of permission to check the view
for, when of course it should be checking for INSERT. You can get burnt
by this whenever a rule generates a query of a different type than the
one replaced. (And yes, it's surprising this wasn't noticed long before
it was ...)

> I didn't have this problem on version 7.3.4 which is what I have upgraded f=
> rom.

7.3.4 had an erroneous fix which effectively disabled most forms of
permission checking for views. We decided it was better to revert to
the old misbehavior until it could be fixed properly in 7.5.

regards, tom lane