Fix search_path to a safe value during maintenance operations.

While executing maintenance operations (ANALYZE, CLUSTER, REFRESH
MATERIALIZED VIEW, REINDEX, or VACUUM), set search_path to
'pg_catalog, pg_temp' to prevent inconsistent behavior.

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

This change was previously committed as 05e1737351, then reverted in
commit 2fcc7ee7af because it was too late in the cycle.

Preparation for the MAINTAIN privilege, which was previously reverted
due to search_path manipulation hazards.

Discussion: https://postgr.es/m/d4ccaf3658cb3c281ec88c851a09733cd9482f22.camel@j-davis.com
Discussion: https://postgr.es/m/E1q7j7Y-000z1H-Hr%40gemulon.postgresql.org
Discussion: https://postgr.es/m/e44327179e5c9015c8dda67351c04da552066017.camel%40j-davis.com
Reviewed-by: Greg Stark, Nathan Bossart, Noah Misch

2 files changed, 22 insertions, 13 deletions

diff --git a/contrib/amcheck/t/004_verify_nbtree_unique.pl b/contrib/amcheck/t/004_verify_nbtree_unique.pl
index 3f474a158a0..4b704e68151 100644
--- a/contrib/amcheck/t/004_verify_nbtree_unique.pl
+++ b/contrib/amcheck/t/004_verify_nbtree_unique.pl
@@ -20,8 +20,11 @@ $node->safe_psql(
 	'postgres', q(
 	CREATE EXTENSION amcheck;
 
+	CREATE SCHEMA test_amcheck;
+	SET search_path = test_amcheck;
+
 	CREATE FUNCTION ok_cmp (int4, int4)
-	RETURNS int LANGUAGE sql AS
+	RETURNS int LANGUAGE sql SET search_path = test_amcheck AS
 	$$
 		SELECT
 			CASE WHEN $1 < $2 THEN -1
@@ -34,7 +37,7 @@ $node->safe_psql(
 	--- Check 1: uniqueness violation.
 	---
 	CREATE FUNCTION ok_cmp1 (int4, int4)
-	RETURNS int LANGUAGE sql AS
+	RETURNS int LANGUAGE sql SET search_path = test_amcheck AS
 	$$
 		SELECT ok_cmp($1, $2);
 	$$;
@@ -43,7 +46,7 @@ $node->safe_psql(
 	--- Make values 768 and 769 look equal.
 	---
 	CREATE FUNCTION bad_cmp1 (int4, int4)
-	RETURNS int LANGUAGE sql AS
+	RETURNS int LANGUAGE sql SET search_path = test_amcheck AS
 	$$
 		SELECT
 			CASE WHEN ($1 = 768 AND $2 = 769) OR
@@ -56,13 +59,13 @@ $node->safe_psql(
 	--- Check 2: uniqueness violation without deduplication.
 	---
 	CREATE FUNCTION ok_cmp2 (int4, int4)
-	RETURNS int LANGUAGE sql AS
+	RETURNS int LANGUAGE sql SET search_path = test_amcheck AS
 	$$
 		SELECT ok_cmp($1, $2);
 	$$;
 
 	CREATE FUNCTION bad_cmp2 (int4, int4)
-	RETURNS int LANGUAGE sql AS
+	RETURNS int LANGUAGE sql SET search_path = test_amcheck AS
 	$$
 		SELECT
 			CASE WHEN $1 = $2 AND $1 = 400 THEN -1
@@ -74,13 +77,13 @@ $node->safe_psql(
 	--- Check 3: uniqueness violation with deduplication.
 	---
 	CREATE FUNCTION ok_cmp3 (int4, int4)
-	RETURNS int LANGUAGE sql AS
+	RETURNS int LANGUAGE sql SET search_path = test_amcheck AS
 	$$
 		SELECT ok_cmp($1, $2);
 	$$;
 
 	CREATE FUNCTION bad_cmp3 (int4, int4)
-	RETURNS int LANGUAGE sql AS
+	RETURNS int LANGUAGE sql SET search_path = test_amcheck AS
 	$$
 		SELECT bad_cmp2($1, $2);
 	$$;
@@ -142,7 +145,7 @@ my ($result, $stdout, $stderr);
 # We have not yet broken the index, so we should get no corruption
 $result = $node->safe_psql(
 	'postgres', q(
-	SELECT bt_index_check('bttest_unique_idx1', true, true);
+	SELECT bt_index_check('test_amcheck.bttest_unique_idx1', true, true);
 ));
 is($result, '', 'run amcheck on non-broken bttest_unique_idx1');
 
@@ -150,6 +153,7 @@ is($result, '', 'run amcheck on non-broken bttest_unique_idx1');
 # values to be equal.
 $node->safe_psql(
 	'postgres', q(
+	SET search_path = test_amcheck;
 	UPDATE pg_catalog.pg_amproc SET
 		   amproc = 'bad_cmp1'::regproc
 	WHERE amproc = 'ok_cmp1'::regproc;
@@ -157,7 +161,7 @@ $node->safe_psql(
 
 ($result, $stdout, $stderr) = $node->psql(
 	'postgres', q(
-	SELECT bt_index_check('bttest_unique_idx1', true, true);
+	SELECT bt_index_check('test_amcheck.bttest_unique_idx1', true, true);
 ));
 ok( $stderr =~ /index uniqueness is violated for index "bttest_unique_idx1"/,
 	'detected uniqueness violation for index "bttest_unique_idx1"');
@@ -175,13 +179,14 @@ ok( $stderr =~ /index uniqueness is violated for index "bttest_unique_idx1"/,
 # but no uniqueness violation.
 ($result, $stdout, $stderr) = $node->psql(
 	'postgres', q(
-	SELECT bt_index_check('bttest_unique_idx2', true, true);
+	SELECT bt_index_check('test_amcheck.bttest_unique_idx2', true, true);
 ));
 ok( $stderr =~ /item order invariant violated for index "bttest_unique_idx2"/,
 	'detected item order invariant violation for index "bttest_unique_idx2"');
 
 $node->safe_psql(
 	'postgres', q(
+	SET search_path = test_amcheck;
 	UPDATE pg_catalog.pg_amproc SET
 		   amproc = 'ok_cmp2'::regproc
 	WHERE amproc = 'bad_cmp2'::regproc;
@@ -189,7 +194,7 @@ $node->safe_psql(
 
 ($result, $stdout, $stderr) = $node->psql(
 	'postgres', q(
-	SELECT bt_index_check('bttest_unique_idx2', true, true);
+	SELECT bt_index_check('test_amcheck.bttest_unique_idx2', true, true);
 ));
 ok( $stderr =~ /index uniqueness is violated for index "bttest_unique_idx2"/,
 	'detected uniqueness violation for index "bttest_unique_idx2"');
@@ -206,7 +211,7 @@ ok( $stderr =~ /index uniqueness is violated for index "bttest_unique_idx2"/,
 # but no uniqueness violation.
 ($result, $stdout, $stderr) = $node->psql(
 	'postgres', q(
-	SELECT bt_index_check('bttest_unique_idx3', true, true);
+	SELECT bt_index_check('test_amcheck.bttest_unique_idx3', true, true);
 ));
 ok( $stderr =~ /item order invariant violated for index "bttest_unique_idx3"/,
 	'detected item order invariant violation for index "bttest_unique_idx3"');
@@ -215,6 +220,7 @@ ok( $stderr =~ /item order invariant violated for index "bttest_unique_idx3"/,
 # with different visibility.
 $node->safe_psql(
 	'postgres', q(
+	SET search_path = test_amcheck;
 	DELETE FROM bttest_unique3 WHERE 380 <= i AND i <= 420;
 	INSERT INTO bttest_unique3 (SELECT * FROM generate_series(380, 420));
 	INSERT INTO bttest_unique3 VALUES (400);
@@ -228,6 +234,7 @@ $node->safe_psql(
 
 $node->safe_psql(
 	'postgres', q(
+	SET search_path = test_amcheck;
 	UPDATE pg_catalog.pg_amproc SET
 		   amproc = 'ok_cmp3'::regproc
 	WHERE amproc = 'bad_cmp3'::regproc;
@@ -235,7 +242,7 @@ $node->safe_psql(
 
 ($result, $stdout, $stderr) = $node->psql(
 	'postgres', q(
-	SELECT bt_index_check('bttest_unique_idx3', true, true);
+	SELECT bt_index_check('test_amcheck.bttest_unique_idx3', true, true);
 ));
 ok( $stderr =~ /index uniqueness is violated for index "bttest_unique_idx3"/,
 	'detected uniqueness violation for index "bttest_unique_idx3"');
diff --git a/contrib/amcheck/verify_nbtree.c b/contrib/amcheck/verify_nbtree.c
index 91caa53dd8b..bff8c61262c 100644
--- a/contrib/amcheck/verify_nbtree.c
+++ b/contrib/amcheck/verify_nbtree.c
@@ -313,6 +313,8 @@ bt_index_check_internal(Oid indrelid, bool parentcheck, bool heapallindexed,
 		SetUserIdAndSecContext(heaprel->rd_rel->relowner,
 							   save_sec_context | SECURITY_RESTRICTED_OPERATION);
 		save_nestlevel = NewGUCNestLevel();
+		SetConfigOption("search_path", GUC_SAFE_SEARCH_PATH, PGC_USERSET,
+						PGC_S_SESSION);
 	}
 	else
 	{