Bugs with rules on views/tables: permission denied

----- Original Message -----=20
From: "Tim Burgess" <tim@queens.unimelb.edu.au>
To: <pgsql-bugs@postgresql.org>
Sent: Monday, February 10, 2003 6:51 PM
Subject: [BUGS] 'update' as action of 'insert' rule: permission denied


> Gday All,
>=20
> I'm surprised I can't find any descriptions of this in the archive=20
> already, since I had imagined it would be a common action, but=20
> apparently not!
>=20
> Illustration:
>=20
> As superuser:
>=20
> create table1 ( test1 integer );
> create table2 ( test2 integer );
> create user pleb;
> grant insert on table1 to pleb;
> create rule test_rule as on insert to table1 do update table2 set test2=
=20
> =3D 2 where test2 =3D 0;
>=20
> As user pleb:
>=20
> insert into table1 values (1);
> ERROR:  table1: Permission denied.
>=20
> However, just to check, if we remove the rule (using DROP RULE) the=20
> INSERT query works fine.
> I also wrote a query using 'insert' as the action instead of 'update'=20
> and that worked fine too.
> I also tried granting the user pleb update and select permission on=20
> table test2, and that failed in the same way as above.
>=20
> My impression from the doco is that the permission rules should be=20
> applied _before_ any rule interpretation is applied, and then again as=20
> the rule owner for the queries (extra queries only?  this is=20
> unspecified in the doco) that the rule creates.  Since the rule owner=20
> in this case is the superuser (correct?), the query should succeed=20
> whether or not the rule is present.
>=20
> Note that the query succeeds when run as the superuser...
>=20

In addition to this observation I note that the same bugs apply to views.

My observations are that unless the user has the permissions to execute=20
the equivalent SQL statement of the rule as though it were a normal=20
query then if will fail with "permission denied". The only time failure=20
doesn't occur is thus:

1) you are performing an INSERT on a table/view that you have been
granted INSERT permissions on and the rule is an INSERT.
e.g. create rule test_rule as on insert to table1 do insert into table2...=
=20

2) you are performing an UPDATE on a table/view that you have been
granted UPDATE permissions on and the rule is an UPDATE...
e.g. create rule test_rule as on update to table1 do update table2...=20

These bugs are a real pain as one of the main points of views are to=20
hide the underlying tables and rules that apply to additional tables.
If the user has to have permissions to such functions and additional=20
tables it defeats the purpose some what.

Regards
Donald Fraser