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/