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

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

> Alvaro Herrera <alvherre(at)commandprompt(dot)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