Re: Fix search_path for all maintenance commands

On Fri, 2023-05-26 at 16:21 -0700, Jeff Davis wrote:
> Maintenance commands (ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW,
> REINDEX, and VACUUM) currently run as the table owner, and as a
> SECURITY_RESTRICTED_OPERATION.
>
> I propose that we also fix the search_path to "pg_catalog, pg_temp"
> when running maintenance commands

New patch attached.

We need this patch for several reasons:

* If you have a functional index, and the function depends on the
search_path, then it's easy to corrupt your index if you (or a
superuser) run a REINDE/CLUSTER with the wrong search_path.

* The MAINTAIN privilege needs a safe search_path, and was reverted
from 16 because the search_path in 16 is not restricted.

* In general, it's a good idea for things like functional indexes and
materialized views to be independent of the search_path.

* The search_path is already restricted in some other contexts, like
logical replication and autoanalyze.

Others have raised some concerns though:

* It might break for users who have a functional index where the
function implicitly depends on a search_path containing a namespace
other than pg_catalog. My opinion is that such functional indexes are
conceptually broken and we need to desupport them, and there will be
some breakage, but I'm open to suggestion about how we minimize that (a
compatibility GUC or something?).

* The fix might not go far enough or might be in the wrong place. I'm
open to suggestion here, too. Maybe we can make it part of the general
function call mechanism, and can be overridden by explicitly setting
the function search path? Or maybe we need new syntax where the
function can acquire the search path from the session explicitly, but
uses a safe search path by default?

--
Jeff Davis
PostgreSQL Contributor Team - AWS