Thread: Re: Problem with delete trigger: how to allow only triggers to delete a row?

-------------- Original message ----------------------
From: "Christopher Maier" <maier@med.unc.edu>
> I have a table where some rows are originally entered, and others are  
> logically deduced from original rows by triggers.  For instance, if  
> one row contains data indicating that "A < B" and another row  
> indicates "B < C", the triggers will generate a new row indicating  
> that "A < C".  All deduced rows have a boolean attribute (named  
> "deduced") that is TRUE only if the row was generated by such a  
> deduction.  A value of FALSE indicates that the row was original data,  
> entered by a user.
> 
> When original data is modified, the triggers are responsible for  
> removing any deduced rows that are now invalid and generating new rows  
> that are now implied.  I would like to make it so that the only way  
> that deduced rows can be deleted is through the actions of these  
> triggers; I don't want a user inadvertently deleting a deduction when  
> the underlying premises (the original rows that were used to generate  
> the deduced rows) still imply that deduction is valid.  Users should  
> only be able to manipulate the original data.
> 
> I can create a trigger that will prevent deletion of deduced rows  
> easily enough, but I'm not sure how to let rows targeted for deletion  
> by these deduction triggers through.  Is there a way to pass some sort  
> of state indicator into a trigger?  Is this at all possible?
> 
> Thanks in advance,
> Chris
> 
> 
> -- 

From the manual http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html:

