Hi,I'm not sure if the following is a bug. I certainly found itsurprising, but maybe more experienced users won't.I
havea table with a trigger on it, designed to run securityinvoker. In my real code this accesses a temporary table
belongingtothe invoker.Then I have second table, together with a foreign key between them anda delete cascade from the
secondto the first table. It appears thatwhen I delete from this second table, the delete cascades as expected,but the
triggeris invoked as if it were security definer, which Ididn't expect.I've attached a short made-up test script. The
deletefrom 'bar'works, and the trigger logs to the temporary table. However, thedelete from 'foo' fails, unless I grant
user1access to 'temp_log'.By playing with the trigger, it is possible to confirm that thetrigger really is running with
thepermissions 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;CREATEUSER user1;CREATE USER user2;-- First user\c - user1CREATE TABLE foo(a int PRIMARY KEY);CREATE TABLE bar(a
intREFERENCES foo ON DELETE CASCADE);CREATE OR REPLACE FUNCTION bar_log_fn() RETURNS trigger AS$$BEGIN EXECUTE 'INSERT
INTOtemp_log VALUES(''Deleting from bar'')'; RETURN OLD;END;$$LANGUAGE plpgsql;CREATE TRIGGER bar_del_trigger BEFORE
DELETEON bar FOR EACH ROW EXECUTE PROCEDURE bar_log_fn();GRANT SELECT,INSERT,UPDATE,DELETE ON foo TO user2;GRANT
SELECT,INSERT,UPDATE,DELETEON bar TO user2;-- Second user\c - user2CREATE TEMPORARY TABLE temp_log(log text);INSERT
INTOfoo 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/