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
Joel on SQL 
I find format() a little clearer without the scary and possibly error-prone string mashing. Oh, and the general case needs to handle schema names, so I’ve included it here:
SELECT format(
$$ALTER FUNCTION %I.%I ( %s ) SET search_path TO public,pg_temp;$$,
n.nspname,
p.proname,
pg_catalog.pg_get_function_arguments(p.oid)
)
FROM
pg_catalog.pg_proc p
JOIN
pg_catalog.pg_namespace n
ON (p.pronamespace = n.oid)
WHERE p.prosecdef;
Doh, why didn’t I think of that, much more elegant than creating the function again. Thanks!
I like search paths and think the feature should stay, however it should also be changed to work locally rather than globally, and should be treated as a data-definition operation. That is, a search path should be like a macro declaration, which basically just has an effect at “compile”/”data-definition” time. By this I mean, by default at the global level the search path is empty, meaning nothing can be found except by being called fully-qualified. And then, optionally, within each local context, eg per source file or per schema or per schema object definition, a search path can be declared which only has any effect on entity references from within that context. This way, for example, if 2 users each own their own schema, and declare their own search paths, this is like a macro where other objects declared in the same schema that refer to things in an unqualified manner, behind the scenes become fully-qualified as defined by their own search paths. External users of the schema objects don’t have to set search paths in order for the internals of the schema objects they use to work correctly, they would only set their own search paths so that their first hop works. So if user A defines a routine foo() and that invokes bar(), the definer of foo() is the only one that has a say what bar() resolves to, except for the owners of the schemas that are in the search path set by user A for their own schema, and ideally they’d keep that as narrow as possible, typically just to include the same schema as the search path so only local stuff resolves.
You can do better using the regprocedure type:
format ($$ALTER FUNCTION %s SET search_path TO public,pg_temp;$$, p.oid::regprocedure)
And yes, this will properly escape everything, due to the magic of the regprocedure type.
“regclass” is also pretty handy sometimes.
Neato! Thanks for the tip š