From kyle@actarg.com Wed Jan 24 22:14:24 2001 Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0OMEOq79265 for ; Wed, 24 Jan 2001 17:14:24 -0500 (EST) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id PAA31943 for ; Wed, 24 Jan 2001 15:14:21 -0700 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id PAA19299 for ; Wed, 24 Jan 2001 15:14:32 -0700 Message-ID: <3A6F53C8.CA9FE05C@actarg.com> Date: Wed, 24 Jan 2001 15:14:32 -0700 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Rule not invoked in 7.1 Content-Type: multipart/mixed; boundary="------------AAC3D857B40623E0D5D9362E" X-Archive-Number: 200101/341 X-Sequence-Number: 1014 This is a multi-part message in MIME format. --------------AAC3D857B40623E0D5D9362E Content-Type: multipart/alternative; boundary="------------F682CF7F7B85FDEADA50AB14" --------------F682CF7F7B85FDEADA50AB14 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have a number of views that seemed to work fine in 7.0.3. When I try to do an update to these views under 7.1, I get the following error: ERROR: Cannot update a view without an appropriate rule. For example, there's a view that looks like this: create view pay_req_v_prl as select empl_id,wdate,seq,hours,hot,proj,entby,paytyp,status,poinum,added,rate,otrate,appby,gross,rgross,cost,ttype,expnum,oid as _oid from pay_req create rule pay_req_v_prl_update as on update to pay_req_v_prl where old.status = 'appr' do instead update pay_req set status = new.status, gross = new.gross, cost = new.cost, ttype = new.ttype, expnum = new.expnum, rgross = new.rgross, hot = new.hot where empl_id = old.empl_id and wdate = old.wdate and seq = old.seq; The sql looks like this: update pay_req_v_prl set gross = 90.09 where empl_id = 1010 and wdate = '2001-01-08' and seq = 1; The record it should update does seem to have status = 'appr' and it updates fine on my 7.0.3 box. Any ideas? --------------F682CF7F7B85FDEADA50AB14 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit I have a number of views that seemed to work fine in 7.0.3.  When I try to do an update to these views under 7.1, I get the following error:

ERROR:  Cannot update a view without an appropriate rule.

For example, there's a view that looks like this:

create view pay_req_v_prl as select empl_id,wdate,seq,hours,hot,proj,entby,paytyp,status,poinum,added,rate,otrate,appby,gross,rgross,cost,ttype,expnum,oid as _oid from pay_req

create rule pay_req_v_prl_update as on update to pay_req_v_prl
    where old.status = 'appr' do instead
    update pay_req set status = new.status, gross = new.gross, cost = new.cost,
    ttype = new.ttype, expnum = new.expnum, rgross = new.rgross, hot = new.hot
    where empl_id = old.empl_id and wdate = old.wdate and seq = old.seq;

The sql looks like this:

update pay_req_v_prl set gross = 90.09 where empl_id = 1010 and wdate = '2001-01-08' and seq = 1;

The record it should update does seem to have status = 'appr' and it updates fine on my 7.0.3 box.

Any ideas?

