Re: Fix search_path for all maintenance commands

On Fri, 27 Oct 2023 at 19:04, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

The approach of locking down search_path during maintenance commands
> would solve the problem, but it means that we are enforcing search_path
> in some contexts and not others. That's not great, but it's similar to
> what we are doing when we ignore SECURITY INVOKER and run the function
> as the table owner during a maintenance command, or (by default) for
> subscriptions.
>

I don't agree that this is ignoring SECURITY INVOKER. Instead, I see it as
running the maintenance command as the owner of the table, which is
therefore the invoker of the function. As far as I can tell we need to do
this for security anyway - otherwise as soon as superuser runs a
maintenance command (which it can already do), the owner of any function
called in the course of the maintenance operation has an opportunity to get
superuser.

For that matter, what would it even mean to ignore SECURITY INVOKER? Run
the function as its owner if it were SECURITY DEFINER?

I understand what ignoring SECURITY DEFINER would mean: obviously, don't
adjust the current user on entry and exit.

The privilege boundary should be at the point where the maintenance command
starts: the role with MAINTAIN privilege gets to kick off maintenance, but
doesn't get to specify anything after that, including the search_path (of
course, function execution search paths should not normally depend on the
caller's search path anyway, but that's a bigger discussion with an
unfortunate backward compatibility problem).

Perhaps the search_path for running a maintenance command should be the
search_path set for the table owner (ALTER ROLE … SET search_path …)? If
none set, the default "$user", public. 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.

My attempts to more generally try to lock down search_path for
> functions attached to tables didn't seem to get much consensus, so if
> we do make an exception to lock down search_path for maintenance
> commands only, it would stay an exception for the foreseeable future.
>