Re: search_path for PL/pgSQL functions partially cached?

On Wed, Jan 1, 2025 at 10:55 AM Jan Behrens <jbe-mlist(at)magnetkern(dot)de> wrote:

> On Sat, 28 Dec 2024 00:40:09 +0100
> Jan Behrens <jbe-mlist(at)magnetkern(dot)de> wrote:
>
> > On Fri, 27 Dec 2024 13:26:28 -0700
> > "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >
> > > > Or is it documented somewhere?
> > >
> > >
> https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
> >
> > I can't find any notes regarding functions and schemas in that section.
>
>
"Because PL/pgSQL saves prepared statements and sometimes execution plans
in this way, SQL commands that appear directly in a PL/pgSQL function must
refer to the same tables and columns on every execution; that is, you
cannot use a parameter as the name of a table or column in an SQL command."

Changing search_path is just one possible way to change out which object a
name tries to refer to so it is not called out explicitly.

> "SQL-language and PL-language functions provided by extensions are at
> risk of search-path-based attacks when they are executed, since parsing
> of these functions occurs at execution time not creation time."
>

> Moreover, it isn't true for all
> SQL-language functions, as can be demonstrated with the following code:
>

Yeah, when we added a second method to write an SQL-language function, one
that doesn't simply accept a string body, we didn't update that section to
point out that is the string input variant of create function that is
affected in this manner, the non-string (atomic) variant stores the result
of parsing the inline code as opposed to storing the raw text.

David J.