Re: Error with pg_dump (of data), with --role

Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> writes:
> On Thu, Feb 18, 2021 at 8:44 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> There was a security change to pg_dump a few years ago to make it
>> put "set search_path = pg_catalog" into the dump script. This
>> basically means that any user-defined function in indexes, check
>> constraints, etc is on its own to be sure that it schema-qualifies
>> non-system names, or has a "SET search_path" clause to do that
>> for it.

> Thank you Tom for that explanation. To follow on, I tried adding:
> SET search_path = public;
> to the functions, but that prevents my function from working at all:

No, the way to do it is with a SET function property, like

create or replace function myfunc(...) returns ... language ...
as $$body here$$
SET search_path = whatever
... other function properties ...
;

That takes care of restoring the old value on the way out of the
function, so it's okay to use in an immutable function.

I think you can plaster this property onto an existing function
with ALTER FUNCTION, which should be less error-prone than
repeating the whole CREATE.

regards, tom lane