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

On Friday 10 October 2008 1:57:28 pm Adrian Klaver wrote:
> 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

Oops the above is wrong. In testing I used a login role that automatically
inherited the privileged role I was using below. Using a different login role
showed me the error.

>
> 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;

The above would still work as long as the privileged role(user) was not
assigned to normal users and the privileged role had DELETE rights to the
table. Also the function would need to be created with the privileges
necessary to do the SET ROLE.

> --
> Adrian Klaver
> aklaver(at)comcast(dot)net

--
Adrian Klaver
aklaver(at)comcast(dot)net