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

On Fri, Feb 19, 2021 at 3:22 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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.
>
>

Great, that works, and ALTER FUNCTION definitely much better for me.

One thing about the search path though, regarding pg_temp. If I add a

SET search_path = public;

Do I need instead to specify "public, pg_temp" to prevent it from being
(silently) at the beginning?

This seems to be what the "Writing SECURITY DEFINER Functions Safely"
section suggests (
https://www.postgresql.org/docs/9.6/sql-createfunction.html) But pg_temp
isn't mentioned at all on the page about schemas (
https://www.postgresql.org/docs/9.6/ddl-schemas.html) so I'm a little
unclear. Also if there are other hidden schemas in the search path.

And along those lines, any chance of seeing something like "SHOW
search_path_complete" (or search_path_explicit) implemented? Seems like it
could be helpful!

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.