public inbox for [email protected]
help / color / mirror / Atom feedFrom: David G. Johnston <[email protected]>
To: Jan Behrens <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Date: Fri, 3 Jan 2025 10:16:15 -0700
Message-ID: <CAKFQuwZdt+YLi=9_WraRLajuOkmw4esFzbHTXmv5MwHJemdDhQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAKFQuwaU19_6HaB+9-L-fQhjUr8_5ACvxLAPRBhEdfLv9JVZBg@mail.gmail.com>
<[email protected]>
On Fri, Jan 3, 2025 at 9:48 AM Jan Behrens <[email protected]> wrote:
> On Fri, 3 Jan 2025 08:34:57 -0700
> "David G. Johnston" <[email protected]> wrote:
>
> > On Friday, January 3, 2025, Jan Behrens <[email protected]> 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.
view thread (33+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: search_path for PL/pgSQL functions partially cached?
In-Reply-To: <CAKFQuwZdt+YLi=9_WraRLajuOkmw4esFzbHTXmv5MwHJemdDhQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox