From: | "Christopher Maier" <maier(at)med(dot)unc(dot)edu> |
---|---|
To: | "Christopher Maier" <maier(at)med(dot)unc(dot)edu> |
Cc: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>, aklaver(at)comcast(dot)net, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Problem with delete trigger: how to allow only triggers to delete a row? |
Date: | 2008-10-13 18:21:15 |
Message-ID: | 09A2A3B7-4CC0-4843-9692-85C24E6FA6D2@med.unc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Oct 10, 2008, at 5:16 PM, Christopher Maier wrote:
>
> On Oct 10, 2008, at 4:53 PM, Tom Lane wrote:
>
>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>>> 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.
>>
>> That seems overly complicated to use.
>>
>> If the triggers that are privileged to delete deduced rows run as a
>> special user, couldn't the validation triggers look at CURRENT_USER
>> to see whether to allow the delete of a deduced row or not?
>>
>> regards, tom lane
>
> That sounds like the best approach, Tom. I've already implemented
> Alvaro's suggestion, which works nicely. It should be a simple
> matter to add in the current_user check. I'll give that a whirl and
> see how it goes.
>
> Thanks for all the great suggestions, everyone.
>
> Chris
Just for completeness, and for posterity, this solution (checking for
CURRENT_USER) works great. I don't need to revoke DELETE privileges
from anyone; simply define all my triggers that kick off a DELETE
operation as SECURITY DEFINER (created by my privileged user role),
and then have a BEFORE DELETE trigger that compares the value of
CURRENT_USER to this privileged user's name. Works great, and is very
easy to implement.
Thanks for the help!
--Chris
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-10-13 18:30:11 | Re: Order of trigger execution: AFTER DELETE ON EACH ROW doesn't appear to be working properly |
Previous Message | Christopher Maier | 2008-10-13 18:15:49 | Order of trigger execution: AFTER DELETE ON EACH ROW doesn't appear to be working properly |