Hi,I'm not sure if the following is a bug. I certainly found itsurprising, but 
maybe more experienced users won't.I have a table with a trigger on it, 
designed to run securityinvoker. In my real code this accesses a temporary 
table belonging tothe invoker.Then I have second table, together with a foreign 
key between them anda delete cascade from the second to the first table. It 
appears thatwhen I delete from this second table, the delete cascades as 
expected,but the trigger is invoked as if it were security definer, which 
Ididn't expect.I've attached a short made-up test script. The delete from 
'bar'works, and the trigger logs to the temporary table. However, thedelete 
from 'foo' fails, unless I grant user1 access to 'temp_log'.By playing with the 
trigger, it is possible to confirm that thetrigger really is running with the 
permissions of user1, when it isinvoked via the delete cascade, but as user2 
otherwise.Is this expected behaviour?Dean.-- Need 2 users\c - postgresDROP 
OWNED BY user1;DROP OWNED BY user2;DROP USER user1;DROP USER user2;CREATE USER 
user1;CREATE USER user2;-- First user\c - user1CREATE TABLE foo(a int PRIMARY 
KEY);CREATE TABLE bar(a int REFERENCES foo ON DELETE CASCADE);CREATE OR REPLACE 
FUNCTION bar_log_fn() RETURNS trigger AS$$BEGIN  EXECUTE 'INSERT INTO temp_log 
VALUES(''Deleting from bar'')';  RETURN OLD;END;$$LANGUAGE plpgsql;CREATE 
TRIGGER bar_del_trigger BEFORE DELETE ON bar  FOR EACH ROW EXECUTE PROCEDURE 
bar_log_fn();GRANT SELECT,INSERT,UPDATE,DELETE ON foo TO user2;GRANT 
SELECT,INSERT,UPDATE,DELETE ON bar TO user2;-- Second user\c - user2CREATE 
TEMPORARY TABLE temp_log(log text);INSERT INTO foo VALUES(1),(2);INSERT INTO 
bar VALUES(1),(2);DELETE FROM bar WHERE a=1;DELETE FROM foo WHERE a=2;SELECT * 
FROM temp_log;
_________________________________________________________________
See the most popular videos on the web 
http://clk.atdmt.com/GBL/go/115454061/direct/01/
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to