Feedback-ID: i76984098:Fastmail
Message-ID: <369dee18-3100-44a5-81af-1baa56c728bd@aklaver.com>
Date: Wed, 1 Jan 2025 10:12:32 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: search_path for PL/pgSQL functions partially cached?
To: Jan Behrens <jbe-mlist@magnetkern.de>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>
References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
 <CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
 <20241228004009.267f21b78394c934f27f9974@magnetkern.de>
 <20250101185504.3d50c571c3448512e94288e8@magnetkern.de>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <20250101185504.3d50c571c3448512e94288e8@magnetkern.de>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/369dee18-3100-44a5-81af-1baa56c728bd%40aklaver.com>
Precedence: bulk

On 1/1/25 09:55, Jan Behrens wrote:
> 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.

https://www.postgresql.org/docs/current/sql-createfunction.html

"sql_body

     The body of a LANGUAGE SQL function. This can either be a single 
statement

     RETURN expression

     or a block

     BEGIN ATOMIC
       statement;
       statement;
       ...
       statement;
     END

     This is similar to writing the text of the function body as a 
string constant (see definition above), but there are some differences: 
This form only works for LANGUAGE SQL, the string constant form works 
for all languages. This form is parsed at function definition time, the 
string constant form is parsed at execution time; therefore this form 
cannot support polymorphic argument types and other constructs that are 
not resolvable at function definition time. This form tracks 
dependencies between the function and objects used in the function body, 
so DROP ... CASCADE will work correctly, whereas the form using string 
literals may leave dangling functions. Finally, this form is more 
compatible with the SQL standard and other SQL implementations.
"

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

-- 
Adrian Klaver
adrian.klaver@aklaver.com