Message-ID: <dba2e7d6293ee3613b0d706d79f602942022e026.camel@j-davis.com>
Subject: Re: pgsql: Fix search_path to a safe value during maintenance
 operations.
From: Jeff Davis <pgsql@j-davis.com>
To: Joe Conway <mail@joeconway.com>, Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Noah Misch <noah@leadboat.com>,
	"pgsql-hackers@postgresql.org"
	 <pgsql-hackers@postgresql.org>, Nathan Bossart <nathandbossart@gmail.com>
Date: Mon, 31 Jul 2023 15:22:07 -0700
In-Reply-To: <a35ef296-9438-2194-9c6c-807b2f174b0c@joeconway.com>
References: <E1q7j7Y-000z1H-Hr@gemulon.postgresql.org>
	 <89a415b86d5421941e4281ba5bf6288934078624.camel@j-davis.com>
	 <d20864b929ff769a0ebf772359719f05bb6982cd.camel@j-davis.com>
	 <573014.1686375211@sss.pgh.pa.us>
	 <20230612170510.GA201972@gust.leadboat.com>
	 <f53df2dbe11bb42764bcdd003a54aa29c9bb6137.camel@j-davis.com>
	 <20230613182920.GA259321@gust.leadboat.com>
	 <1799839.1686687804@sss.pgh.pa.us>
	 <4df537f5a0d8c0ceac3eec3809d1144880b8e76d.camel@j-davis.com>
	 <CA+TgmoYtJU0KknWr11W--wK=4eJtdHOgupyT=beZVPC8P=o+YA@mail.gmail.com>
	 <578fb4be80247570e6a05924908765a0b345971e.camel@j-davis.com>
	 <CA+TgmoZVCHERUkXhAMT2Er-sKBc5C6_iX+TpxxivBevDHzq2TQ@mail.gmail.com>
	 <43bbc9f7318057f51552d5986830908cb018dadf.camel@j-davis.com>
	 <1659699.1688086436@sss.pgh.pa.us>
	 <fff566293c9165c69bb4c555da1ac02c63660664.camel@j-davis.com>
	 <CA+Tgmoa5mksH1rAggYYwm0fJAO8TCnLG_oRE=su06XfqU+KTLA@mail.gmail.com>
	 <a35ef296-9438-2194-9c6c-807b2f174b0c@joeconway.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.4-0ubuntu1 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/dba2e7d6293ee3613b0d706d79f602942022e026.camel%40j-davis.com>
Precedence: bulk

On Mon, 2023-07-31 at 13:17 -0400, Joe Conway wrote:
> But the analysis of the issue needs to go one step further. Even if
> the=20
> search_path does not change from the originally intended one, a newly
> created function can shadow the intended one based on argument
> coercion=20
> rules.

There are quite a few issues going down this path:

* The set of objects in each schema can change. Argument coercion is a
particularly subtle one, but there are other ways that it could find
the wrong object. The temp namespace also has some subtle issues.

* Schema USAGE privileges may vary over time or from caller to caller,
affecting which items in the search path are searched at all. The same
goes if theres an object access hook in place.

* $user should be resolved to a specific schema (or perhaps not in some
cases?)

* There are other GUCs and environment that can affect function
behavior. Is it worth trying to lock those down?

I agree that each of these is some potential problem, but these are
much smaller problems than allowing the caller to have complete control
over the search_path.

Regards,
	Jeff Davis