"Donald Fraser" <demolish@cwgsy.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