Message-ID: <0e51f9e2-9272-ccb3-9219-39d7695f7c50@joeconway.com>
Date: Sat, 12 Aug 2023 09:15:55 -0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.13.0
Content-Language: en-US
To: Jeff Davis <pgsql@j-davis.com>, pgsql-hackers@postgresql.org
References: <2710f56add351a1ed553efb677408e51b060e67c.camel@j-davis.com>
From: Joe Conway <mail@joeconway.com>
Subject: Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION }
In-Reply-To: <2710f56add351a1ed553efb677408e51b060e67c.camel@j-davis.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/0e51f9e2-9272-ccb3-9219-39d7695f7c50%40joeconway.com>
Precedence: bulk

On 8/11/23 22:35, Jeff Davis wrote:
> The attached patch implements a new SEARCH clause for CREATE FUNCTION.
> The SEARCH clause controls the search_path used when executing
> functions that were created without a SET clause.
> 
> Background:
> 
> Controlling search_path is critical for the correctness and security of
> functions. Right now, the author of a function without a SET clause has
> little ability to control the function's behavior, because even basic
> operators like "+" involve search_path. This is a big problem for, e.g.
> functions used in expression indexes which are called by any user with
> write privileges on the table.
> 
> Motivation:
> 
> I'd like to (eventually) get to safe-by-default behavior. In other
> words, the simplest function declaration should be safe for the most
> common use cases.

I agree with the general need.

> Add SEARCH { DEFAULT | SYSTEM | SESSION } clause to CREATE/ALTER
> function.
> 
>    * SEARCH DEFAULT is the same as no SEARCH clause at all, and ends up
> stored in the catalog as prosearch='d'.
>    * SEARCH SYSTEM means that we switch to the safe search path of
> "pg_catalog, pg_temp"  when executing the function. Stored as
> prosearch='y'.
>    * SEARCH SESSION means that we don't switch the search_path when
> executing the function, and it's inherited from the session. Stored as
> prosearch='e'.

It isn't clear to me what is the precise difference between  DEFAULT and 
SESSION


> 2. We can more accurately serve the user's intent. For instance, the
> safe search_path of "pg_catalog, pg_temp" is arcane and seems to be
> there just because we don't have a way to specify that pg_temp be
> excluded entirely. But perhaps in the future we *do* want to exclude
> pg_temp entirely. Knowing that the user just wants "SEARCH SYSTEM"
> allows us some freedom to do that.

Personally I think having pg_temp in the SYSTEM search path makes sense 
for temp tables, but I find it easy to forget that functions can be 
created by unprivileged users in pg_temp, and therefore having pg_temp 
in the search path for functions is dangerous.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com