As pointed out by Peter Eisentraut in a blog post named
Schema Search Paths Considered Pain in the Butt, you need to make sure the
search_path is explicitly set for all
SECURITY DEFINER functions in PostgreSQL.
Fixing this manually for, in my case, 2106 functions, is, indeed a “pain in the butt”, so I crafted a little query to automate the job:
\t \pset format unaligned \o /tmp/fix_search_path_for_security_definer_functions.sql select array_to_string( array_agg( -- inject SET search_path in-between LANGUAGE and SECURITY DEFINER in the declaration regexp_replace( pg_get_functiondef(oid), E'(LANGUAGE [a-z]+)\\s+(SECURITY DEFINER)', E'\\1\n SET search_path TO public, pg_temp\n \\2' ) ), ';' ) from pg_proc where prosecdef is true -- SECURITY DEFINER functions -- don't include functions for which we have already specified a search_path and not (coalesce(array_to_string(proconfig,''),'') like '%search_path%') -- public schema and pronamespace = 2200 ; \t \o \i /tmp/fix_search_path_for_security_definer_functions.sql -- If all goes well you should see a lot of CREATE FUNCTION being spammed on the screen