On Fri, Feb 19, 2021 at 3:22 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Ken Tanzer <ken.tan...@gmail.com> writes: > > On Thu, Feb 18, 2021 at 8:44 PM Tom Lane <t...@sss.pgh.pa.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.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
