On Fri, Feb 19, 2021 at 3:22 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Ken Tanzer <ken.tanzer@xxxxxxxxx> writes:
> On Thu, Feb 18, 2021 at 8:44 PM Tom Lane <tgl@xxxxxxxxxxxxx> 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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
