Received: from smtp1b.mail.yahoo.com (smtp3.mail.yahoo.com [128.11.68.135]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0QGgjq21070 for ; Fri, 26 Jan 2001 11:42:45 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from j13.us.greatbridge.com (HELO jupiter.greatbridge.com) (216.54.52.153) by smtp.mail.vip.suc.yahoo.com with SMTP; 26 Jan 2001 17:39:42 -0000 X-Apparently-From: Received: (from janwieck@localhost) by jupiter.greatbridge.com (8.9.3/8.9.3) id LAA02743; Fri, 26 Jan 2001 11:52:40 -0500 From: Jan Wieck Message-Id: <200101261652.LAA02743@jupiter.greatbridge.com> Subject: Re: Rule not invoked in 7.1 In-Reply-To: <28329.980480146@sss.pgh.pa.us> from Tom Lane at "Jan 25, 2001 10:35:46 pm" To: Tom Lane Date: Fri, 26 Jan 2001 11:52:40 -0500 (EST) CC: Kyle , Jan Wieck , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/380 X-Sequence-Number: 1053 Tom Lane wrote: > Kyle 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@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com