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
