Re: [HACKERS] Trigger execution role (was: Triggers with DO functionality)
Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: By default, a trigger function runs as the table owner, ie it's implicitly SEC DEF to the table owner. Really? That's certainly what I would *want*, but it's not what I've seen. [self-contained example of that not happening] Christopher Browne cbbro...@gmail.com wrote: Yeah, not quite consistent with what I've seen. Peter Eisentraut pete...@gmx.net wrote: Yes, you're right 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? Test case (slightly modified) in runnable format, rather than a copy/paste of a run: create user bob; create user ted; -- set role bob; create table t (id int not null primary key, val text); create table s (id int not null primary key, val text not null); grant select, insert, update, delete on t to ted; grant select on s to ted; create function t_ins_func() returns trigger language plpgsql as $$ begin raise notice 'role = ''%''', current_user; if new.val is not null then insert into s (id, val) values (new.id, new.val); end if; return new; end; $$; create trigger t_ins_trig before insert on t for each row execute procedure t_ins_func(); -- reset role; set role ted; insert into t values (1, null); select * from s; select * from t; insert into t values (2, 'two'); -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Trigger execution role (was: Triggers with DO functionality)
Kevin Grittner kevin.gritt...@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. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Trigger execution role (was: Triggers with DO functionality)
On Mon, Feb 27, 2012 at 6:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Trigger execution role (was: Triggers with DO functionality)
Christopher Browne cbbro...@gmail.com writes: On Mon, Feb 27, 2012 at 6:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: So, whatever the desirability of having them run as table owner, we can't just up and change that. I'm inclined to hold to the argument that it Works Properly Now, and that we shouldn't break it by changing it. I would say the same, or at least that any argument for changing it is probably not strong enough to trump backwards compatibility. However, Peter seems to think the other way is required by standard. We can get away with defining whatever behavior we want for triggers that invoke functions, since that syntax is nonstandard anyway. But, if you remember the original point of this thread, it was to add syntax that is pretty nearly equivalent to the spec's. If we're going to do that, it had better also have semantics similar to the spec's. So (assuming Peter has read the spec correctly) I'm coming around to the idea that the anonymous trigger functions created by this syntax ought to be SECURITY DEFINER table_owner. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Trigger execution role (was: Triggers with DO functionality)
Excerpts from Tom Lane's message of lun feb 27 20:49:36 -0300 2012: So (assuming Peter has read the spec correctly) I'm coming around to the idea that the anonymous trigger functions created by this syntax ought to be SECURITY DEFINER table_owner. I don't remember all the details, but I had a look at this in the standard about a year ago and the behavior it mandated wasn't trivially implemented using our existing mechanism. I mentioned the issue of a stack of user authorizations that is set up whenever a routine (function) is entered, during last year's PGCon developer's meeting. I intended to have a look at implementing that, but I haven't done anything yet. What was clear to me was that once I explained the problem, everyone seemed to agree that fixing it required more than some trivial syntax rework. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Trigger execution role (was: Triggers with DO functionality)
2012/2/28 Tom Lane t...@sss.pgh.pa.us: Christopher Browne cbbro...@gmail.com writes: On Mon, Feb 27, 2012 at 6:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: So, whatever the desirability of having them run as table owner, we can't just up and change that. I'm inclined to hold to the argument that it Works Properly Now, and that we shouldn't break it by changing it. I would say the same, or at least that any argument for changing it is probably not strong enough to trump backwards compatibility. +1 However, Peter seems to think the other way is required by standard. We can get away with defining whatever behavior we want for triggers that invoke functions, since that syntax is nonstandard anyway. But, if you remember the original point of this thread, it was to add syntax that is pretty nearly equivalent to the spec's. If we're going to do that, it had better also have semantics similar to the spec's. So (assuming Peter has read the spec correctly) I'm coming around to the idea that the anonymous trigger functions created by this syntax ought to be SECURITY DEFINER table_owner. It should be strange if using two forms of one code can have two relative different behave. Actually we are in opposition to spec, because it expect SECURITY DEFINER for all stored procedures. All logic about rights are consistent now and I am not for changes in this area. Regards Pavel regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
