How could a "SECURITY DEFINER" function in PG be insecure with an improper search_path?

The PG docs provides this example of a properly written security definer function with a search_path:

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;

I don't understand this part:


But without the SET clause, or with a SET clause mentioning only
admin, the function could be subverted by creating a temporary table
named pwds.

If admin.pwds exists and search_path is set to admin only, how is it even possible to create a temporary table, pwds, maliciously?

If pg_temp is not present in search_path at all, then the temp scheme is always searched first, i.e. as if it were implicitly prepended to search_path.

The only way to prevent it from being searched first is to give it an explicit presence in search_path.

StackExchange Database Administrators Q#211055, answer score: 3