--------------F682CF7F7B85FDEADA50AB14-- --------------AAC3D857B40623E0D5D9362E Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:kyle@actarg.com title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard --------------AAC3D857B40623E0D5D9362E-- From tgl@sss.pgh.pa.us Thu Jan 25 04:53:45 2001 Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0P4rjq07891 for ; Wed, 24 Jan 2001 23:53:45 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f0P4rT926491; Wed, 24 Jan 2001 23:53:29 -0500 (EST) To: Kyle cc: pgsql-sql@postgresql.org Subject: Re: Rule not invoked in 7.1 In-reply-to: <3A6F53C8.CA9FE05C@actarg.com> References: <3A6F53C8.CA9FE05C@actarg.com> Comments: In-reply-to Kyle message dated "Wed, 24 Jan 2001 15:14:32 -0700" Date: Wed, 24 Jan 2001 23:53:29 -0500 Message-ID: <26488.980398409@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200101/348 X-Sequence-Number: 1021 Kyle writes: > ERROR: Cannot update a view without an appropriate rule. 7.1 insists that you provide an *unconditional* DO INSTEAD rule for a view. What do you think was happening on your old database when the "where old.status = 'appr'" clause wasn't satisfied? Nothing good I'm afraid. If you really do need conditional rules, you can satisfy the check by writing one unconditional DO INSTEAD NOTHING rule and then one or more conditional non-INSTEAD rules. But you should think carefully about what you expect to happen when you use a conditional rule. regards, tom lane From janwieck@Yahoo.com Thu Jan 25 13:11:33 2001 Received: from smtp1.mail.yahoo.com (smtp1.mail.yahoo.com [128.11.69.60]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0PDBXq56343 for ; Thu, 25 Jan 2001 08:11:33 -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; 25 Jan 2001 13:11:29 -0000 X-Apparently-From: Received: (from janwieck@localhost) by jupiter.greatbridge.com (8.9.3/8.9.3) id IAA02253; Thu, 25 Jan 2001 08:21:09 -0500 From: Jan Wieck Message-Id: <200101251321.IAA02253@jupiter.greatbridge.com> Subject: Re: Rule not invoked in 7.1 In-Reply-To: <26488.980398409@sss.pgh.pa.us> from Tom Lane at "Jan 24, 2001 11:53:29 pm" To: Tom Lane Date: Thu, 25 Jan 2001 08:21:09 -0500 (EST) CC: Kyle , 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/355 X-Sequence-Number: 1028 Tom Lane wrote: > Kyle writes: > > ERROR: Cannot update a view without an appropriate rule. > > 7.1 insists that you provide an *unconditional* DO INSTEAD rule > for a view. What do you think was happening on your old database > when the "where old.status = 'appr'" clause wasn't satisfied? > Nothing good I'm afraid. No harm in the UPDATE case, because so far there aren't any tuples in the view that could be affected by the still executed original query. But in an INSERT case, it would let tuples through into the views heap file. > If you really do need conditional rules, you can satisfy the check > by writing one unconditional DO INSTEAD NOTHING rule and then one > or more conditional non-INSTEAD rules. But you should think carefully > about what you expect to happen when you use a conditional rule. Alternatively he should be able to move the condition down into the query itself. In that case, it's an unconditional INSTEAD rule, causing the rewriter not to fork off another query but replace the initial one completely. But the condition is still there and affects the effects. 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 From kyle@actarg.com Thu Jan 25 17:27:31 2001 Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0PHRVq76388 for ; Thu, 25 Jan 2001 12:27:31 -0500 (EST) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id KAA13058; Thu, 25 Jan 2001 10:27:26 -0700 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id KAA21644; Thu, 25 Jan 2001 10:27:39 -0700 Message-ID: <3A706208.AD6AB9@actarg.com> Date: Thu, 25 Jan 2001 10:27:36 -0700 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: Jan Wieck , pgsql-sql@postgresql.org Subject: Re: Rule not invoked in 7.1 References: <200101251321.IAA02253@jupiter.greatbridge.com> Content-Type: multipart/mixed; boundary="------------02781DDADCB71FFBA4FB3C25" X-Archive-Number: 200101/357 X-Sequence-Number: 1030 This is a multi-part message in MIME format. --------------02781DDADCB71FFBA4FB3C25 Content-Type: multipart/alternative; boundary="------------F3ED4B7F6540DD1E0E329179" --------------F3ED4B7F6540DD1E0E329179 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Jan Wieck wrote: > Tom Lane wrote: > > Kyle writes: > > > ERROR: Cannot update a view without an appropriate rule. > > > > 7.1 insists that you provide an *unconditional* DO INSTEAD rule > > for a view. What do you think was happening on your old database > > when the "where old.status = 'appr'" clause wasn't satisfied? > > Nothing good I'm afraid. > > No harm in the UPDATE case, because so far there aren't any > tuples in the view that could be affected by the still > executed original query. But in an INSERT case, it would let > tuples through into the views heap file. > > > If you really do need conditional rules, you can satisfy the check > > by writing one unconditional DO INSTEAD NOTHING rule and then one > > or more conditional non-INSTEAD rules. But you should think carefully > > about what you expect to happen when you use a conditional rule. > I'm using the view as a way of restricting a single class of users to only update tuples that have a certain status in the table. Isn't this essentially what a "dynamic view" is? 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. I've inserted the dummy do nothing rule as follows: create view pay_req_v_prl as select empl_id,wdate,seq,hours,hot,proj,entby,paytyp,status,poinum,added,rate,otrate,appby,gross,rgross,cost,ttype,expnum,oid as _oid from pay_req; create rule pay_req_v_prl_upnull as on update to pay_req_v_prl do instead nothing; create rule pay_req_v_prl_update as on update to pay_req_v_prl where old.status = 'appr' do instead update pay_req set status = new.status, gross = new.gross, cost = new.cost, ttype = new.ttype, expnum = new.expnum, rgross = new.rgross, hot = new.hot where empl_id = old.empl_id and wdate = old.wdate and seq = old.seq; This seems to work now when I do: psql ati -c "update pay_req_v_prl set gross = 90.09 where empl_id = 1010 and wdate = '2001-01-08' and seq = 1;" You see any problems with this method? BTW, the update still returns UPDATE 0 from psql even though a record was updated. I've never quite figured out why views with rules do this. I've also done some testing on 7.1 for that nasty thing in 7.0 where you had to give select,update privs to a table referenced by a foreign key. So far, looks good. I was able to reference a table that the user didn't have privs to at all. I think that is the desired behavior. Good work guys! 7.1 is looking good. --------------F3ED4B7F6540DD1E0E329179 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Jan Wieck wrote:

Tom Lane wrote:
> Kyle <kyle@actarg.com> writes:
> > ERROR:  Cannot update a view without an appropriate rule.
>
> 7.1 insists that you provide an *unconditional* DO INSTEAD rule
> for a view.  What do you think was happening on your old database
> when the "where old.status = 'appr'" clause wasn't satisfied?
> Nothing good I'm afraid.

    No  harm  in the UPDATE case, because so far there aren't any
    tuples in the view  that  could  be  affected  by  the  still
    executed original query.  But in an INSERT case, it would let
    tuples through into the views heap file.

> If you really do need conditional rules, you can satisfy the check
> by writing one unconditional DO INSTEAD NOTHING rule and then one
> or more conditional non-INSTEAD rules.  But you should think carefully
> about what you expect to happen when you use a conditional rule.
 


I'm using the view as a way of restricting a single class of users to only update tuples that have a certain status in the table.  Isn't this essentially what a "dynamic view" is?

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.

I've inserted the dummy do nothing rule as follows:

create view pay_req_v_prl as select empl_id,wdate,seq,hours,hot,proj,entby,paytyp,status,poinum,added,rate,otrate,appby,gross,rgross,cost,ttype,expnum,oid as _oid from pay_req;

create rule pay_req_v_prl_upnull as on update to pay_req_v_prl do instead nothing;
create rule pay_req_v_prl_update as on update to pay_req_v_prl
    where old.status = 'appr' do instead
    update pay_req set status = new.status, gross = new.gross, cost = new.cost,
    ttype = new.ttype, expnum = new.expnum, rgross = new.rgross, hot = new.hot
    where empl_id = old.empl_id and wdate = old.wdate and seq = old.seq;

This seems to work now when I do:

psql ati -c "update pay_req_v_prl set gross = 90.09  where empl_id = 1010 and wdate = '2001-01-08' and seq = 1;"

You see any problems with this method?

BTW, the update still returns UPDATE 0 from psql even though a record was updated.  I've never quite figured out why views with rules do this.

I've also done some testing on 7.1 for that nasty thing in 7.0 where you had to give select,update privs to a table referenced by a foreign key.  So far, looks good.  I was able to reference a table that the user didn't have privs to at all.  I think that is the desired behavior.

Good work guys!  7.1 is looking good.
 
  --------------F3ED4B7F6540DD1E0E329179-- --------------02781DDADCB71FFBA4FB3C25 Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:kyle@actarg.com title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard --------------02781DDADCB71FFBA4FB3C25-- From tgl@sss.pgh.pa.us Fri Jan 26 03:35:53 2001 Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0Q3Zrq48822 for ; Thu, 25 Jan 2001 22:35:53 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f0Q3Zl928332; Thu, 25 Jan 2001 22:35:47 -0500 (EST) To: Kyle cc: Jan Wieck , pgsql-sql@postgresql.org Subject: Re: Rule not invoked in 7.1 In-reply-to: <3A706208.AD6AB9@actarg.com> References: <200101251321.IAA02253@jupiter.greatbridge.com> <3A706208.AD6AB9@actarg.com> Comments: In-reply-to Kyle message dated "Thu, 25 Jan 2001 10:27:36 -0700" Date: Thu, 25 Jan 2001 22:35:46 -0500 Message-ID: <28329.980480146@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200101/374 X-Sequence-Number: 1047 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. regards, tom lane From janwieck@Yahoo.com Fri Jan 26 16:42:45 2001 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 From kyle@actarg.com Fri Jan 26 17:05:17 2001 Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0QH5Hq23634 for ; Fri, 26 Jan 2001 12:05:17 -0500 (EST) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id KAA28650; Fri, 26 Jan 2001 10:05:14 -0700 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id KAA23122; Fri, 26 Jan 2001 10:05:27 -0700 Message-ID: <3A71AE57.BBDFA129@actarg.com> Date: Fri, 26 Jan 2001 10:05:27 -0700 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: pgsql-sql@postgresql.org, janwieck@yahoo.com Subject: Re: Rule not invoked in 7.1 References: <200101251321.IAA02253@jupiter.greatbridge.com> <3A706208.AD6AB9@actarg.com> <28329.980480146@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------ABAE6FC58618AD06788E029F" X-Archive-Number: 200101/383 X-Sequence-Number: 1056 This is a multi-part message in MIME format. --------------ABAE6FC58618AD06788E029F Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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. > The underlying table contains payroll data. Each employee should be able to enter payroll requests, but there is a very strict set of rules about which rows he should be able to access, how he can access them, and when. For example, an employee can enter new time records, but once the records have been approved or paid, he can no longer modify them. I have set up several views that allow access to the rows depending on their status. For example, employees only have access to "working records." Once they are satisfied with the data they have entered, they change the status to "open" at which point they can no longer edit it. Supervisors then have access to the record and can approve it, changing its status to "approved" and so on. The problem I had with trying to use a trigger was that the trigger fires on the underlying table, regardless of which view the user comes in on. (At least it seemed that way when I tested it.) I need to apply a different set of rules based on which view the user is coming in on--not simply who the user is. Is there a way to configure a trigger to fire on a view rather than the underlying table? I tried linking a trigger to a view but it seemed to not get called at all. I assumed this was because the rewriting rules were directing the query away from the view class and so the trigger was never getting called at all. --------------ABAE6FC58618AD06788E029F Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:kyle@actarg.com title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard --------------ABAE6FC58618AD06788E029F-- From kyle@actarg.com Fri Jan 26 17:13:16 2001 Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0QHDGq24677 for ; Fri, 26 Jan 2001 12:13:16 -0500 (EST) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id KAA28739; Fri, 26 Jan 2001 10:13:15 -0700 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id KAA23152; Fri, 26 Jan 2001 10:13:28 -0700 Message-ID: <3A71B038.175C5B26@actarg.com> Date: Fri, 26 Jan 2001 10:13:28 -0700 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: Jan Wieck , pgsql-sql@postgresql.org Subject: Re: Rule not invoked in 7.1 References: <200101251321.IAA02253@jupiter.greatbridge.com> <3A706208.AD6AB9@actarg.com> <28329.980480146@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------F695A53B7981BD4C1B4AB234" X-Archive-Number: 200101/384 X-Sequence-Number: 1057 This is a multi-part message in MIME format. --------------F695A53B7981BD4C1B4AB234 Content-Type: multipart/alternative; boundary="------------0A8B3006FFE8479D32A873ED" --------------0A8B3006FFE8479D32A873ED Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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. > I have another interesting use of this same concept you may be interested in (or slightly nausious, as the case may be): The underlying database (empl) contains all employees in the organization. The view empl_v_sup calls a recursive function to determine if a given employee works for the current user (either directly, or anywhere under him in the company heirarchy). The view only includes employees that work under the that user. There is also an exception for users who have certain types of privileges who get to see the whole company. This dynamic view is very cool as it allows different people to see different data in the same view depending on who they are, and how the hierarchical data is arranged in the employee database. -- Determine if an employee has another employee as his supervisor. -- An employee is, by definition, not his own supervisor -- Returns true or false -- calling sequence: _empl_ancest(employee,ancestor,level) create function _empl_ancest(int4,int4,int4) returns boolean as ' declare trec record; begin if $3 > 15 then raise exception \'Supervisor loop found on employee %\', $1; end if; -- a person can not be his own supervisor -- also if null or 0, we reached top of the ladder so return false if $1 = $2 or $1 is null or $1 = 0 then return false; end if; -- get the employees record select * into trec from empl_pub where empl_id = $1; if not found then raise exception \'Record not found for employee %\', $1; end if; -- if he is his own supervisor, we have probably reached the top so false if trec.superv = $1 then return false; end if; -- if his supervisor is the ancestor, return true if trec.superv = $2 then return true; end if; -- else check the parent recursively return _empl_ancest(trec.superv, $2, $3+1); end;' language 'plpgsql'; -- Determine if an employee has another employee as his ancestor. -- This adds a level parm to prevent infinite recursion. -- calling sequence: empl_ancest(employee,ancestor) create function empl_ancest(int4,int4) returns boolean as ' select _empl_ancest($1,$2,0); ' language 'sql'; --View with limited privileges for supervisors to see their own people create view empl_v_sup as select *,oid as _oid from empl where exists (select * from priv where empl_id = getpguid() and ((priv = 'emplim' and alevel = 'super') or (priv = 'payroll'))) or empl_ancest(empl_id,getpguid()); --Only the emplim-super can insert records create rule empl_v_sup_innull as on insert to empl_v_sup do instead nothing; create rule empl_v_sup_insert as on insert to empl_v_sup where (select count(*) from priv where priv = 'emplim' and alevel = 'super' and empl_id = getpguid()) > 0 do instead insert into empl (empl_id,pertitle,surname,givnames,prefname,jobtitle,addr,city,state,zip,country,phone,workph,mobile,email,ssn,bday,hiredate,termdate,lrevdate,nrevdate,paytyp,empltyp,superv,proxy,status,mstat,payrate,allow,wccode,eic,cmt) values (new.empl_id,new.pertitle,new.surname,new.givnames,new.prefname,new.jobtitle,new.addr,new.city,new.state,new.zip,new.country,new.phone,new.workph,new.mobile,new.email,new.ssn,new.bday,new.hiredate,new.termdate,new.lrevdate,new.nrevdate,new.paytyp,new.empltyp,new.superv,new.proxy,new.status,new.mstat,new.payrate,new.allow,new.wccode,new.eic,new.cmt); --Emplim-super can update any field create rule empl_v_sup_upnull as on update to empl_v_sup do instead nothing; create rule empl_v_sup_update as on update to empl_v_sup where (select count(*) from priv where priv = 'emplim' and alevel = 'super' and empl_id = getpguid()) > 0 do instead update empl set empl_id = new.empl_id, pertitle = new.pertitle, surname = new.surname, givnames = new.givnames, prefname = new.prefname, jobtitle = new.jobtitle, addr = new.addr, city = new.city, state = new.state, zip = new.zip, country = new.country, phone = new.phone, workph = new.workph, mobile = new.mobile, email = new.email, ssn = new.ssn, bday = new.bday, hiredate = new.hiredate, termdate = new.termdate, lrevdate = new.lrevdate, nrevdate = new.nrevdate, paytyp = new.paytyp, empltyp where empl_id = old.empl_id; --Emplim-user can update certain fields create rule empl_v_sup_update1 as on update to empl_v_sup where (select count(*) from priv where priv = 'emplim' and alevel = 'user' and empl_id = getpguid()) > 0 and empl_ancest(old.empl_id,getpguid()) do instead update empl set pertitle = new.pertitle, prefname = new.prefname, jobtitle = new.jobtitle, phone = new.phone, workph = new.workph, mobile = new.mobile, email = new.email, lrevdate = new.lrevdate, nrevdate = new.nrevdate, payrate = new.payrate where empl_id = old.empl_id; --------------0A8B3006FFE8479D32A873ED Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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.
 

I have another interesting use of this same concept you may be interested in (or slightly nausious, as the case may be):

The underlying database (empl) contains all employees in the organization.  The view empl_v_sup calls a recursive function to determine if
a given employee works for the current user (either directly, or anywhere under him in the company heirarchy).  The view only includes
employees that work under the that user.  There is also an exception for users who have certain types of privileges who get to see the whole
company.

This dynamic view is very cool as it allows different people to see different data in the same view depending on who they are, and how the
hierarchical data is arranged in the employee database.

-- Determine if an employee has another employee as his supervisor.
-- An employee is, by definition, not his own supervisor
-- Returns true or false
-- calling sequence: _empl_ancest(employee,ancestor,level)
create function _empl_ancest(int4,int4,int4) returns boolean as '
    declare
        trec record;
    begin
        if $3 > 15 then
            raise exception \'Supervisor loop found on employee %\', $1;
        end if;

-- a person can not be his own supervisor
-- also if null or 0, we reached top of the ladder so return false
        if $1 = $2 or $1 is null or $1 = 0 then return false; end if;
 
-- get the employees record
        select * into trec from empl_pub where empl_id = $1;
        if not found then
            raise exception \'Record not found for employee %\', $1;
        end if;

-- if he is his own supervisor, we have probably reached the top so false
        if trec.superv = $1 then return false; end if;

-- if his supervisor is the ancestor, return true
        if trec.superv = $2 then return true; end if;

-- else check the parent recursively
        return _empl_ancest(trec.superv, $2, $3+1);
    end;' language 'plpgsql';

-- Determine if an employee has another employee as his ancestor.
-- This adds a level parm to prevent infinite recursion.
-- calling sequence: empl_ancest(employee,ancestor)
create function empl_ancest(int4,int4) returns boolean as '
    select _empl_ancest($1,$2,0);
    ' language 'sql';

--View with limited privileges for supervisors to see their own people
create view empl_v_sup as select *,oid as _oid from empl where
    exists (select * from priv where empl_id = getpguid() and ((priv = 'emplim' and alevel = 'super') or (priv = 'payroll'))) or
    empl_ancest(empl_id,getpguid());

--Only the emplim-super can insert records
create rule empl_v_sup_innull as on insert to empl_v_sup do instead nothing;
create rule empl_v_sup_insert as on insert to empl_v_sup where
    (select count(*) from priv where priv = 'emplim' and alevel = 'super' and empl_id = getpguid()) > 0
    do instead
    insert into empl (empl_id,pertitle,surname,givnames,prefname,jobtitle,addr,city,state,zip,country,phone,workph,mobile,email,ssn,bday,hiredate,termdate,lrevdate,nrevdate,paytyp,empltyp,superv,proxy,status,mstat,payrate,allow,wccode,eic,cmt)
    values (new.empl_id,new.pertitle,new.surname,new.givnames,new.prefname,new.jobtitle,new.addr,new.city,new.state,new.zip,new.country,new.phone,new.workph,new.mobile,new.email,new.ssn,new.bday,new.hiredate,new.termdate,new.lrevdate,new.nrevdate,new.paytyp,new.empltyp,new.superv,new.proxy,new.status,new.mstat,new.payrate,new.allow,new.wccode,new.eic,new.cmt);

--Emplim-super can update any field
create rule empl_v_sup_upnull as on update to empl_v_sup do instead nothing;
create rule empl_v_sup_update as on update to empl_v_sup where
    (select count(*) from priv where priv = 'emplim' and alevel = 'super' and empl_id = getpguid()) > 0
    do instead
    update empl set empl_id = new.empl_id, pertitle = new.pertitle, surname = new.surname, givnames = new.givnames, prefname = new.prefname, jobtitle = new.jobtitle, addr = new.addr, city = new.city, state = new.state, zip = new.zip, country = new.country, phone = new.phone, workph = new.workph, mobile = new.mobile, email = new.email, ssn = new.ssn, bday = new.bday, hiredate = new.hiredate, termdate = new.termdate, lrevdate = new.lrevdate, nrevdate = new.nrevdate, paytyp = new.paytyp, empltyp
    where empl_id = old.empl_id;

--Emplim-user can update certain fields
create rule empl_v_sup_update1 as on update to empl_v_sup where
    (select count(*) from priv where priv = 'emplim' and alevel = 'user' and empl_id = getpguid()) > 0 and
    empl_ancest(old.empl_id,getpguid())
    do instead
    update empl set pertitle = new.pertitle, prefname = new.prefname, jobtitle = new.jobtitle, phone = new.phone, workph = new.workph, mobile = new.mobile, email = new.email, lrevdate = new.lrevdate, nrevdate = new.nrevdate, payrate = new.payrate
    where empl_id = old.empl_id;
 
  --------------0A8B3006FFE8479D32A873ED-- --------------F695A53B7981BD4C1B4AB234 Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:kyle@actarg.com title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard --------------F695A53B7981BD4C1B4AB234-- From kyle@actarg.com Fri Jan 26 17:20:09 2001 Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0QHK9q25479 for ; Fri, 26 Jan 2001 12:20:09 -0500 (EST) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id KAA28870; Fri, 26 Jan 2001 10:20:09 -0700 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id KAA23225; Fri, 26 Jan 2001 10:20:22 -0700 Message-ID: <3A71B1D6.BD193BE4@actarg.com> Date: Fri, 26 Jan 2001 10:20:22 -0700 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: Jan Wieck CC: Tom Lane , pgsql-sql@postgresql.org Subject: Re: Rule not invoked in 7.1 References: <200101261652.LAA02743@jupiter.greatbridge.com> Content-Type: multipart/mixed; boundary="------------6662AE9AFF7C9F34EFD12F3E" X-Archive-Number: 200101/385 X-Sequence-Number: 1058 This is a multi-part message in MIME format. --------------6662AE9AFF7C9F34EFD12F3E Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Jan Wieck wrote: > Tom Lane wrote: > > > 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. That's right. > 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. This hadn't occurred to me but makes sense now that you say it. Taking that into consideration will make my job a bit simpler. The only complication is that there are a class of records which the user should be able to view, but not modify. For example, the employee can create and modify working records as long as the only modification to their status is to move them on to "open status" (creating an "approved" record would be a bad idea.) But the user should be able to view all their records (working, open, approved, and even paid). Hence, the restrictions on update are more stringent than those on select. --------------6662AE9AFF7C9F34EFD12F3E Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:kyle@actarg.com title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard --------------6662AE9AFF7C9F34EFD12F3E-- From janwieck@Yahoo.com Fri Jan 26 19:05:42 2001 Received: from smtp6.mail.yahoo.com (smtp6.mail.yahoo.com [128.11.69.103]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0QJ5gq36443 for ; Fri, 26 Jan 2001 14:05:42 -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 19:05:39 -0000 X-Apparently-From: Received: (from janwieck@localhost) by jupiter.greatbridge.com (8.9.3/8.9.3) id OAA03785 for pgsql-sql@PostgreSQL.org; Fri, 26 Jan 2001 14:15:35 -0500 From: Jan Wieck Message-Id: <200101261915.OAA03785@jupiter.greatbridge.com> Subject: Re: Rule not invoked in 7.1 To: PostgreSQL SQL Date: Fri, 26 Jan 2001 14:15:35 -0500 (EST) 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/393 X-Sequence-Number: 1066 Kyle wrote: > The only complication is > that there are a class of records which the user should be able to view, but not > modify. For example, > the employee can create and modify working records as long as the only > modification to their status > is to move them on to "open status" (creating an "approved" record would be a bad > idea.) > > But the user should be able to view all their records (working, open, approved, > and even paid). > > Hence, the restrictions on update are more stringent than those on select. Ah. Describe the entire problem and you'll get a complete answer: CREATE TABLE wr_table ( w_id serial PRIMARY KEY, w_user name, w_state text, w_data text ); CREATE CREATE VIEW wr_view AS SELECT * FROM wr_table WHERE w_user = CURRENT_USER; CREATE CREATE RULE wr_view_ins AS ON INSERT TO wr_view DO INSTEAD INSERT INTO wr_table (w_user, w_state, w_data) VALUES ( CURRENT_USER, 'OPEN', new.w_data ); CREATE CREATE RULE wr_view_upd AS ON UPDATE TO wr_view DO INSTEAD UPDATE wr_table SET w_data = new.w_data WHERE w_id = old.w_id AND w_state = 'OPEN'; CREATE CREATE RULE wr_view_del AS ON DELETE TO wr_view DO INSTEAD DELETE FROM wr_table WHERE w_id = old.w_id AND w_state = 'OPEN'; CREATE INSERT INTO wr_table (w_user, w_state, w_data) VALUES ('pgsql', 'OPEN', 'Open item 1 of pgsql'); INSERT 19392 1 INSERT INTO wr_table (w_user, w_state, w_data) VALUES ('pgsql', 'OPEN', 'Open item 2 of pgsql'); INSERT 19393 1 INSERT INTO wr_table (w_user, w_state, w_data) VALUES ('pgsql', 'CLOSED', 'Closed item 3 of pgsql'); INSERT 19394 1 INSERT INTO wr_table (w_user, w_state, w_data) VALUES ('someone', 'OPEN', 'Open item of someone else'); INSERT 19395 1 INSERT INTO wr_table (w_user, w_state, w_data) VALUES ('someone', 'CLOSED', 'Closed item of someone else'); INSERT 19396 1 SELECT CURRENT_USER; current_user -------------- pgsql (1 row) SELECT * FROM wr_table ORDER BY w_id; w_id | w_user | w_state | w_data ------+---------+---------+----------------------------- 1 | pgsql | OPEN | Open item 1 of pgsql 2 | pgsql | OPEN | Open item 2 of pgsql 3 | pgsql | CLOSED | Closed item 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED | Closed item of someone else (5 rows) UPDATE wr_view SET w_data = 'Changed item 2 of pgsql' WHERE w_id = 2; UPDATE 1 SELECT * FROM wr_table ORDER BY w_id; w_id | w_user | w_state | w_data ------+---------+---------+----------------------------- 1 | pgsql | OPEN | Open item 1 of pgsql 2 | pgsql | OPEN | Changed item 2 of pgsql 3 | pgsql | CLOSED | Closed item 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED | Closed item of someone else (5 rows) UPDATE wr_view SET w_data = 'Changed item of someone else' WHERE w_id = 4; UPDATE 0 SELECT * FROM wr_table ORDER BY w_id; w_id | w_user | w_state | w_data ------+---------+---------+----------------------------- 1 | pgsql | OPEN | Open item 1 of pgsql 2 | pgsql | OPEN | Changed item 2 of pgsql 3 | pgsql | CLOSED | Closed item 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED | Closed item of someone else (5 rows) UPDATE wr_view SET w_data = 'Changed item 3 of pgsql' WHERE w_id = 3; UPDATE 0 SELECT * FROM wr_table ORDER BY w_id; w_id | w_user | w_state | w_data ------+---------+---------+----------------------------- 1 | pgsql | OPEN | Open item 1 of pgsql 2 | pgsql | OPEN | Changed item 2 of pgsql 3 | pgsql | CLOSED | Closed item 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED | Closed item of someone else (5 rows) DELETE FROM wr_view; DELETE 2 SELECT * FROM wr_table ORDER BY w_id; w_id | w_user | w_state | w_data ------+---------+---------+----------------------------- 3 | pgsql | CLOSED | Closed item 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED | Closed item of someone else (3 rows) INSERT INTO wr_view VALUES (99, 'someone', 'CLOSED', 'Meant for someone'); INSERT 19397 1 SELECT * FROM wr_table ORDER BY w_id; w_id | w_user | w_state | w_data ------+---------+---------+----------------------------- 3 | pgsql | CLOSED | Closed item 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED | Closed item of someone else 6 | pgsql | OPEN | Meant for someone (4 rows) 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