Re: pgsql: Fix search_path to a safe value during maintenance operations.

On 7/31/23 12:53, Robert Haas wrote:
> On Fri, Jun 30, 2023 at 3:41 AM Jeff Davis <pgsql@j-davis.com> wrote:
>> I'm not sure that everyone in this thread realizes just how broken it
>> is to depend on search_path in a functional index at all. And doubly so
>> if it depends on a schema other than pg_catalog in the search_path.
>>
>> Let's also not forget that logical replication always uses
>> search_path=pg_catalog, so if you depend on a different search_path for
>> any function attached to the table (not just functional indexes, also
>> functions inside expressions or trigger functions), then those are
>> already broken in version 15. And if a superuser is executing
>> maintenance commands, there's little reason to think they'll have the
>> same search path as the user that created the table.
>>
>> At some point in the very near future (though I realize that point may
>> come after version 16), we need to lock down the search path in a lot
>> of cases (not just maintenance commands), and I don't see any way
>> around that.
> 
> I agree. I think there are actually two interrelated problems here.
> 
> One is that virtually all code needs to run with the originally
> intended search_path rather than some search_path chosen at another
> time and maybe by a different user. If not, it's going to break, or
> compromise security, depending on the situation. The other is that
> running arbitrary code written by somebody else as yourself is
> basically instant death, from a security perspective.

I agree too.

But the analysis of the issue needs to go one step further. Even if the 
search_path does not change from the originally intended one, a newly 
created function can shadow the intended one based on argument coercion 
rules.

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