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.