MIME-Version: 1.0
In-Reply-To: <20250103135332.7910a008714abaa04d32e5e2@magnetkern.de>
References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
 <CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
 <20241228004009.267f21b78394c934f27f9974@magnetkern.de> <20250103135332.7910a008714abaa04d32e5e2@magnetkern.de>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Fri, 3 Jan 2025 08:34:57 -0700
Message-ID: <CAKFQuwaU19_6HaB+9-L-fQhjUr8_5ACvxLAPRBhEdfLv9JVZBg@mail.gmail.com>
Subject: Re: search_path for PL/pgSQL functions partially cached?
To: Jan Behrens <jbe-mlist@magnetkern.de>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000739291062acf05e1"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwaU19_6HaB%2B9-L-fQhjUr8_5ACvxLAPRBhEdfLv9JVZBg%40mail.gmail.com>
Precedence: bulk

--000000000000739291062acf05e1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Friday, January 3, 2025, Jan Behrens <jbe-mlist@magnetkern.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.  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.

I=E2=80=99d probably modify the function signature to take search_path as a=
 second
optional argument and then invoke a set search_path within the function.
At worse the caller can place current_setting(search_path) as the value of
that argument though being explicit would be recommended.

David J.

--000000000000739291062acf05e1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Friday, January 3, 2025, Jan Behrens &lt;<a href=3D"mailto:jbe-mlist@mag=
netkern.de">jbe-mlist@magnetkern.de</a>&gt; wrote:<blockquote class=3D"gmai=
l_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left=
:1ex">
I would like to know if the above example is correct. It seems overall<br>
bulky, but I haven&#39;t found a better way, assuming that it can be<br>
unknown where a particular extension has been installed to. In<br>
particular I feel a bit insecure about where I have to fully qualify,<br>
and where not. See the comments in the code above.</blockquote><div><br></d=
iv><div>Short answer, you cannot looking at a definition and know the answe=
r - whether the code is going to be executed in a sanitized search_path is =
what matters.=C2=A0 Anything that would be executed during pg_restore has t=
o be made safe.=C2=A0 Therefore, code that is only ever executed by applica=
tions directly can use swarch_path.</div><div><br></div><div>I=E2=80=99d pr=
obably modify the function signature to take search_path as a second option=
al argument and then invoke a set search_path within the function.=C2=A0 At=
 worse the caller can place current_setting(search_path) as the value of th=
at argument though being explicit would be recommended.</div><div><br></div=
><div>David J.</div><div><br></div>

--000000000000739291062acf05e1--