New version attached.
Do we need a documentation update here? If so, where would be a good
place?
On Fri, 2024-02-23 at 15:30 -0600, Nathan Bossart wrote:
> Why is this change needed? Is the idea to make amcheck follow the
> same
> rules as maintenance commands to encourage folks to set up index
> functions
> correctly?
amcheck is calling functions it defined, so in order to find those
functions it needs to set the right search path.
>
> What is the purpose of this [bootstrap-related] change?
DefineIndex() is called during bootstrap, and it's also a maintenance
command. I tried to handle the bootstrapping case, but I think it's
best to just guard it with a conditional. Done.
I also added Assert(!IsBootstrapProcessingMode()) in
assign_search_path().
> > + SetConfigOption("search_path", GUC_SAFE_SEARCH_PATH,
> > PGC_USERSET,
> > + PGC_S_SESSION);
>
> I wonder if it's worth using PGC_S_INTERACTIVE or introducing a new
> value
> for these.
> >
Did you have a particular concern about PGC_S_SESSION?
If it's less than PGC_S_SESSION, it won't work, because the caller's
SET command will override it, and the same manipulation is possible.
And I don't think we want it higher than PGC_S_SESSION, otherwise the
function can't set its own search_path, if needed.
> > +#define GUC_SAFE_SEARCH_PATH "pg_catalog, pg_temp"
>
> Is including pg_temp actually safe? I worry that a user could use
> their
> temporary schema to inject objects that would take the place of
> non-schema-qualified stuff in functions.
pg_temp cannot (currently) be excluded. If it is omitted from the
string, it will be placed *first* in the search_path, which is more
dangerous.
pg_temp does not take part in function or operator resolution, which
makes it safer than it first appears. There are potentially some risks
around tables, but it's not typical to access a table in a function
called as part of an index expression.
If we determine that pg_temp is actually unsafe to include, we need to
do something like what I proposed here:
https://www.postgresql.org/message-id/a6865db287596c9c6ea12bdd9de87216cb5e7902.ca...@j-davis.com
before this change.
Regards,
Jeff Davis
From d628e1b4e2632a7f853e2c1f016758569eaee14e Mon Sep 17 00:00:00 2001
From: Jeff Davis <j...@j-davis.com>
Date: Fri, 16 Feb 2024 14:04:23 -0800
Subject: [PATCH v5] 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.ca...@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
Reviewed-by: Nathan Bossart
---
contrib/amcheck/t/004_verify_nbtree_unique.pl | 33 +++++++++++--------
contrib/amcheck/verify_nbtree.c | 2 ++
src/backend/access/brin/brin.c | 2 ++
src/backend/catalog/index.c | 9 +++++
src/backend/catalog/namespace.c | 3 ++
src/backend/commands/analyze.c | 2 ++
src/backend/commands/cluster.c | 2 ++
src/backend/commands/indexcmds.c | 8 +++++
src/backend/commands/matview.c | 2 ++
src/backend/commands/vacuum.c | 2 ++
src/bin/scripts/t/100_vacuumdb.pl | 4 ---
src/include/utils/guc.h | 6 ++++
.../test_oat_hooks/expected/alter_table.out | 2 ++
.../expected/test_oat_hooks.out | 4 +++
src/test/regress/expected/matview.out | 4 ++-
src/test/regress/expected/privileges.out | 12 +++----
src/test/regress/expected/vacuum.out | 2 +-
src/test/regress/sql/matview.sql | 4 ++-
src/test/regress/sql/privileges.sql | 8 ++---
src/test/regress/sql/vacuum.sql | 2 +-
20 files changed, 82 insertions(+), 31 deletions(-)
diff --git a/contrib/amcheck/t/004_verify_nbtree_unique.pl b/contrib/amcheck/t/004_verify_nbtree_unique.pl
index 3f474a158a..4b704e6815 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 91caa53dd8..bff8c61262 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
{
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index 1087a9011e..d1854c2a1c 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -1412,6 +1412,8 @@ brin_summarize_range(PG_FUNCTION_ARGS)
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
{
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 4b88a9cb87..53278ec89b 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1469,6 +1469,8 @@ index_concurrently_build(Oid heapRelationId,
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);
indexRelation = index_open(indexRelationId, RowExclusiveLock);
@@ -3021,6 +3023,9 @@ index_build(Relation heapRelation,
SetUserIdAndSecContext(heapRelation->rd_rel->relowner,
save_sec_context | SECURITY_RESTRICTED_OPERATION);
save_nestlevel = NewGUCNestLevel();
+ if (!IsBootstrapProcessingMode())
+ SetConfigOption("search_path", GUC_SAFE_SEARCH_PATH, PGC_USERSET,
+ PGC_S_SESSION);
/* Set up initial progress report status */
{
@@ -3356,6 +3361,8 @@ validate_index(Oid heapId, Oid indexId, Snapshot snapshot)
SetUserIdAndSecContext(heapRelation->rd_rel->relowner,
save_sec_context | SECURITY_RESTRICTED_OPERATION);
save_nestlevel = NewGUCNestLevel();
+ SetConfigOption("search_path", GUC_SAFE_SEARCH_PATH, PGC_USERSET,
+ PGC_S_SESSION);
indexRelation = index_open(indexId, RowExclusiveLock);
@@ -3617,6 +3624,8 @@ reindex_index(const ReindexStmt *stmt, Oid indexId,
SetUserIdAndSecContext(heapRelation->rd_rel->relowner,
save_sec_context | SECURITY_RESTRICTED_OPERATION);
save_nestlevel = NewGUCNestLevel();
+ SetConfigOption("search_path", GUC_SAFE_SEARCH_PATH, PGC_USERSET,
+ PGC_S_SESSION);
if (progress)
{
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index 8df30b2440..a68fdd844b 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -4697,6 +4697,9 @@ check_search_path(char **newval, void **extra, GucSource source)
void
assign_search_path(const char *newval, void *extra)
{
+ /* don't access search_path during bootstrap */
+ Assert(!IsBootstrapProcessingMode());
+
/*
* We mark the path as needing recomputation, but don't do anything until
* it's needed. This avoids trying to do database access during GUC
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index a03495d6c9..70ce834c08 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -349,6 +349,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
SetUserIdAndSecContext(onerel->rd_rel->relowner,
save_sec_context | SECURITY_RESTRICTED_OPERATION);
save_nestlevel = NewGUCNestLevel();
+ SetConfigOption("search_path", GUC_SAFE_SEARCH_PATH, PGC_USERSET,
+ PGC_S_SESSION);
/* 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 e2c48ec560..c2666bc386 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -353,6 +353,8 @@ cluster_rel(Oid tableOid, Oid indexOid, ClusterParams *params)
SetUserIdAndSecContext(OldHeap->rd_rel->relowner,
save_sec_context | SECURITY_RESTRICTED_OPERATION);
save_nestlevel = NewGUCNestLevel();
+ SetConfigOption("search_path", GUC_SAFE_SEARCH_PATH, PGC_USERSET,
+ PGC_S_SESSION);
/*
* Since we may open a new transaction for each relation, we have to check
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 7a87626f5f..96eb7d346a 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -585,6 +585,10 @@ DefineIndex(Oid tableId,
root_save_nestlevel = NewGUCNestLevel();
+ if (!IsBootstrapProcessingMode())
+ SetConfigOption("search_path", GUC_SAFE_SEARCH_PATH, PGC_USERSET,
+ PGC_S_SESSION);
+
/*
* Some callers need us to run with an empty default_tablespace; this is a
* necessary hack to be able to reproduce catalog state accurately when
@@ -1340,6 +1344,8 @@ DefineIndex(Oid tableId,
SetUserIdAndSecContext(childrel->rd_rel->relowner,
child_save_sec_context | SECURITY_RESTRICTED_OPERATION);
child_save_nestlevel = NewGUCNestLevel();
+ SetConfigOption("search_path", GUC_SAFE_SEARCH_PATH, PGC_USERSET,
+ PGC_S_SESSION);
/*
* Don't try to create indexes on foreign tables, though. Skip
@@ -3881,6 +3887,8 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
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);
/* 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 59920ced83..c9046ac5a9 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -178,6 +178,8 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
SetUserIdAndSecContext(relowner,
save_sec_context | SECURITY_RESTRICTED_OPERATION);
save_nestlevel = NewGUCNestLevel();
+ SetConfigOption("search_path", GUC_SAFE_SEARCH_PATH, PGC_USERSET,
+ PGC_S_SESSION);
/* 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 64da848627..32a1a80954 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -2168,6 +2168,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params,
SetUserIdAndSecContext(rel->rd_rel->relowner,
save_sec_context | SECURITY_RESTRICTED_OPERATION);
save_nestlevel = NewGUCNestLevel();
+ SetConfigOption("search_path", GUC_SAFE_SEARCH_PATH, PGC_USERSET,
+ PGC_S_SESSION);
/*
* If PROCESS_MAIN is set (the default), it's time to vacuum the main
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 4ac021534b..0601fde205 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -109,7 +109,6 @@ $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);
CREATE SCHEMA "Bar";
@@ -117,9 +116,6 @@ $node->safe_psql(
|);
$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/guc.h b/src/include/utils/guc.h
index 471d53da8f..391d8d0212 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -203,6 +203,12 @@ typedef enum
#define GUC_QUALIFIER_SEPARATOR '.'
+/*
+ * Safe search path when executing code as the table owner, such as during
+ * maintenance operations.
+ */
+#define GUC_SAFE_SEARCH_PATH "pg_catalog, pg_temp"
+
/*
* Bit values in "flags" of a GUC variable. Note that these don't appear
* on disk, so we can reassign their values freely.
diff --git a/src/test/modules/test_oat_hooks/expected/alter_table.out b/src/test/modules/test_oat_hooks/expected/alter_table.out
index 19adb28ffb..8cbacca2c9 100644
--- a/src/test/modules/test_oat_hooks/expected/alter_table.out
+++ b/src/test/modules/test_oat_hooks/expected/alter_table.out
@@ -62,6 +62,8 @@ BEGIN
END IF;
END; $$;
NOTICE: in process utility: superuser attempting CREATE FUNCTION
+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 FUNCTION
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 67a50bde3d..038ab73517 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -574,10 +574,11 @@ DROP OWNED BY regress_user_mvtest CASCADE;
DROP ROLE regress_user_mvtest;
-- Concurrent refresh requires a unique index on the materialized
-- view. Test what happens if it's dropped during the refresh.
+SET search_path = mvtest_mvschema, public;
CREATE OR REPLACE FUNCTION mvtest_drop_the_index()
RETURNS bool AS $$
BEGIN
- EXECUTE 'DROP INDEX IF EXISTS mvtest_drop_idx';
+ EXECUTE 'DROP INDEX IF EXISTS mvtest_mvschema.mvtest_drop_idx';
RETURN true;
END;
$$ LANGUAGE plpgsql;
@@ -588,6 +589,7 @@ CREATE UNIQUE INDEX mvtest_drop_idx ON drop_idx_matview (i);
REFRESH MATERIALIZED VIEW CONCURRENTLY drop_idx_matview;
ERROR: could not find suitable unique index on materialized view
DROP MATERIALIZED VIEW drop_idx_matview; -- clean up
+RESET search_path;
-- make sure that create WITH NO DATA works via SPI
BEGIN;
CREATE FUNCTION mvtest_func()
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index fbb0489a4f..5ae5757bde 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 235123de1e..b74ee305e0 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -233,10 +233,11 @@ DROP ROLE regress_user_mvtest;
-- Concurrent refresh requires a unique index on the materialized
-- view. Test what happens if it's dropped during the refresh.
+SET search_path = mvtest_mvschema, public;
CREATE OR REPLACE FUNCTION mvtest_drop_the_index()
RETURNS bool AS $$
BEGIN
- EXECUTE 'DROP INDEX IF EXISTS mvtest_drop_idx';
+ EXECUTE 'DROP INDEX IF EXISTS mvtest_mvschema.mvtest_drop_idx';
RETURN true;
END;
$$ LANGUAGE plpgsql;
@@ -247,6 +248,7 @@ CREATE MATERIALIZED VIEW drop_idx_matview AS
CREATE UNIQUE INDEX mvtest_drop_idx ON drop_idx_matview (i);
REFRESH MATERIALIZED VIEW CONCURRENTLY drop_idx_matview;
DROP MATERIALIZED VIEW drop_idx_matview; -- clean up
+RESET search_path;
-- make sure that create WITH NO DATA works via SPI
BEGIN;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 3f68cafcd1..2ef15a9d8c 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
