v2-0002-For-materialized-views-use-REFRESH-to-load-data-d.patch

From 00323c08d07f1fb53e29e89e44b9393d442c15b1 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Fri, 12 Jul 2024 14:23:17 -0700
Subject: [PATCH v2 2/2] For materialized views, use REFRESH to load data
 during creation.

Previously, CREATE MATERIALIZED VIEW ... WITH DATA populated the MV
during creation. Instead, use REFRESH, which locks down
security-restricted operations and restricts the
search_path. Otherwise, it's possible to create MVs that cannot be
refreshed.

Reported-by: Noah Misch
Backpatch-through: 17
Discussion: https://postgr.es/m/20240630222344.db.nmisch@google.com
---
 src/backend/commands/createas.c         | 35 +++++++++++++------------
 src/test/regress/expected/namespace.out |  4 +--
 2 files changed, 20 insertions(+), 19 deletions(-)

diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 62050f4dc59..166ade5faa7 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -225,10 +225,8 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
 	Query	   *query = castNode(Query, stmt->query);
 	IntoClause *into = stmt->into;
 	bool		is_matview = (into->viewQuery != NULL);
+	bool		do_refresh = false;
 	DestReceiver *dest;
-	Oid			save_userid = InvalidOid;
-	int			save_sec_context = 0;
-	int			save_nestlevel = 0;
 	ObjectAddress address;
 	List	   *rewritten;
 	PlannedStmt *plan;
@@ -263,18 +261,13 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
 	Assert(query->commandType == CMD_SELECT);
 
 	/*
-	 * For materialized views, lock down security-restricted operations and
-	 * arrange to make GUC variable changes local to this command.  This is
-	 * not necessary for security, but this keeps the behavior similar to
-	 * REFRESH MATERIALIZED VIEW.  Otherwise, one could create a materialized
-	 * view not possible to refresh.
+	 * For materialized views, always skip data during table creation, and use
+	 * REFRESH instead.
 	 */
 	if (is_matview)
 	{
-		GetUserIdAndSecContext(&save_userid, &save_sec_context);
-		SetUserIdAndSecContext(save_userid,
-							   save_sec_context | SECURITY_RESTRICTED_OPERATION);
-		save_nestlevel = NewGUCNestLevel();
+		do_refresh = !into->skipData;
+		into->skipData = true;
 	}
 
 	if (into->skipData)
@@ -346,13 +339,21 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
 		PopActiveSnapshot();
 	}
 
-	if (is_matview)
+	/*
+	 * For materialized views, use REFRESH, which locks down
+	 * security-restricted operations and restricts the search_path.
+	 * Otherwise, one could create a materialized view not possible to
+	 * refresh.
+	 */
+	if (do_refresh)
 	{
-		/* Roll back any GUC changes */
-		AtEOXact_GUC(false, save_nestlevel);
+		RefreshMatViewStmt *refresh = makeNode(RefreshMatViewStmt);
 
-		/* Restore userid and security context */
-		SetUserIdAndSecContext(save_userid, save_sec_context);
+		refresh->relation = into->rel;
+		ExecRefreshMatView(refresh, pstate->p_sourcetext, NULL, qc);
+
+		if (qc)
+			qc->commandTag = CMDTAG_SELECT;
 	}
 
 	return address;
diff --git a/src/test/regress/expected/namespace.out b/src/test/regress/expected/namespace.out
index 7d36e9cc0c4..dbbda72d395 100644
--- a/src/test/regress/expected/namespace.out
+++ b/src/test/regress/expected/namespace.out
@@ -129,8 +129,8 @@ $$;
 CREATE TABLE test_maint(i INT);
 INSERT INTO test_maint VALUES (1), (2);
 CREATE MATERIALIZED VIEW test_maint_mv AS SELECT fn(i) FROM test_maint;
-NOTICE:  current search_path: test_maint_search_path
-NOTICE:  current search_path: test_maint_search_path
+NOTICE:  current search_path: pg_catalog, pg_temp
+NOTICE:  current search_path: pg_catalog, pg_temp
 -- the following commands should see search_path as pg_catalog, pg_temp
 CREATE INDEX test_maint_idx ON test_maint_search_path.test_maint (fn(i));
 NOTICE:  current search_path: pg_catalog, pg_temp
-- 
2.34.1