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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql