Date: Wed, 1 Jan 2025 18:55:04 +0100
From: Jan Behrens <jbe-mlist@magnetkern.de>
To: "pgsql-general@lists.postgresql.org"
 <pgsql-general@lists.postgresql.org>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Message-Id: <20250101185504.3d50c571c3448512e94288e8@magnetkern.de>
In-Reply-To: <20241228004009.267f21b78394c934f27f9974@magnetkern.de>
References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
	<CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
	<20241228004009.267f21b78394c934f27f9974@magnetkern.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20250101185504.3d50c571c3448512e94288e8%40magnetkern.de>
Precedence: bulk

On Sat, 28 Dec 2024 00:40:09 +0100
Jan Behrens <jbe-mlist@magnetkern.de> wrote:

> On Fri, 27 Dec 2024 13:26:28 -0700
> "David G. Johnston" <david.g.johnston@gmail.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.

Actually, I found another note in the documentation. But it doesn't
explain things correctly. In the documentation for PostgreSQL 17,
section 36.17.6.1. (Security Considerations for Extension Functions)
says:

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

https://www.postgresql.org/docs/17/extend-extensions.html#EXTEND-EXTENSIONS-SECURITY

So here, the manual explicity states that functions are parsed at
execution, not creation time. As seen in my original example in this
thread, this isn't (fully) true. Moreover, it isn't true for all
SQL-language functions, as can be demonstrated with the following code:

============

CREATE SCHEMA s1;
CREATE SCHEMA s2;

CREATE VIEW s1.v AS SELECT 'creation' AS col;
CREATE VIEW s2.v AS SELECT 'runtime' AS col;

SET search_path TO 'public', 's1';

CREATE FUNCTION use_sql_atomic() RETURNS TEXT LANGUAGE sql BEGIN ATOMIC
  SELECT 'use_sql_atomic = ' || col FROM v;
END;

CREATE FUNCTION use_sql_string() RETURNS TEXT LANGUAGE sql AS $$
  SELECT 'use_sql_string = ' || col FROM v;
$$;

CREATE FUNCTION use_plpgsql() RETURNS TEXT LANGUAGE plpgsql AS $$ BEGIN
  RETURN (SELECT 'use_plpgsql = ' || col FROM v);
END; $$;

SET search_path TO 'public', 's2';

SELECT use_sql_atomic() AS "output" UNION ALL
SELECT use_sql_string() AS "output" UNION ALL
SELECT use_plpgsql() AS "output";

============

This generates the following output:

          output           
---------------------------
 use_sql_atomic = creation
 use_sql_string = runtime
 use_plpgsql = runtime
(3 rows)

Overall, PostgreSQL doesn't behave consistent, and to me it seems that
the documentation isn't describing its behavior correctly either.

I understand if fixing this is too much work (even though I would
really like to see this fixed). But given that the current behavior is
highly surprising and inconsistent - and keeping in mind that this is a
subject that may affect security - I think the documentation should
reflect the current behavior at least. I thus see this as a
documentation issue.

Kind regards,
Jan Behrens