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

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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.

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?

regards, tom lane