Re: Rule not invoked in 7.1 - Mailing list pgsql-sql

Tom Lane wrote:
> Kyle <kyle@actarg.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
UPDATEcan  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
updateto v1 do instead               update t1 set id = new.id, data = new.data where id = old.id;       CREATE
insertinto 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
(2rows)
 
   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@Yahoo.com #



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