On Tue, 06 Aug 2024 11:24:03 -0700
Jeff Davis <pg...@j-davis.com> wrote:
> On Wed, 2024-08-07 at 03:06 +0900, Yugo Nagata wrote:
> > I'm sorry. After sending the mail, I found the patch didn't work.
> > If we call RestrictSearchPath() before creating a relation, it tries
> > to create the relation in pg_catalog and it causes an error.
>
> Yeah, that's exactly the problem I ran into in ExecCreateTableAs(),
> which was the reason I refactored it to use RefreshMatViewByOid().
I came up with an idea that we can rewrite into->rel to add the
current schemaname before calling RestrictSearchPath() as in the
attached patch.
It seems a simpler fix at least, but I am not sure whether this design
is better than using RefreshMatViewByOid from EXPLAIN considering
to support EXPLAIN REFRESH ...
Regards,
Yugo Nagata
--
Yugo NAGATA <nag...@sraoss.co.jp>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 5771aabf40..1d06a7e96e 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -15,6 +15,7 @@
#include "access/xact.h"
#include "catalog/pg_type.h"
+#include "catalog/namespace.h"
#include "commands/createas.h"
#include "commands/defrem.h"
#include "commands/prepare.h"
@@ -22,6 +23,7 @@
#include "jit/jit.h"
#include "libpq/pqformat.h"
#include "libpq/protocol.h"
+#include "miscadmin.h"
#include "nodes/extensible.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -467,6 +469,10 @@ standard_ExplainOneQuery(Query *query, int cursorOptions,
MemoryContext planner_ctx = NULL;
MemoryContext saved_ctx = NULL;
+ Oid save_userid = InvalidOid;
+ int save_sec_context = 0;
+ int save_nestlevel = 0;
+
if (es->memory)
{
/*
@@ -487,6 +493,24 @@ standard_ExplainOneQuery(Query *query, int cursorOptions,
bufusage_start = pgBufferUsage;
INSTR_TIME_SET_CURRENT(planstart);
+ /*
+ * For CREATE MATERIALIZED VIEW command, switch to the owner's userid, so
+ * that any functions are run as that user. Also lock down security-restricted
+ * operations and arrange to make GUC variable changes local to this command.
+ */
+ if (into && into->viewQuery)
+ {
+ Oid nspid = RangeVarGetCreationNamespace(into->rel);
+
+ into->rel = makeRangeVar(get_namespace_name(nspid), into->rel->relname, -1);
+
+ GetUserIdAndSecContext(&save_userid, &save_sec_context);
+ SetUserIdAndSecContext(save_userid,
+ save_sec_context | SECURITY_RESTRICTED_OPERATION);
+ save_nestlevel = NewGUCNestLevel();
+ RestrictSearchPath();
+ }
+
/* plan the query */
plan = pg_plan_query(query, queryString, cursorOptions, params);
@@ -510,6 +534,16 @@ standard_ExplainOneQuery(Query *query, int cursorOptions,
ExplainOnePlan(plan, into, es, queryString, params, queryEnv,
&planduration, (es->buffers ? &bufusage : NULL),
es->memory ? &mem_counters : NULL);
+
+ /* CREATE MATERIALIZED VIEW command */
+ if (into && into->viewQuery)
+ {
+ /* Roll back any GUC changes */
+ AtEOXact_GUC(false, save_nestlevel);
+
+ /* Restore userid and security context */
+ SetUserIdAndSecContext(save_userid, save_sec_context);
+ }
}
/*
