On Mon, 2023-07-17 at 12:16 -0700, Jeff Davis wrote:
> Based on feedback, I plan to commit soon.

Attached is a new version.

Changes:

* Also switch the search_path during CREATE MATERIALIZED VIEW, so that
it's consistent with REFRESH. As a part of this change, I slightly
reordered things in ExecCreateTableAs() so that the skipData path
returns early without entering the SECURITY_RESTRICTED_OPERATION. I
don't think that's a problem because (a) that is one place where
SECURITY_RESTRICTED_OPERATION is not used for security, but rather for
consistency; and (b) that path doesn't go through rewriter, planner, or
executor anyway so I don't see why it would matter.

* Use GUC_ACTION_SAVE rather than GUC_ACTION_SET. That was a problem
with the previous patch for index functions executed in parallel
workers, which can happen calling SQL functions from pg_amcheck.

* I used a wrapper function RestrictSearchPath() rather than calling
set_config_option() directly. That provides a nice place in case we
need to add a compatibility GUC to disable it.

Question:

Why do we switch to the table owner and use
SECURITY_RESTRICTED_OPERATION in DefineIndex(), when we will switch in
index_build (etc.) anyway? Similarly, why do we switch in vacuum_rel(),
when it doesn't matter for lazy vacuum and we will switch in
cluster_rel() and do_analyze_rel() anyway?

For now, I left the extra calls to RestrictSearchPath() in for
consistency with the switches to the table owner.

Regards,
        Jeff Davis

From a2a2468dc9577498d75aaff5ea83726af0fd4d5d Mon Sep 17 00:00:00 2001
From: Jeff Davis <j...@j-davis.com>
Date: Thu, 6 Jul 2023 13:06:22 -0700
Subject: [PATCH v1] Restrict search_path when performing maintenance.

When executing maintenance operations (ANALYZE, CLUSTER,
CREATE/REFRESH MATERIALIZED VIEW, REINDEX, or VACUUM), set search_path
to 'pg_catalog, pg_temp'.

This change avoids problems when maintenance commands are executed
with a different search_path, which could lead to errors or
inconsistent results. It's also a step towards offering the MAINTAIN
privilege safely.

Functions that are used for functional indexes, partial indexes, or in
materialized views that depend on a different search path should be
declared with CREATE FUNCTION ... SET search_path='...'.

A previous version of this change was committed as 05e1737351, but was
not acceptable for 16 and reverted.

Discussion: https://postgr.es/m/CA%2BTgmoZVCHERUkXhAMT2Er-sKBc5C6_iX%2BTpxxivBevDHzq2TQ%40mail.gmail.com
Discussion: https://postgr.es/m/e44327179e5c9015c8dda67351c04da552066017.camel%40j-davis.com
---
 contrib/amcheck/verify_nbtree.c               |   2 +
 .../sgml/ref/create_materialized_view.sgml    |   6 +-
 .../sgml/ref/refresh_materialized_view.sgml   |  19 ++-
 src/backend/access/brin/brin.c                |   2 +
 src/backend/catalog/index.c                   |   5 +
 src/backend/commands/analyze.c                |   2 +
 src/backend/commands/cluster.c                |   2 +
 src/backend/commands/createas.c               | 136 ++++++++++--------
 src/backend/commands/indexcmds.c              |   7 +
 src/backend/commands/matview.c                |   2 +
 src/backend/commands/vacuum.c                 |   2 +
 src/backend/utils/init/usercontext.c          |  14 ++
 src/bin/scripts/t/100_vacuumdb.pl             |   4 -
 src/include/utils/usercontext.h               |   1 +
 .../expected/test_oat_hooks.out               |   4 +
 src/test/regress/expected/matview.out         |  40 +++++-
 src/test/regress/expected/privileges.out      |  12 +-
 src/test/regress/expected/vacuum.out          |   2 +-
 src/test/regress/sql/matview.sql              |  23 +++
 src/test/regress/sql/privileges.sql           |   8 +-
 src/test/regress/sql/vacuum.sql               |   2 +-
 21 files changed, 212 insertions(+), 83 deletions(-)

diff --git a/contrib/amcheck/verify_nbtree.c b/contrib/amcheck/verify_nbtree.c
index 94a9759322..bc43808d35 100644
--- a/contrib/amcheck/verify_nbtree.c
+++ b/contrib/amcheck/verify_nbtree.c
@@ -40,6 +40,7 @@
 #include "utils/guc.h"
 #include "utils/memutils.h"
 #include "utils/snapmgr.h"