Row-level triggers fired BEFORE may return null to signal the trigger manager to skip the rest of the operation for
thisrow (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a
nonnullvalue is returned then the operation proceeds with that row value
 

Could you have the the trigger examine the row to see if it meets the criteria for deletion. If it does RETURN a NON
NULLvalue so the trigger completes, otherwise RETURN NULL to prevent the DELETE.
 

--
Adrian Klaver
aklaver@comcast.net

On Oct 10, 2008, at 1:23 PM, Adrian Klaver wrote:
> Could you have the the trigger examine the row to see if it meets  
> the criteria for deletion. If it does RETURN a NON NULL value so the  
> trigger completes, otherwise RETURN NULL to prevent the DELETE.

Thanks for your reply, Adrian.  This is indeed part of the solution.   
My problem concerns the determination of the criteria for deletion.   
Each row has a boolean attribute that says whether it is deduced or  
not and that can be inspected readily enough.  However, I need to  
restrict deletion based on, for lack of a better term, "where" the  
DELETE command comes from.  I do not want someone sitting at a psql  
console to be able to type:

DELETE FROM my_table WHERE deduced IS TRUE;

This should fail because users should only be able to delete non- 
deduced rows.  However, when a user deletes a non-deduced row, my  
triggers issue DELETE commands for all deduced rows that are logically  
derived from that non-deduced row.  Back to the example from my  
original post, if I have two rows in the table that say, in effect:

A < B
B < C

then the triggers will generate a row that says "A < C".  If the user  
then deletes the "B < C" row, the triggers will delete the "A < C"  
row, because there is no longer any support for this.  I need to  
figure out how to block DELETEs from the user, while allowing DELETEs  
that come from the triggers.  If I could pass along some kind of flag  
or parameter with the DELETE commands issued from the triggers, then  
that might do it, but my understanding of trigger parameters is that  
they are the same for all invocations on a particular table, whereas I  
need them to be different for each call of the trigger.

I'm currently using plpgsql, but I'm open to other PL languages or  
architectural restructurings if that's necessary.  I'm just stumped as  
to how to achieve this effect.

I hope that clarifies things.

Thanks again,

Chris

Christopher Maier wrote:

> However, I need to restrict deletion based on, for lack of a better
> term, "where" the DELETE command comes from.  I do not want someone
> sitting at a psql console to be able to type:
>
> DELETE FROM my_table WHERE deduced IS TRUE;

Looks like you should revoke DELETE privilege from plain users, and
have your delete trigger be a security definer function.  There would be
another security definer function to delete non-deduced rows which users
can call directly.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Oct 10, 2008, at 2:05 PM, Alvaro Herrera wrote:

> Looks like you should revoke DELETE privilege from plain users, and
> have your delete trigger be a security definer function.  There  
> would be
> another security definer function to delete non-deduced rows which  
> users
> can call directly.

Thanks, Alvaro.  So you're suggesting I create a function like this:

CREATE FUNCTION user_delete(identifier my_table.id%TYPE) RETURNS VOID  
LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN...-- do various checks...DELETE FROM my_table WHERE id = identifier;...
END;
$$;

Correct?  That sounds like it would work.  If at all possible, I'd  
like to keep the "interface" the same for all my tables, though (i.e.,  
users don't have to be concerned with whether they can do regular SQL  
deletes, or if they have to call a special function).  I suppose that  
can ultimately be hidden, though.

I will try this approach and see how it works out.  If there is any  
other way to achieve this goal, however, I would be interested to hear.

Thanks again.

--Chris

Christopher Maier wrote:

> Correct?  That sounds like it would work.  If at all possible, I'd like 
> to keep the "interface" the same for all my tables, though (i.e., users 
> don't have to be concerned with whether they can do regular SQL deletes, 
> or if they have to call a special function).

Hmm, maybe you can turn a regular DELETE into a function call by using
an INSTEAD rule, but I'm not sure.  That way they would just do a plain
DELETE and the sec-def function would be called instead.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Looks like you should revoke DELETE privilege from plain users, and
> have your delete trigger be a security definer function.  There would be
> another security definer function to delete non-deduced rows which users
> can call directly.

That seems overly complicated to use.

If the triggers that are privileged to delete deduced rows run as a
special user, couldn't the validation triggers look at CURRENT_USER
to see whether to allow the delete of a deduced row or not?
        regards, tom lane

On Friday 10 October 2008 11:25:05 am Christopher Maier wrote:
> On Oct 10, 2008, at 2:05 PM, Alvaro Herrera wrote:
> > Looks like you should revoke DELETE privilege from plain users, and
> > have your delete trigger be a security definer function.  There
> > would be
> > another security definer function to delete non-deduced rows which
> > users
> > can call directly.
>
> Thanks, Alvaro.  So you're suggesting I create a function like this:
>
> CREATE FUNCTION user_delete(identifier my_table.id%TYPE) RETURNS VOID
> LANGUAGE plpgsql SECURITY DEFINER AS $$
> BEGIN
>     ...
>     -- do various checks
>     ...
>     DELETE FROM my_table WHERE id = identifier;
>     ...
> END;
> $$;
>
> Correct?  That sounds like it would work.  If at all possible, I'd
> like to keep the "interface" the same for all my tables, though (i.e.,
> users don't have to be concerned with whether they can do regular SQL
> deletes, or if they have to call a special function).  I suppose that
> can ultimately be hidden, though.
>
> I will try this approach and see how it works out.  If there is any
> other way to achieve this goal, however, I would be interested to hear.
>
> Thanks again.
>
> --Chris

A possible approach, not fully tested.
REVOKE DELETE from normal users as suggested above.
GRANT DELETE to privileged_user

Semi psuedo-code below.

CREATE OR REPLACE FUNCTION  check_delete RETURNS TRIGGER AS 
$Body$
BEGINIF current_user != 'privileged_user' AND old.deduced = 'f' THEN    SET LOCAL ROLE  'privileged_user';    --Do your
sanitychecks and create DELETE statements    RETURN OLD;ELSIF current_user != 'privileged_user' AND old.deduced ='t'
THEN   RETURN NULL;ELSIF current_user = 'priviliged_user' THEN    RETURN OLD
 


END;

$Body$
LANGUAGE plpgsql;
-- 
Adrian Klaver
aklaver@comcast.net

On Oct 10, 2008, at 4:53 PM, Tom Lane wrote:

> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Looks like you should revoke DELETE privilege from plain users, and
>> have your delete trigger be a security definer function.  There  
>> would be
>> another security definer function to delete non-deduced rows which  
>> users
>> can call directly.
>
> That seems overly complicated to use.
>
> If the triggers that are privileged to delete deduced rows run as a
> special user, couldn't the validation triggers look at CURRENT_USER
> to see whether to allow the delete of a deduced row or not?
>
>             regards, tom lane

That sounds like the best approach, Tom.  I've already implemented  
Alvaro's suggestion, which works nicely.  It should be a simple matter  
to add in the current_user check.  I'll give that a whirl and see how  
it goes.

Thanks for all the great suggestions, everyone.

Chris

On Friday 10 October 2008 1:57:28 pm Adrian Klaver wrote:
> On Friday 10 October 2008 11:25:05 am Christopher Maier wrote:
> > On Oct 10, 2008, at 2:05 PM, Alvaro Herrera wrote:
> > > Looks like you should revoke DELETE privilege from plain users, and
> > > have your delete trigger be a security definer function.  There
> > > would be
> > > another security definer function to delete non-deduced rows which
> > > users
> > > can call directly.
> >
> > Thanks, Alvaro.  So you're suggesting I create a function like this:
> >
> > CREATE FUNCTION user_delete(identifier my_table.id%TYPE) RETURNS VOID
> > LANGUAGE plpgsql SECURITY DEFINER AS $$
> > BEGIN
> >     ...
> >     -- do various checks
> >     ...
> >     DELETE FROM my_table WHERE id = identifier;
> >     ...
> > END;
> > $$;
> >
> > Correct?  That sounds like it would work.  If at all possible, I'd
> > like to keep the "interface" the same for all my tables, though (i.e.,
> > users don't have to be concerned with whether they can do regular SQL
> > deletes, or if they have to call a special function).  I suppose that
> > can ultimately be hidden, though.
> >
> > I will try this approach and see how it works out.  If there is any
> > other way to achieve this goal, however, I would be interested to hear.
> >
> > Thanks again.
> >
> > --Chris
>
> A possible approach, not fully tested.
> REVOKE DELETE from normal users as suggested above.
> GRANT DELETE to privileged_user

Oops the above is wrong. In testing I used a login role that automatically 
inherited the privileged role I was using below. Using a different login role 
showed me the error. 

>
> Semi psuedo-code below.
>
> CREATE OR REPLACE FUNCTION  check_delete RETURNS TRIGGER AS
> $Body$
> BEGIN
>     IF current_user != 'privileged_user' AND old.deduced = 'f' THEN
>         SET LOCAL ROLE  'privileged_user';
>         --Do your sanity checks and create DELETE statements
>         RETURN OLD;
>     ELSIF current_user != 'privileged_user' AND old.deduced ='t' THEN
>         RETURN NULL;
>     ELSIF current_user = 'priviliged_user' THEN
>         RETURN OLD
>
>
> END;
>
> $Body$
> LANGUAGE plpgsql;

The above would still work as long as the privileged role(user) was not 
assigned to normal users and the privileged role had DELETE rights to the 
table. Also the function would need to be created with the privileges 
necessary to do the SET ROLE.


> --
> Adrian Klaver
> aklaver@comcast.net



-- 
Adrian Klaver
aklaver@comcast.net

On Oct 10, 2008, at 5:16 PM, Christopher Maier wrote:

>
> On Oct 10, 2008, at 4:53 PM, Tom Lane wrote:
>
>> Alvaro Herrera <alvherre@commandprompt.com> writes:
>>> Looks like you should revoke DELETE privilege from plain users, and
>>> have your delete trigger be a security definer function.  There  
>>> would be
>>> another security definer function to delete non-deduced rows which  
>>> users
>>> can call directly.
>>
>> That seems overly complicated to use.
>>
>> If the triggers that are privileged to delete deduced rows run as a
>> special user, couldn't the validation triggers look at CURRENT_USER
>> to see whether to allow the delete of a deduced row or not?
>>
>>             regards, tom lane
>
> That sounds like the best approach, Tom.  I've already implemented  
> Alvaro's suggestion, which works nicely.  It should be a simple  
> matter to add in the current_user check.  I'll give that a whirl and  
> see how it goes.
>
> Thanks for all the great suggestions, everyone.
>
> Chris

Just for completeness, and for posterity, this solution (checking for  
CURRENT_USER) works great.  I don't need to revoke DELETE privileges  
from anyone; simply define all my triggers that kick off a DELETE  
operation as SECURITY DEFINER (created by my privileged user role),  
and then have a BEFORE DELETE trigger that compares the value of  
CURRENT_USER to this privileged user's name.  Works great, and is very  
easy to implement.

Thanks for the help!

--Chris