Re: Fix search_path for all maintenance commands - Mailing list pgsql-hackers

From Isaac Morland
Subject Re: Fix search_path for all maintenance commands
Date
Msg-id CAMsGm5efO3HXGcDFT7T4iv0eDa16S1tVbptbXSOEdfqnSS0Owg@mail.gmail.com
Whole thread Raw
In response to Re: Fix search_path for all maintenance commands  (Jeff Davis <pgsql@j-davis.com>)
Responses Re: Fix search_path for all maintenance commands
List pgsql-hackers
On Thu, 2 Nov 2023 at 14:22, Jeff Davis <pgsql@j-davis.com> wrote:
On Tue, 2023-10-31 at 13:16 -0400, Isaac Morland wrote:

> Perhaps the search_path for running a maintenance command should be
> the search_path set for the table owner (ALTER ROLE … SET search_path
> …)?

That's an interesting idea; I hadn't considered that, or at least not
very deeply. I feel like it came up before but I can't remember what
(if anything) was wrong with it.

If we expanded this idea a bit, I could imagine it applying to SECURITY
DEFINER functions as well, and that would make writing SECURITY DEFINER
functions a lot safer.

I still think the right default is that CREATE FUNCTION stores the search_path in effect when it runs with the function, and that is the search_path used to run the function (and don't "BEGIN ATOMIC" functions partially work this way already?). But I suggest the owner search_path as an option which is clearly better than using the caller's search_path in most cases.

I think the problems are essentially the same for security invoker vs. security definer. The difference is that the problems are security problems only for security definers.

>  After that, change search_path on function invocation as usual
> rather than having special rules for what happens when a function is
> invoked during a maintenance command.

I don't follow what you mean here.

I’m referring to the idea that the search_path during function execution should be determined at function creation time (or, at least, not at function execution time). While this is a security requirement for security definer functions, I think it’s what is wanted about 99.9% of the time for security invoker functions as well. So when the maintenance command ends up running a function, the search_path in effect during the function execution will be the one established at function definition time; or if we go with this "search_path associated with function owner" idea, then again the search_path is determined by the usual rule (function owner), rather than by any special rules associated with maintenance commands.

pgsql-hackers by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: Wrong security context for deferred triggers?
Next
From: Nathan Bossart
Date:
Subject: Re: Atomic ops for unlogged LSN