Re: Delete cascade trigger runs security definer - Mailing list pgsql-general

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