Sorry, Opera removed all the newlines from my last post.
Trying again in Firefox...
Hi,
I'm not sure if the following is a bug. I certainly found it
surprising, but maybe more experienced users won't.
I have a table with a trigger on it, designed to run security
invoker. In my real code this accesses a temporary table belonging to
the invoker.
Then I have second table, together with a foreign key between them and
a delete cascade from the second to the first table. It appears that
when I delete from this second table, the deletes cascade as expected,
but the trigger is invoked as if it were security definer, which I
didn'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, the
delete from 'foo' fails, unless I grant user1 access to 'temp_log'.
By playing with the trigger, it is possible to confirm that the
trigger really is running with the permissions of user1, when it is
invoked via the delete cascade, but as user2 otherwise.
Is this expected behaviour?
Dean.
-- Need 2 users
\c - postgres
DROP OWNED BY user1;
DROP OWNED BY user2;
DROP USER user1;
DROP USER user2;
CREATE USER user1;
CREATE USER user2;
-- First user
\c - user1
CREATE 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 - user2
CREATE 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;
_________________________________________________________________
Win £1000 John Lewis shopping sprees with BigSnapSearch.com
http://clk.atdmt.com/UKM/go/117442309/direct/01/
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
