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

Re: Triggers with DO functionality

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Peter Eisentraut" <peter_e(at)gmx(dot)net>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Dimitri Fontaine" <dimitri(at)2ndquadrant(dot)fr>, "Andrew Dunstan" <andrew(at)dunslane(dot)net>,"Thom Brown" <thom(at)linux(dot)com>, "PostgreSQL-development" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Triggers with DO functionality
Date: 2012-02-24 19:55:01
Message-ID: 4F4796B50200002500045B3F@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Christopher BrowneDate: 2012-02-24 20:01:01
Subject: Re: Triggers with DO functionality
Previous:From: Peter EisentrautDate: 2012-02-24 19:40:31
Subject: Re: Triggers with DO functionality

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