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

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