Re: Problem with delete trigger: how to allow only triggers to delete a row? - Mailing list pgsql-sql

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