Re: search_path for PL/pgSQL functions partially cached?

On Fri, Jan 3, 2025 at 9:48 AM Jan Behrens <jbe-mlist(at)magnetkern(dot)de> wrote:

> On Fri, 3 Jan 2025 08:34:57 -0700
> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> > On Friday, January 3, 2025, Jan Behrens <jbe-mlist(at)magnetkern(dot)de> wrote:
> > >
> > > I would like to know if the above example is correct. It seems overall
> > > bulky, but I haven't found a better way, assuming that it can be
> > > unknown where a particular extension has been installed to. In
> > > particular I feel a bit insecure about where I have to fully qualify,
> > > and where not. See the comments in the code above.
> >
> >
> > Short answer, you cannot looking at a definition and know the answer -
> > whether the code is going to be executed in a sanitized search_path is
> what
> > matters.
>
> I don't understand. Do you mean my last example is wrong / insecure?
> If so, why?
>

It is at risk because it depends on the session search_path. That is all.
Whether that risk turns into a failure to execute depends on how/when it is
executed. I'm not that comfortable talking about security risks in this
context though the current design goal is to mitigate such security issues
by setting things up so the function execution fails rather than is
executed insecurely. This is presently mainly done by setting the
search_path to just effectively pg_catalog before executing the query,
breaking any code depending on other schemas existing in the search_path.

> > Anything that would be executed during pg_restore has to be made
> > safe. Therefore, code that is only ever executed by applications
> directly
> > can use swarch_path.
>
> Why should the function be executed during pg_restore?

If the function is used in building an index, or a materialized view, are
the common cases. Trigger functions too.

Note, this is talking about evaluating functions generally, not the one
provided here specifically.

> I could do that, but I would like to understand if that is really
> necessary as it makes the interface more complicated, and I would like
> to avoid unnecessary complexity in my interface.
>
> Is it really impossible to have functions without SET search_path in
> the definition of a PL/pgSQL function if I fully-qualify all types in
> the DECLARE section and if all other non-qualified identifiers occur
> after set_config('search_path', ...)?
>
> If you add a set_config to the body of the function then you indeed avoid
the problem. It is basically equivalent to adding a SET clause to the
create function command. In this case even when the function is executed
in a sanitized search_path environment (such as the one established by
pg_restore) you are not relying on it. That non-reliance is all that
really matters.

David J.