Re: Triggers with DO functionality

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.

test=# create user bob;
CREATE ROLE
test=# create user ted;
CREATE ROLE
test=# alter database test owner to bob;
ALTER DATABASE
test=# set role bob;
SET
test=> create table t (id int not null primary key, val text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey"
for table "t"
CREATE TABLE
test=> create table s (id int not null primary key, val text not
null);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "s_pkey"
for table "s"
CREATE TABLE
test=> grant select, insert, update, delete on t to ted;
GRANT
test=> grant select on s to ted;
GRANT
test=> create function t_ins_func() returns trigger language plpgsql as
$$
test$> begin
test$> if new.val is not null then
test$> insert into s (id, val) values (new.id, new.val);
test$> end if;
test$> return new;
test$> end;
test$> $$;
CREATE FUNCTION
test=> create trigger t_ins_trig before insert on t for each row
execute procedure t_ins_func();
CREATE TRIGGER
test=> reset role; set role ted;
RESET
SET
test=> insert into t values (1, null);
INSERT 0 1
test=> select * from s;
id | val
----+-----
(0 rows)

test=> select * from t;
id | val
----+-----
1 |
(1 row)

test=> insert into t values (2, 'two');
ERROR: permission denied for relation s
CONTEXT: SQL statement "insert into s (id, val) values (new.id,
new.val)"
PL/pgSQL function t_ins_func() line 4 at SQL statement

-Kevin