Re: pgsql: Fix search_path to a safe value during maintenance operations.

On Fri, Jun 30, 2023 at 3:41 AM Jeff Davis <pgsql@j-davis.com> wrote:
> I'm not sure that everyone in this thread realizes just how broken it
> is to depend on search_path in a functional index at all. And doubly so
> if it depends on a schema other than pg_catalog in the search_path.
>
> Let's also not forget that logical replication always uses
> search_path=pg_catalog, so if you depend on a different search_path for
> any function attached to the table (not just functional indexes, also
> functions inside expressions or trigger functions), then those are
> already broken in version 15. And if a superuser is executing
> maintenance commands, there's little reason to think they'll have the
> same search path as the user that created the table.
>
> At some point in the very near future (though I realize that point may
> come after version 16), we need to lock down the search path in a lot
> of cases (not just maintenance commands), and I don't see any way
> around that.

I agree. I think there are actually two interrelated problems here.

One is that virtually all code needs to run with the originally
intended search_path rather than some search_path chosen at another
time and maybe by a different user. If not, it's going to break, or
compromise security, depending on the situation. The other is that
running arbitrary code written by somebody else as yourself is
basically instant death, from a security perspective.

It's a little hard to imagine a world in which these problems don't
exist at all, but it somehow feels like the design of the system
pushes you toward doing this stuff incorrectly rather than doing it
correctly. For instance, you can imagine a system where when you run
CREATE OR REPLACE FUNCTION, the prevailing search_path is captured and
automatically included in proconfig. Then the default behavior would
be to run functions and procedures with the search_path that was in
effect when they were created, rather than what we actually have,
where it's the one in effect at execution time as it is currently.

It's a little harder to imagine something similar around all the user
switching behavior, just because we have so many ways of triggering
arbitrary code execution: views, triggers, event triggers, expression
indexes, constraints, etc. But you can maybe imagine a system where
all code associated with a table is run as the table owner in all
cases, regardless of SECURITY INVOKER/DEFINER, which I think would at
least close some holes.

The difficulty is that it's a bit hard to imagine making these kinds
of definitional changes now, because they'd probably be breaking
changes for pretty significant numbers of users. But on the other
hand, if we don't start thinking about systemic changes here, it feels
like we're just playing whack-a-mole.

--
Robert Haas
EDB: http://www.enterprisedb.com