Thread: Delete cascade trigger runs security definer
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/
Dean Rasheed <dean_rasheed@hotmail.com> writes:
> 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.
Referential integrity actions execute as the owner of the table, so
anything triggered by them would execute as the owner too.
regards, tom lane
Tom Lane wrote:
> Dean Rasheed <dean_rasheed@hotmail.com> writes:
>> 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.
>
> Referential integrity actions execute as the owner of the table, so
> anything triggered by them would execute as the owner too.
Is the search path in any way reset for this?
I tried to trick a trigger function that was fired by an ON DELETE
CASCADE into running a replacement for a commonly used function by
putting a malicious new definition on the search path before the safe
one. The malicious function tries to drop a table that the user issuing
the DELETE that triggers the cascade does not have the rights to drop
themselves.
The safe definition is still run instead of the malicious function,
whether I invoke the test function "looks_safe(INTEGER) returns integer" as:
PERFORM looks_safe(4);
or as:
EXECUTE 'SELECT looks_safe(4)';
from within the trigger function when it's invoked via an ON DELETE
CASCADE. When invoked directly with a delete on the table containing the
trigger, the malicious function is run instead (but there's no privilege
escalation happening, so it just fails).
The search path within the trigger is shown to list the schema
containing the malicious function before the schema containing the
legitimate version whether the trigger is invoked by a direct delete or
via a cascaded delete. The same results are seen with:
raise notice 'path: %',pg_catalog.current_setting('search_path');
and:
execute 'show search_path' into sp;
raise notice 'Path2: %',sp;
Yet the search path being reported seems to be ignored if the trigger is
invoked via an ON DELETE CASCADE.
Is the search_path reset in some way that's not visible in
pg_catalog.pg_settings when the ON DELETE CASCADE is issued? Is this
documented anywhere? I'm glad to see that there doesn't seem to be a
priv. escalation issue, but a little puzzled about how exactly the
search_path works within functions invoked via ON DELETE CASCADE triggers.
This doesn't seem to be the same effect as is seen with the SET
parameter for functions (particularly SECURITY DEFINER functions) when
used with search_path. If SET search_path = 'whatever' is used in a
SECURITY DEFINER function any functions called by it see the new
search_path using current_setting('search_path') or `show search_path'.
By contrast, when invoked via an ON DELETE CASCADE trigger the search
path seems to be somehow overridden without the actual visible value
being changed.
Anyone able to enlighten me about what's going on here?
--
Craig Ringer
Craig Ringer <craig@postnewspapers.com.au> writes:
> Is the search_path reset in some way that's not visible in
> pg_catalog.pg_settings when the ON DELETE CASCADE is issued?
No, I don't believe so. Perhaps your test case was simply fooled by
plan caching within the trigger function?
In general the solution to this type of problem is to attach a
search_path setting to any function that might be invoked via untrusted
users.
regards, tom lane
> 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/
Dean Rasheed <dean_rasheed@hotmail.com> writes:
>> Referential integrity actions execute as the owner of the table, so
>> anything triggered by them would execute as the owner too.
> Hmm, that opens up a very nasty gotcha, as shown by the script
> below. What user1 does looks, at first sight, fairly innocuous.
Well, granting TRIGGER on one of your tables is never innocuous.
That gives the recipient the ability to arbitrarily interfere with
your use of the table, even without exploiting the fact that the
trigger runs as you when you operate on the table.
Possibly we ought to document the security risks a bit better.
regards, tom lane