Date: Fri, 3 Jan 2025 22:33:12 +0100
From: Jan Behrens <jbe-mlist@magnetkern.de>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "pgsql-general@lists.postgresql.org"
 <pgsql-general@lists.postgresql.org>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Message-Id: <20250103223312.dde69ae482776e4f1b0c2258@magnetkern.de>
In-Reply-To: <CAKFQuwZdt+YLi=9_WraRLajuOkmw4esFzbHTXmv5MwHJemdDhQ@mail.gmail.com>
References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
	<CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
	<20241228004009.267f21b78394c934f27f9974@magnetkern.de>
	<20250103135332.7910a008714abaa04d32e5e2@magnetkern.de>
	<CAKFQuwaU19_6HaB+9-L-fQhjUr8_5ACvxLAPRBhEdfLv9JVZBg@mail.gmail.com>
	<20250103174849.f071d9c6942f76a9b812157c@magnetkern.de>
	<CAKFQuwZdt+YLi=9_WraRLajuOkmw4esFzbHTXmv5MwHJemdDhQ@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20250103223312.dde69ae482776e4f1b0c2258%40magnetkern.de>
Precedence: bulk

On Fri, 3 Jan 2025 10:16:15 -0700
"David G. Johnston" <david.g.johnston@gmail.com> wrote:

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

I'm not sure if there is a misunderstanding. In my last example (e-mail
dated Fri, 3 Jan 2025 13:53:32 +0100), the user who has control over
the contents of the "query_p" argument is an application programmer,
not a real end-user. The function is also *not* marked as SECURITY
DEFINER, so it always runs with the privileges of the caller. I don't
see any specific security risk here, except that I'm unsure if the
function is written properly with regard to qualification of the used
types after PL/pgSQL's BEGIN. As I learned, I must fully-qualify types
*before* the BEGIN, i.e. in the DECLARE section. But does this also
hold for types after the BEGIN when I previously ensure that the
search_path is correctly set (set within the function's body)?

> 
> > > 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 don't think my function would be evaluated during a pg_restore then.

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

But if I use "SET search_path FROM CURRENT", then the called function
won't know the search_path that is set at the caller's side (which is
what I need to make my interface nice to use).

I would prefer my current solution, but I would like to be sure that my
example (the one in my e-mail dated Fri, 3 Jan 2025 13:53:32 +0100) is
correct. I still am not sure about that.

Kind Regards,
Jan Behrens