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

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