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

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

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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-02-27 23:10:39
Subject: Re: [PATCH] Documentation: remove confusing paragraph about backslash escaping
Previous:From: Tom LaneDate: 2012-02-27 22:51:49
Subject: Re: default values in inheritance hierarchies

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