Re: Trigger execution role (was: Triggers with DO functionality)

On Mon, Feb 27, 2012 at 6:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> As far as I can tell, triggers run as the user performing the
>> operation which fires the trigger, not as the owner of the table.
>  > Can anyone provide an example of a trigger running as the table
>> owner?  Is there a bug here?  Something for the docs?
>
> A quick look into trigger.c shows that there is no attempt to switch
> current userid, so we were clearly all wrong about that.  Not sure
> why everyone recollected the opposite.
>
> On reflection, there's a fairly clear reason why not to switch userid:
> it would break triggers that do something like what's shown in the very
> first example in the plpgsql trigger documentation:
>
>        -- Remember who changed the payroll when
>        NEW.last_date := current_timestamp;
>        NEW.last_user := current_user;
>        RETURN NEW;
>
> So, whatever the desirability of having them run as table owner,
> we can't just up and change that.  At minimum we'd need to provide
> some function to get at the "calling userid" (or perhaps make that
> a new trigger argument?) and have a reasonable grace period for
> people to change over to using that.

I'm inclined to hold to the argument that it Works Properly Now, and
that we shouldn't break it by changing it.

The user *can* be changed, by running a security definer trigger function.

The behaviour that is under consideration seems to be to use something
akin to "security definer as table owner".  If someone *wants* that,
then they can readily accomplish that TODAY by altering the function
to make it a SECURITY DEFINER, and change owner to the table owner.

But if we change to have that be the default, it's nowhere near as
easy to unravel it, and to get to the situation where the trigger runs
with the security context of the user that ran the query.  SECURITY
DEFINER is more static than that.

> This might be something to consider in the adjacent thread about command
> triggers, too --- who do they run as, and if it's not the calling user,
> how do they find out who that is?

I'm inclined to hold to the same position on that.

- If you *WANT* the command trigger to run as user "frotz", then have
it be a security definer function owned by "frotz."
- Otherwise, it runs with the privileges of the calling user.

That doesn't seem woefully wrong to me.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"