Skip site navigation (1) Skip section navigation (2)

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

From: Adrian Klaver <aklaver(at)comcast(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Cc: "Christopher Maier" <maier(at)med(dot)unc(dot)edu>, "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>
Subject: Re: Problem with delete trigger: how to allow only triggers to delete a row?
Date: 2008-10-10 22:10:27
Message-ID: 200810101510.27931.aklaver@comcast.net (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Denis WoodburyDate: 2008-10-11 11:22:09
Subject: EXISTS
Previous:From: Christopher MaierDate: 2008-10-10 21:16:28
Subject: Re: Problem with delete trigger: how to allow only triggers to delete a row?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group