Обсуждение: Rule not invoked in 7.1
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?
Вложения
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.
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
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. 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
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.
Вложения
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.
regards, tom lane
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
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. > 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.
Вложения
Kyle <kyle@actarg.com> writes:I have another interesting use of this same concept you may be interested in (or slightly nausious, as the case may be):
> 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 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;
Вложения
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.
Вложения
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_tableSET w_data = new.w_data WHERE w_id = old.w_id AND w_state = 'OPEN'; CREATE CREATE RULE
wr_view_delAS 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',
'Openitem 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
INTOwr_table (w_user, w_state, w_data) VALUES ('someone', 'CLOSED', 'Closed item of someone else');
INSERT19396 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 *
FROMwr_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 *
FROMwr_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 |
Closeditem 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED |
Closeditem of someone else (3 rows)
INSERT INTO wr_view VALUES (99, 'someone', 'CLOSED', 'Meant for someone'); INSERT 19397 1 SELECT *
FROMwr_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