Bugs with rules on views/tables: permission denied

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

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

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
the equivalent SQL statement of the rule as though it were a normal
query then if will fail with "permission denied". The only time failure
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...

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...

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

Regards
Donald Fraser