+#include "utils/usercontext.h"
 
 
 PG_MODULE_MAGIC;
@@ -281,6 +282,7 @@ bt_index_check_internal(Oid indrelid, bool parentcheck, bool heapallindexed,
 		SetUserIdAndSecContext(heaprel->rd_rel->relowner,
 							   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 		save_nestlevel = NewGUCNestLevel();
+		RestrictSearchPath();
 	}
 	else
 	{
diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml
index 0d2fea2b97..06e3d80f70 100644
--- a/doc/src/sgml/ref/create_materialized_view.sgml
+++ b/doc/src/sgml/ref/create_materialized_view.sgml
@@ -37,8 +37,10 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
   <para>
    <command>CREATE MATERIALIZED VIEW</command> defines a materialized view of
    a query.  The query is executed and used to populate the view at the time
-   the command is issued (unless <command>WITH NO DATA</command> is used) and may be
-   refreshed later using <command>REFRESH MATERIALIZED VIEW</command>.
+   the command is issued (unless <command>WITH NO DATA</command> is used) and
+   may be refreshed later using <command>REFRESH MATERIALIZED VIEW</command>.
+   While the query is executing, the <xref linkend="guc-search-path"/> is set
+   to <literal>pg_catalog, pg_temp</literal>.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/refresh_materialized_view.sgml b/doc/src/sgml/ref/refresh_materialized_view.sgml
index 675d6090f3..a2232a6438 100644
--- a/doc/src/sgml/ref/refresh_materialized_view.sgml
+++ b/doc/src/sgml/ref/refresh_materialized_view.sgml
@@ -32,12 +32,19 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="parameter">name</
   <para>
    <command>REFRESH MATERIALIZED VIEW</command> completely replaces the
    contents of a materialized view.  To execute this command you must be the
-   owner of the materialized view.  The old contents are discarded.  If
-   <literal>WITH DATA</literal> is specified (or defaults) the backing query
-   is executed to provide the new data, and the materialized view is left in a
-   scannable state.  If <literal>WITH NO DATA</literal> is specified no new
-   data is generated and the materialized view is left in an unscannable
-   state.
+   owner of the materialized view.  The old contents are discarded.
+  </para>
+  <para>
+   If <literal>WITH DATA</literal> is specified (or defaults) the backing
+   query is executed to provide the new data, and the materialized view is
+   left in a scannable state.  The query is executed as the owner of the
+   materialized view; and while the query is executing the <xref
+   linkend="guc-search-path"/> is set to <literal>pg_catalog,
+   pg_temp</literal>.
+  </para>
+  <para>
+   If <literal>WITH NO DATA</literal> is specified no new data is generated
+   and the materialized view is left in an unscannable state.
   </para>
   <para>
    <literal>CONCURRENTLY</literal> and <literal>WITH NO DATA</literal> may not
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index 3c6a956eaa..ec7e135663 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -40,6 +40,7 @@
 #include "utils/index_selfuncs.h"
 #include "utils/memutils.h"
 #include "utils/rel.h"
+#include "utils/usercontext.h"
 
 
 /*
@@ -1066,6 +1067,7 @@ brin_summarize_range(PG_FUNCTION_ARGS)
 		SetUserIdAndSecContext(heapRel->rd_rel->relowner,
 							   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 		save_nestlevel = NewGUCNestLevel();
+		RestrictSearchPath();
 	}
 	else
 	{
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index eb2b8d84c3..c2f3398ac9 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -85,6 +85,7 @@
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
 #include "utils/tuplesort.h"
+#include "utils/usercontext.h"
 
 /* Potentially set by pg_upgrade_support functions */
 Oid			binary_upgrade_next_index_pg_class_oid = InvalidOid;
@@ -1476,6 +1477,7 @@ index_concurrently_build(Oid heapRelationId,
 	SetUserIdAndSecContext(heapRel->rd_rel->relowner,
 						   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 	save_nestlevel = NewGUCNestLevel();
+	RestrictSearchPath();
 
 	indexRelation = index_open(indexRelationId, RowExclusiveLock);
 
@@ -3007,6 +3009,7 @@ index_build(Relation heapRelation,
 	SetUserIdAndSecContext(heapRelation->rd_rel->relowner,
 						   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 	save_nestlevel = NewGUCNestLevel();
+	RestrictSearchPath();
 
 	/* Set up initial progress report status */
 	{
@@ -3343,6 +3346,7 @@ validate_index(Oid heapId, Oid indexId, Snapshot snapshot)
 	SetUserIdAndSecContext(heapRelation->rd_rel->relowner,
 						   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 	save_nestlevel = NewGUCNestLevel();
+	RestrictSearchPath();
 
 	indexRelation = index_open(indexId, RowExclusiveLock);
 
@@ -3603,6 +3607,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
 	SetUserIdAndSecContext(heapRelation->rd_rel->relowner,
 						   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 	save_nestlevel = NewGUCNestLevel();
+	RestrictSearchPath();
 
 	if (progress)
 	{
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index bfd981aa3f..fe730970cf 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -67,6 +67,7 @@
 #include "utils/spccache.h"
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
+#include "utils/usercontext.h"
 
 
 /* Per-index data for ANALYZE */
@@ -349,6 +350,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
 	SetUserIdAndSecContext(onerel->rd_rel->relowner,
 						   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 	save_nestlevel = NewGUCNestLevel();
+	RestrictSearchPath();
 
 	/* measure elapsed time iff autovacuum logging requires it */
 	if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0)
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index a3bef6ac34..1730a3cd59 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -59,6 +59,7 @@
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
 #include "utils/tuplesort.h"
+#include "utils/usercontext.h"
 
 /*
  * This struct is used to pass around the information on tables to be
@@ -353,6 +354,7 @@ cluster_rel(Oid tableOid, Oid indexOid, ClusterParams *params)
 	SetUserIdAndSecContext(OldHeap->rd_rel->relowner,
 						   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 	save_nestlevel = NewGUCNestLevel();
+	RestrictSearchPath();
 
 	/*
 	 * Since we may open a new transaction for each relation, we have to check
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index e91920ca14..8e8e55641f 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -50,6 +50,7 @@
 #include "utils/rel.h"
 #include "utils/rls.h"
 #include "utils/snapmgr.h"
+#include "utils/usercontext.h"
 
 typedef struct
 {
@@ -61,6 +62,8 @@ typedef struct
 	CommandId	output_cid;		/* cmin to insert in output tuples */
 	int			ti_options;		/* table_tuple_insert performance options */
 	BulkInsertState bistate;	/* bulk insert state */
+	int		   *save_nestlevel;	/* initialized in ExecCreateTableAs if new GUC
+								 * nest level is created */
 } DR_intorel;
 
 /* utility functions for CTAS definition creation */
@@ -267,6 +270,17 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
 	}
 	Assert(query->commandType == CMD_SELECT);
 
+	if (into->skipData)
+	{
+		/*
+		 * If WITH NO DATA was specified, do not go through the rewriter,
+		 * planner and executor.  Just define the relation using a code path
+		 * similar to CREATE VIEW.  This avoids dump/restore problems stemming
+		 * from running the planner before all dependencies are set up.
+		 */
+		return create_ctas_nodata(query->targetList, into);
+	}
+
 	/*
 	 * For materialized views, lock down security-restricted operations and
 	 * arrange to make GUC variable changes local to this command.  This is
@@ -280,76 +294,65 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
 		SetUserIdAndSecContext(save_userid,
 							   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 		save_nestlevel = NewGUCNestLevel();
+		RestrictSearchPath();
+		((DR_intorel *) dest)->save_nestlevel = &save_nestlevel;
 	}
 
-	if (into->skipData)
-	{
-		/*
-		 * If WITH NO DATA was specified, do not go through the rewriter,
-		 * planner and executor.  Just define the relation using a code path
-		 * similar to CREATE VIEW.  This avoids dump/restore problems stemming
-		 * from running the planner before all dependencies are set up.
-		 */
-		address = create_ctas_nodata(query->targetList, into);
-	}
-	else
-	{
-		/*
-		 * Parse analysis was done already, but we still have to run the rule
-		 * rewriter.  We do not do AcquireRewriteLocks: we assume the query
-		 * either came straight from the parser, or suitable locks were
-		 * acquired by plancache.c.
-		 */
-		rewritten = QueryRewrite(query);
-
-		/* SELECT should never rewrite to more or less than one SELECT query */
-		if (list_length(rewritten) != 1)
-			elog(ERROR, "unexpected rewrite result for %s",
-				 is_matview ? "CREATE MATERIALIZED VIEW" :
-				 "CREATE TABLE AS SELECT");
-		query = linitial_node(Query, rewritten);
-		Assert(query->commandType == CMD_SELECT);
+	/*
+	 * Parse analysis was done already, but we still have to run the rule
+	 * rewriter.  We do not do AcquireRewriteLocks: we assume the query either
+	 * came straight from the parser, or suitable locks were acquired by
+	 * plancache.c.
+	 */
+	rewritten = QueryRewrite(query);
+
+	/* SELECT should never rewrite to more or less than one SELECT query */
+	if (list_length(rewritten) != 1)
+		elog(ERROR, "unexpected rewrite result for %s",
+			 is_matview ? "CREATE MATERIALIZED VIEW" :
+			 "CREATE TABLE AS SELECT");
+	query = linitial_node(Query, rewritten);
+	Assert(query->commandType == CMD_SELECT);
 
-		/* plan the query */
-		plan = pg_plan_query(query, pstate->p_sourcetext,
-							 CURSOR_OPT_PARALLEL_OK, params);
+	/* plan the query */
+	plan = pg_plan_query(query, pstate->p_sourcetext,
+						 CURSOR_OPT_PARALLEL_OK, params);
 
-		/*
-		 * Use a snapshot with an updated command ID to ensure this query sees
-		 * results of any previously executed queries.  (This could only
-		 * matter if the planner executed an allegedly-stable function that
-		 * changed the database contents, but let's do it anyway to be
-		 * parallel to the EXPLAIN code path.)
-		 */
-		PushCopiedSnapshot(GetActiveSnapshot());
-		UpdateActiveSnapshotCommandId();
+	/*
+	 * Use a snapshot with an updated command ID to ensure this query sees
+	 * results of any previously executed queries.  (This could only matter if
+	 * the planner executed an allegedly-stable function that changed the
+	 * database contents, but let's do it anyway to be parallel to the EXPLAIN
+	 * code path.)
+	 */
+	PushCopiedSnapshot(GetActiveSnapshot());
+	UpdateActiveSnapshotCommandId();
 
-		/* Create a QueryDesc, redirecting output to our tuple receiver */
-		queryDesc = CreateQueryDesc(plan, pstate->p_sourcetext,
-									GetActiveSnapshot(), InvalidSnapshot,
-									dest, params, queryEnv, 0);
+	/* Create a QueryDesc, redirecting output to our tuple receiver */
+	queryDesc = CreateQueryDesc(plan, pstate->p_sourcetext,
+								GetActiveSnapshot(), InvalidSnapshot,
+								dest, params, queryEnv, 0);
 
-		/* call ExecutorStart to prepare the plan for execution */
-		ExecutorStart(queryDesc, GetIntoRelEFlags(into));
+	/* call ExecutorStart to prepare the plan for execution */
+	ExecutorStart(queryDesc, GetIntoRelEFlags(into));
 
-		/* run the plan to completion */
-		ExecutorRun(queryDesc, ForwardScanDirection, 0, true);
+	/* run the plan to completion */
+	ExecutorRun(queryDesc, ForwardScanDirection, 0, true);
 
-		/* save the rowcount if we're given a qc to fill */
-		if (qc)
-			SetQueryCompletion(qc, CMDTAG_SELECT, queryDesc->estate->es_processed);
+	/* save the rowcount if we're given a qc to fill */
+	if (qc)
+		SetQueryCompletion(qc, CMDTAG_SELECT, queryDesc->estate->es_processed);
 
-		/* get object address that intorel_startup saved for us */
-		address = ((DR_intorel *) dest)->reladdr;
+	/* get object address that intorel_startup saved for us */
+	address = ((DR_intorel *) dest)->reladdr;
 
-		/* and clean up */
-		ExecutorFinish(queryDesc);
-		ExecutorEnd(queryDesc);
+	/* and clean up */
+	ExecutorFinish(queryDesc);
+	ExecutorEnd(queryDesc);
 
-		FreeQueryDesc(queryDesc);
+	FreeQueryDesc(queryDesc);
 
-		PopActiveSnapshot();
-	}
+	PopActiveSnapshot();
 
 	if (is_matview)
 	{
@@ -522,11 +525,28 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("too many column names were specified")));
 
+	/*
+	 * Restore search_path to original value so that the object is created in
+	 * the correct namespace.
+	 */
+	if (myState->save_nestlevel)
+		AtEOXact_GUC(false, *myState->save_nestlevel);
+
 	/*
 	 * Actually create the target table
 	 */
 	intoRelationAddr = create_ctas_internal(attrList, into);
 
+	/*
+	 * Restrict the search_path again for execution of the materialized view
+	 * query.
+	 */
+	if (myState->save_nestlevel)
+	{
+		*myState->save_nestlevel = NewGUCNestLevel();
+		RestrictSearchPath();
+	}
+
 	/*
 	 * Finally we can open the target table
 	 */
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index baf3e6e57a..e855a50df8 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -68,6 +68,7 @@
 #include "utils/regproc.h"
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
+#include "utils/usercontext.h"
 
 
 /* non-export function prototypes */
@@ -574,6 +575,7 @@ DefineIndex(Oid relationId,
 	int			root_save_nestlevel;
 
 	root_save_nestlevel = NewGUCNestLevel();
+	RestrictSearchPath();
 
 	/*
 	 * Some callers need us to run with an empty default_tablespace; this is a
@@ -1316,6 +1318,7 @@ DefineIndex(Oid relationId,
 				SetUserIdAndSecContext(childrel->rd_rel->relowner,
 									   child_save_sec_context | SECURITY_RESTRICTED_OPERATION);
 				child_save_nestlevel = NewGUCNestLevel();
+				RestrictSearchPath();
 
 				/*
 				 * Don't try to create indexes on foreign tables, though. Skip
@@ -2038,6 +2041,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
 			{
 				SetUserIdAndSecContext(save_userid, save_sec_context);
 				*ddl_save_nestlevel = NewGUCNestLevel();
+				RestrictSearchPath();
 			}
 		}
 
@@ -2085,6 +2089,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
 		{
 			SetUserIdAndSecContext(save_userid, save_sec_context);
 			*ddl_save_nestlevel = NewGUCNestLevel();
+			RestrictSearchPath();
 		}
 
 		/*
@@ -2115,6 +2120,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
 			{
 				SetUserIdAndSecContext(save_userid, save_sec_context);
 				*ddl_save_nestlevel = NewGUCNestLevel();
+				RestrictSearchPath();
 			}
 
 			/*
@@ -3778,6 +3784,7 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 		SetUserIdAndSecContext(heapRel->rd_rel->relowner,
 							   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 		save_nestlevel = NewGUCNestLevel();
+		RestrictSearchPath();
 
 		/* determine safety of this index for set_indexsafe_procflags */
 		idx->safe = (indexRel->rd_indexprs == NIL &&
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index ac2e74fa3f..c2f4c28c51 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -45,6 +45,7 @@
 #include "utils/rel.h"
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
+#include "utils/usercontext.h"
 
 
 typedef struct
@@ -178,6 +179,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
 	SetUserIdAndSecContext(relowner,
 						   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 	save_nestlevel = NewGUCNestLevel();
+	RestrictSearchPath();
 
 	/* Make sure it is a materialized view. */
 	if (matviewRel->rd_rel->relkind != RELKIND_MATVIEW)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 69ac276687..a791bd9d42 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -61,6 +61,7 @@
 #include "utils/pg_rusage.h"
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
+#include "utils/usercontext.h"
 
 
 /*
@@ -2187,6 +2188,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params,
 	SetUserIdAndSecContext(rel->rd_rel->relowner,
 						   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 	save_nestlevel = NewGUCNestLevel();
+	RestrictSearchPath();
 
 	/*
 	 * If PROCESS_MAIN is set (the default), it's time to vacuum the main
diff --git a/src/backend/utils/init/usercontext.c b/src/backend/utils/init/usercontext.c
index dd9a0dd6a8..cd037c0c6a 100644
--- a/src/backend/utils/init/usercontext.c
+++ b/src/backend/utils/init/usercontext.c
@@ -90,3 +90,17 @@ RestoreUserContext(UserContext *context)
 		AtEOXact_GUC(false, context->save_nestlevel);
 	SetUserIdAndSecContext(context->save_userid, context->save_sec_context);
 }
+
+/*
+ * Set search_path to a safe and consistent value. The caller should have
+ * already called NewGUCNestLevel().
+ *
+ * This is important when executing code as another user; for example, when
+ * running a maintenance command on a table with a functional index.
+ */
+void
+RestrictSearchPath()
+{
+	set_config_option("search_path", "pg_catalog, pg_temp", PGC_USERSET,
+					  PGC_S_SESSION, GUC_ACTION_SAVE, true, 0, false);
+}
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index eccfcc54a1..f91b5127a8 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -109,15 +109,11 @@ $node->safe_psql(
   CREATE FUNCTION f1(int) RETURNS int LANGUAGE SQL AS 'SELECT f0($1)';
   CREATE TABLE funcidx (x int);
   INSERT INTO funcidx VALUES (0),(1),(2),(3);
-  CREATE INDEX i0 ON funcidx ((f1(x)));
   CREATE SCHEMA "Foo";
   CREATE TABLE "Foo".bar(id int);
 |);
 $node->command_ok([qw|vacuumdb -Z --table="need""q(uot"(")x") postgres|],
 	'column list');
-$node->command_fails(
-	[qw|vacuumdb -Zt funcidx postgres|],
-	'unqualified name via functional index');
 
 $node->command_fails(
 	[ 'vacuumdb', '--analyze', '--table', 'vactable(c)', 'postgres' ],
diff --git a/src/include/utils/usercontext.h b/src/include/utils/usercontext.h
index a8195c194d..548980ced8 100644
--- a/src/include/utils/usercontext.h
+++ b/src/include/utils/usercontext.h
@@ -22,5 +22,6 @@ typedef struct UserContext
 /* Function prototypes. */
 extern void SwitchToUntrustedUser(Oid userid, UserContext *context);
 extern void RestoreUserContext(UserContext *context);
+extern void RestrictSearchPath(void);
 
 #endif							/* USERCONTEXT_H */
diff --git a/src/test/modules/test_oat_hooks/expected/test_oat_hooks.out b/src/test/modules/test_oat_hooks/expected/test_oat_hooks.out
index f80373aecc..effdc49145 100644
--- a/src/test/modules/test_oat_hooks/expected/test_oat_hooks.out
+++ b/src/test/modules/test_oat_hooks/expected/test_oat_hooks.out
@@ -89,11 +89,15 @@ NOTICE:  in object access: superuser finished create (subId=0x0) [internal]
 NOTICE:  in process utility: superuser finished CREATE TABLE
 CREATE INDEX regress_test_table_t_idx ON regress_test_table (t);
 NOTICE:  in process utility: superuser attempting CREATE INDEX
+NOTICE:  in object access: superuser attempting namespace search (subId=0x0) [no report on violation, allowed]
+NOTICE:  in object access: superuser finished namespace search (subId=0x0) [no report on violation, allowed]
 NOTICE:  in object access: superuser attempting create (subId=0x0) [explicit]
 NOTICE:  in object access: superuser finished create (subId=0x0) [explicit]
 NOTICE:  in process utility: superuser finished CREATE INDEX
 GRANT SELECT ON Table regress_test_table TO public;
 NOTICE:  in process utility: superuser attempting GRANT
+NOTICE:  in object access: superuser attempting namespace search (subId=0x0) [no report on violation, allowed]
+NOTICE:  in object access: superuser finished namespace search (subId=0x0) [no report on violation, allowed]
 NOTICE:  in process utility: superuser finished GRANT
 CREATE FUNCTION regress_test_func (t text) RETURNS text AS $$
 	SELECT $1;
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 7cb05827ca..f03aebf10a 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -631,12 +631,50 @@ REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2;
 RESET SESSION AUTHORIZATION;
 ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
   GRANT INSERT ON TABLES TO regress_matview_user;
+-- Test that MV is populated using restricted search_path
+SET SESSION AUTHORIZATION regress_matview_user;
+SET search_path = matview_schema;
+CREATE FUNCTION matview_schema.mod(a INT, b INT) RETURNS INT
+  IMMUTABLE LANGUAGE plpgsql AS
+  $$ BEGIN RETURN 42; END; $$;
+CREATE FUNCTION matview_schema.mod10(i INT) RETURNS INT
+  IMMUTABLE LANGUAGE plpgsql AS
+  $$ BEGIN RETURN mod(i, 10); END; $$;
+CREATE MATERIALIZED VIEW matview_search_path AS
+  SELECT current_user, matview_schema.mod10(33);
+SELECT * FROM matview_search_path;
+     current_user     | mod10 
+----------------------+-------
+ regress_matview_user |     3
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+REFRESH MATERIALIZED VIEW matview_search_path;
+SELECT * FROM matview_search_path;
+     current_user     | mod10 
+----------------------+-------
+ regress_matview_user |     3
+(1 row)
+
+ALTER FUNCTION matview_schema.mod10(INT)
+  SET search_path = matview_schema, pg_catalog;
+REFRESH MATERIALIZED VIEW matview_search_path;
+SELECT * FROM matview_search_path;
+     current_user     | mod10 
+----------------------+-------
+ regress_matview_user |    42
+(1 row)
+
+RESET search_path;
 DROP SCHEMA matview_schema CASCADE;
-NOTICE:  drop cascades to 4 other objects
+NOTICE:  drop cascades to 7 other objects
 DETAIL:  drop cascades to materialized view matview_schema.mv_withdata1
 drop cascades to materialized view matview_schema.mv_withdata2
 drop cascades to materialized view matview_schema.mv_nodata1
 drop cascades to materialized view matview_schema.mv_nodata2
+drop cascades to function matview_schema.mod(integer,integer)
+drop cascades to function matview_schema.mod10(integer)
+drop cascades to materialized view matview_schema.matview_search_path
 DROP USER regress_matview_user;
 -- CREATE MATERIALIZED VIEW ... IF NOT EXISTS
 CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1;
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index c1e610e62f..1c36d7f894 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -1769,7 +1769,7 @@ SET SESSION AUTHORIZATION regress_sro_user;
 CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS
 	'GRANT regress_priv_group2 TO regress_sro_user';
 CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS
-	'DECLARE c CURSOR WITH HOLD FOR SELECT unwanted_grant(); SELECT true';
+	'DECLARE c CURSOR WITH HOLD FOR SELECT public.unwanted_grant(); SELECT true';
 -- REFRESH of this MV will queue a GRANT at end of transaction
 CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA;
 REFRESH MATERIALIZED VIEW sro_mv;
@@ -1783,12 +1783,12 @@ SET SESSION AUTHORIZATION regress_sro_user;
 -- INSERT to this table will queue a GRANT at end of transaction
 CREATE TABLE sro_trojan_table ();
 CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS
-	'BEGIN PERFORM unwanted_grant(); RETURN NULL; END';
+	'BEGIN PERFORM public.unwanted_grant(); RETURN NULL; END';
 CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table
     INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan();
 -- Now, REFRESH will issue such an INSERT, queueing the GRANT
 CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS
-	'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true';
+	'INSERT INTO public.sro_trojan_table DEFAULT VALUES; SELECT true';
 REFRESH MATERIALIZED VIEW sro_mv;
 ERROR:  cannot fire deferred trigger within security-restricted operation
 CONTEXT:  SQL function "mv_action" statement 1
@@ -1800,15 +1800,15 @@ BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT;
 ERROR:  permission denied to grant role "regress_priv_group2"
 DETAIL:  Only roles with the ADMIN option on role "regress_priv_group2" may grant this role.
 CONTEXT:  SQL function "unwanted_grant" statement 1
-SQL statement "SELECT unwanted_grant()"
-PL/pgSQL function sro_trojan() line 1 at PERFORM
+SQL statement "SELECT public.unwanted_grant()"
+PL/pgSQL function public.sro_trojan() line 1 at PERFORM
 SQL function "mv_action" statement 1
 -- REFRESH MATERIALIZED VIEW CONCURRENTLY use of eval_const_expressions()
 SET SESSION AUTHORIZATION regress_sro_user;
 CREATE FUNCTION unwanted_grant_nofail(int) RETURNS int
 	IMMUTABLE LANGUAGE plpgsql AS $$
 BEGIN
-	PERFORM unwanted_grant();
+	PERFORM public.unwanted_grant();
 	RAISE WARNING 'owned';
 	RETURN 1;
 EXCEPTION WHEN OTHERS THEN
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 4def90b805..330fcd884c 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -64,7 +64,7 @@ CLUSTER vaccluster;
 CREATE FUNCTION do_analyze() RETURNS VOID VOLATILE LANGUAGE SQL
 	AS 'ANALYZE pg_am';
 CREATE FUNCTION wrap_do_analyze(c INT) RETURNS INT IMMUTABLE LANGUAGE SQL
-	AS 'SELECT $1 FROM do_analyze()';
+	AS 'SELECT $1 FROM public.do_analyze()';
 CREATE INDEX ON vaccluster(wrap_do_analyze(i));
 INSERT INTO vaccluster VALUES (1), (2);
 ANALYZE vaccluster;
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index acc4519d01..9c026be325 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -270,6 +270,29 @@ RESET SESSION AUTHORIZATION;
 ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
   GRANT INSERT ON TABLES TO regress_matview_user;
 
+-- Test that MV is populated using restricted search_path
+SET SESSION AUTHORIZATION regress_matview_user;
+SET search_path = matview_schema;
+CREATE FUNCTION matview_schema.mod(a INT, b INT) RETURNS INT
+  IMMUTABLE LANGUAGE plpgsql AS
+  $$ BEGIN RETURN 42; END; $$;
+CREATE FUNCTION matview_schema.mod10(i INT) RETURNS INT
+  IMMUTABLE LANGUAGE plpgsql AS
+  $$ BEGIN RETURN mod(i, 10); END; $$;
+CREATE MATERIALIZED VIEW matview_search_path AS
+  SELECT current_user, matview_schema.mod10(33);
+SELECT * FROM matview_search_path;
+RESET SESSION AUTHORIZATION;
+REFRESH MATERIALIZED VIEW matview_search_path;
+SELECT * FROM matview_search_path;
+
+ALTER FUNCTION matview_schema.mod10(INT)
+  SET search_path = matview_schema, pg_catalog;
+REFRESH MATERIALIZED VIEW matview_search_path;
+SELECT * FROM matview_search_path;
+
+RESET search_path;
+
 DROP SCHEMA matview_schema CASCADE;
 DROP USER regress_matview_user;
 
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index bf0035d96d..8eed1f6735 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1177,7 +1177,7 @@ SET SESSION AUTHORIZATION regress_sro_user;
 CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS
 	'GRANT regress_priv_group2 TO regress_sro_user';
 CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS
-	'DECLARE c CURSOR WITH HOLD FOR SELECT unwanted_grant(); SELECT true';
+	'DECLARE c CURSOR WITH HOLD FOR SELECT public.unwanted_grant(); SELECT true';
 -- REFRESH of this MV will queue a GRANT at end of transaction
 CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA;
 REFRESH MATERIALIZED VIEW sro_mv;
@@ -1188,12 +1188,12 @@ SET SESSION AUTHORIZATION regress_sro_user;
 -- INSERT to this table will queue a GRANT at end of transaction
 CREATE TABLE sro_trojan_table ();
 CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS
-	'BEGIN PERFORM unwanted_grant(); RETURN NULL; END';
+	'BEGIN PERFORM public.unwanted_grant(); RETURN NULL; END';
 CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table
     INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan();
 -- Now, REFRESH will issue such an INSERT, queueing the GRANT
 CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS
-	'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true';
+	'INSERT INTO public.sro_trojan_table DEFAULT VALUES; SELECT true';
 REFRESH MATERIALIZED VIEW sro_mv;
 \c -
 REFRESH MATERIALIZED VIEW sro_mv;
@@ -1204,7 +1204,7 @@ SET SESSION AUTHORIZATION regress_sro_user;
 CREATE FUNCTION unwanted_grant_nofail(int) RETURNS int
 	IMMUTABLE LANGUAGE plpgsql AS $$
 BEGIN
-	PERFORM unwanted_grant();
+	PERFORM public.unwanted_grant();
 	RAISE WARNING 'owned';
 	RETURN 1;
 EXCEPTION WHEN OTHERS THEN
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 51d7b1fecc..0b63ef8dc6 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -49,7 +49,7 @@ CLUSTER vaccluster;
 CREATE FUNCTION do_analyze() RETURNS VOID VOLATILE LANGUAGE SQL
 	AS 'ANALYZE pg_am';
 CREATE FUNCTION wrap_do_analyze(c INT) RETURNS INT IMMUTABLE LANGUAGE SQL
-	AS 'SELECT $1 FROM do_analyze()';
+	AS 'SELECT $1 FROM public.do_analyze()';
 CREATE INDEX ON vaccluster(wrap_do_analyze(i));
 INSERT INTO vaccluster VALUES (1), (2);
 ANALYZE vaccluster;
-- 
2.34.1

Reply via email to