On Friday 10 October 2008 11:25:05 am Christopher Maier wrote: > 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
A possible approach, not fully tested. REVOKE DELETE from normal users as suggested above. GRANT DELETE to privileged_user Semi psuedo-code below. CREATE OR REPLACE FUNCTION check_delete RETURNS TRIGGER AS $Body$ BEGIN IF current_user != 'privileged_user' AND old.deduced = 'f' THEN SET LOCAL ROLE 'privileged_user'; --Do your sanity checks and create DELETE statements RETURN OLD; ELSIF current_user != 'privileged_user' AND old.deduced ='t' THEN RETURN NULL; ELSIF current_user = 'priviliged_user' THEN RETURN OLD END; $Body$ LANGUAGE plpgsql; -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql