Re: pgsql: Fix search_path to a safe value during maintenance operations. - Mailing list pgsql-hackers

On Mon, Jul 31, 2023 at 1:18 PM Joe Conway <mail@joeconway.com> wrote:
> But the analysis of the issue needs to go one step further. Even if the
> search_path does not change from the originally intended one, a newly
> created function can shadow the intended one based on argument coercion
> rules.

Yeah, this is a complicated issue. As the system works today,  if you
include in your search_path a schema to which some other user can
write, you are pretty much agreeing to execute code provided by that
user. If that user has strictly greater privileges than you, e.g. they
are the super-user, then that's fine, because they can compromise your
account anyway, but otherwise, you're probably doomed. Not only can
they try to capture references with similarly-named objects, they can
also do things like create objects whose names are common
mis-spellings of the objects that are supposed to be there and hope
you access the wrong one by mistake. Maybe there are other attacks as
well, but even if not, I think it's already a pretty hopeless
situation. I think the UNIX equivalent would be including a directory
in your PATH that is world-writable and hoping your account will stay
secure. Not very likely.

We have already taken an important step in terms of preventing this
attack in commit b073c3ccd06e4cb845e121387a43faa8c68a7b62, which
removed PUBLIC CREATE from the public schema. Before that, we were
shipping a configuration analogous to a UNIX system where /usr/bin was
world-writable -- something which no actual UNIX system has likely
done any time in the last 40 years, because it's so clearly insane. We
could maybe go a step further by changing the default search_path to
not even include public, to further discourage people from using that
as a catch-all where everybody can just dump their objects. Right now,
anybody can revert that change with a single GRANT statement, and if
we removed public from the default search_path as well, people would
have one extra step to restore that insecure configuration. I don't
know such a change is worthwhile, though. It would still be super-easy
for users to create insecure configurations: as soon as user A can
write a schema and user B has it in the search_path, B is in a lot of
trouble if A turns out to be malicious.

One thing we might be able to do to prevent that sort of thing is to
have a feature to prevent "accidental" code execution, as in the
"function trust" mechanism proposed previously. Say I trust all users
who can SET ROLE to me and/or who inherit my privileges. Additionally
I can decide to trust users who do neither of those things by some
sort of explicit declaration. If I don't trust a user then if I do
anything that would cause code supplied by that user to get executed,
it just errors out:

ERROR: role "rhaas" should not execute arbitrary code provided by role "jconway"
HINT: If this should be allowed, use the TRUST command to permit it.

Even if we do this, I still think we need the kinds of fixes that I
mentioned earlier. An error like this is fine if you're trying to do
something to a table owned by another user and they've got a surprise
trigger on there or something, but a maintenance command like VACUUM
should find a way to work that is both secure and non-astonishing. And
we probably also still need to find ways to control search_path in a
lot more widely than we do today. Otherwise, even if stuff is
technically secure, it may just not work.

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