Horton

Re: Fix search_path for all maintenance commands

Isaac Morland <isaac.morland@gmail.com>

View thread
From: Isaac Morland <isaac.morland@gmail.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>, Noah Misch <noah@leadboat.com>, "David G. Johnston" <david.g.johnston@gmail.com>, Gurjeet Singh <gurjeet@singh.im>, pgsql-hackers@postgresql.org, Robert Haas <robertmhaas@gmail.com>, Greg Stark <stark@mit.edu>, Tom Lane <tgl@sss.pgh.pa.us>
Date: 2023-11-06T20:31:58Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix search_path to a safe value during maintenance operations.

  2. Make relation-enumerating operations be security-restricted operations.

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.