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
