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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: "Christopher Browne" <cbbrowne(at)gmail(dot)com>, "Peter Eisentraut" <peter_e(at)gmx(dot)net>, "PostgreSQL-development" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Trigger execution role (was: Triggers with DO functionality)
Date: 2012-02-27 23:20:20
Message-ID: 21298.1330384820@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
"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

In response to

Responses

pgsql-hackers by date

Next:From: Andres FreundDate: 2012-02-27 23:26:05
Subject: Re: Command Triggers
Previous:From: Tom LaneDate: 2012-02-27 23:10:39
Subject: Re: [PATCH] Documentation: remove confusing paragraph about backslash escaping

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