Re: Rule not invoked in 7.1

Tom Lane wrote:
> Kyle <kyle(at)actarg(dot)com> writes:
> > If someone happens to know the primary key of a record they should not be
> > able to access, and they try to update it, I would like the backend to
> > ignore the query (or better yet, raise an exception but I haven't figured
> > out how to do that). If the status is correct, the update should proceed.
>
> This might be better done with a trigger than a rule. For one thing,
> a trigger can easily raise an exception. MHO is that rules are good
> when you need to update multiple rows in other tables when certain
> things happen. If you just want to validate or twiddle an individual
> tuple as it's inserted/updated, a trigger is a good bet.

But the trigger aproach requires access permissions to the
base table in the first place, and exactly that's what Kyle
want to restrict.

Kyle, I doubt if you need the condition in the update rule at
all. As far as I understood, your view restricts what the
user can see from the base table. This restricted SELECT rule
is applied to UPDATE events as well, so the UPDATE can never
affect rows which are invisible through the view.

create table t1 (
id integer,
visible bool,
data text
);
CREATE
create view v1 as select id, data from t1 where visible;
CREATE
create rule upd_v1 as on update to v1 do instead
update t1 set id = new.id, data = new.data where id = old.id;
CREATE
insert into t1 values (1, 't', 'Visible row');
INSERT 18809 1
insert into t1 values (2, 'f', 'Invisible row');
INSERT 18810 1
select * from v1;
id | data
----+-------------
1 | Visible row
(1 row)

update v1 set data = 'Updated row';
UPDATE 1
select * from t1;
id | visible | data
----+---------+---------------
2 | f | Invisible row
1 | t | Updated row
(2 rows)

update v1 set data = 'Updated row' where id = 2;
UPDATE 0
select * from t1;
id | visible | data
----+---------+---------------
2 | f | Invisible row
1 | t | Updated row
(2 rows)

As you see, neither an unqualified update of all rows, nor if
the user guesses a valid id, can touch the invisible row
filtered out by the view.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com