Re: Fix search_path for all maintenance commands

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 was earlier pushing for search_path to be tied to the function (in my
"CREATE FUNCTION ... SEARCH" proposal) on the grounds that the author
(usually) doesn't want the behavior to depend on the caller's
search_path. That proposal didn't go very well because it required
extra DDL.

But if we tie the search_path to the user-switching behavior rather
than the function, that still protects the function author from many
sorts of search_path attacks, because it's either running as the
function author with the function author's search_path; or running as
the invoking user with their search_path. And there aren't a lot of
cases where a function author would want it to run with their
privileges and the caller's search_path.

[ It would still leave open the problem of a SECURITY INVOKER function
in an index expression returning inconsistent results due to a changing
search_path, which would compromise the index structure and any
constraints using that index. But that problem is more bounded, at
least. ]

> 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.

Regards,
Jeff Davis