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.
 

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;