BUG #11208: Refresh Materialized View Concurrently bug using user Postgres

*** a/src/backend/commands/matview.c
--- b/src/backend/commands/matview.c
***************
*** 59,70 **** static void transientrel_receive(TupleTableSlot *slot, DestReceiver *self);
  static void transientrel_shutdown(DestReceiver *self);
  static void transientrel_destroy(DestReceiver *self);
  static void refresh_matview_datafill(DestReceiver *dest, Query *query,
! 						 const char *queryString, Oid relowner);
  
  static char *make_temptable_name_n(char *tempname, int n);
  static void mv_GenerateOper(StringInfo buf, Oid opoid);
  
! static void refresh_by_match_merge(Oid matviewOid, Oid tempOid);
  static void refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap);
  
  static void OpenMatViewIncrementalMaintenance(void);
--- 59,71 ----
  static void transientrel_shutdown(DestReceiver *self);
  static void transientrel_destroy(DestReceiver *self);
  static void refresh_matview_datafill(DestReceiver *dest, Query *query,
! 						 const char *queryString);
  
  static char *make_temptable_name_n(char *tempname, int n);
  static void mv_GenerateOper(StringInfo buf, Oid opoid);
  
! static void refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
! 						 int save_sec_context);
  static void refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap);
  
  static void OpenMatViewIncrementalMaintenance(void);
***************
*** 142,152 **** ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
  	List	   *actions;
  	Query	   *dataQuery;
  	Oid			tableSpace;
! 	Oid			owner;
  	Oid			OIDNewHeap;
  	DestReceiver *dest;
  	bool		concurrent;
  	LOCKMODE	lockmode;
  
  	/* Determine strength of lock needed. */
  	concurrent = stmt->concurrent;
--- 143,156 ----
  	List	   *actions;
  	Query	   *dataQuery;
  	Oid			tableSpace;
! 	Oid			relowner;
  	Oid			OIDNewHeap;
  	DestReceiver *dest;
  	bool		concurrent;
  	LOCKMODE	lockmode;
+ 	Oid			save_userid;
+ 	int			save_sec_context;
+ 	int			save_nestlevel;
  
  	/* Determine strength of lock needed. */
  	concurrent = stmt->concurrent;
***************
*** 231,244 **** ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
  	 */
  	SetMatViewPopulatedState(matviewRel, !stmt->skipData);
  
  	/* Concurrent refresh builds new data in temp tablespace, and does diff. */
  	if (concurrent)
  		tableSpace = GetDefaultTablespace(RELPERSISTENCE_TEMP);
  	else
  		tableSpace = matviewRel->rd_rel->reltablespace;
  
- 	owner = matviewRel->rd_rel->relowner;
- 
  	/*
  	 * Create the transient table that will receive the regenerated data. Lock
  	 * it against access by any other process until commit (by which time it
--- 235,259 ----
  	 */
  	SetMatViewPopulatedState(matviewRel, !stmt->skipData);
  
+ 	relowner = matviewRel->rd_rel->relowner;
+ 
+ 	/*
+ 	 * Switch to the owner's userid, so that any functions are run as that
+ 	 * user.  Also arrange to make GUC variable changes local to this command.
+ 	 * Don't lock it down too tight to create a temporary table just yet.  We
+ 	 * will switch modes when we are about to execute user code.
+ 	 */
+ 	GetUserIdAndSecContext(&save_userid, &save_sec_context);
+ 	SetUserIdAndSecContext(relowner,
+ 						   save_sec_context | SECURITY_LOCAL_USERID_CHANGE);
+ 	save_nestlevel = NewGUCNestLevel();
+ 
  	/* Concurrent refresh builds new data in temp tablespace, and does diff. */
  	if (concurrent)
  		tableSpace = GetDefaultTablespace(RELPERSISTENCE_TEMP);
  	else
  		tableSpace = matviewRel->rd_rel->reltablespace;
  
  	/*
  	 * Create the transient table that will receive the regenerated data. Lock
  	 * it against access by any other process until commit (by which time it
***************
*** 249,257 **** ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
  	LockRelationOid(OIDNewHeap, AccessExclusiveLock);
  	dest = CreateTransientRelDestReceiver(OIDNewHeap);
  
  	/* Generate the data, if wanted. */
  	if (!stmt->skipData)
! 		refresh_matview_datafill(dest, dataQuery, queryString, owner);
  
  	heap_close(matviewRel, NoLock);
  
--- 264,278 ----
  	LockRelationOid(OIDNewHeap, AccessExclusiveLock);
  	dest = CreateTransientRelDestReceiver(OIDNewHeap);
  
+ 	/*
+ 	 * Now lock down security-restricted operations.
+ 	 */
+ 	SetUserIdAndSecContext(relowner,
+ 						   save_sec_context | SECURITY_RESTRICTED_OPERATION);
+ 
  	/* Generate the data, if wanted. */
  	if (!stmt->skipData)
! 		refresh_matview_datafill(dest, dataQuery, queryString);
  
  	heap_close(matviewRel, NoLock);
  
***************
*** 262,268 **** ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
  
  		PG_TRY();
  		{
! 			refresh_by_match_merge(matviewOid, OIDNewHeap);
  		}
  		PG_CATCH();
  		{
--- 283,290 ----
  
  		PG_TRY();
  		{
! 			refresh_by_match_merge(matviewOid, OIDNewHeap, relowner,
! 								   save_sec_context);
  		}
  		PG_CATCH();
  		{
***************
*** 274,279 **** ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
--- 296,307 ----
  	}
  	else
  		refresh_by_heap_swap(matviewOid, OIDNewHeap);
+ 
+ 	/* Roll back any GUC changes */
+ 	AtEOXact_GUC(false, save_nestlevel);
+ 
+ 	/* Restore userid and security context */
+ 	SetUserIdAndSecContext(save_userid, save_sec_context);
  }
  
  /*
***************
*** 281,306 **** ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
   */
  static void
  refresh_matview_datafill(DestReceiver *dest, Query *query,
! 						 const char *queryString, Oid relowner)
  {
  	List	   *rewritten;
  	PlannedStmt *plan;
  	QueryDesc  *queryDesc;
- 	Oid			save_userid;
- 	int			save_sec_context;
- 	int			save_nestlevel;
  	Query	   *copied_query;
  
- 	/*
- 	 * 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.
- 	 */
- 	GetUserIdAndSecContext(&save_userid, &save_sec_context);
- 	SetUserIdAndSecContext(relowner,
- 						   save_sec_context | SECURITY_RESTRICTED_OPERATION);
- 	save_nestlevel = NewGUCNestLevel();
- 
  	/* Lock and rewrite, using a copy to preserve the original query. */
  	copied_query = copyObject(query);
  	AcquireRewriteLocks(copied_query, true, false);
--- 309,321 ----
   */
  static void
  refresh_matview_datafill(DestReceiver *dest, Query *query,
! 						 const char *queryString)
  {
  	List	   *rewritten;
  	PlannedStmt *plan;
  	QueryDesc  *queryDesc;
  	Query	   *copied_query;
  
  	/* Lock and rewrite, using a copy to preserve the original query. */
  	copied_query = copyObject(query);
  	AcquireRewriteLocks(copied_query, true, false);
***************
*** 344,355 **** refresh_matview_datafill(DestReceiver *dest, Query *query,
  	FreeQueryDesc(queryDesc);
  
  	PopActiveSnapshot();
- 
- 	/* Roll back any GUC changes */
- 	AtEOXact_GUC(false, save_nestlevel);
- 
- 	/* Restore userid and security context */
- 	SetUserIdAndSecContext(save_userid, save_sec_context);
  }
  
  DestReceiver *
--- 359,364 ----
***************
*** 520,526 **** mv_GenerateOper(StringInfo buf, Oid opoid)
   * this command.
   */
  static void
! refresh_by_match_merge(Oid matviewOid, Oid tempOid)
  {
  	StringInfoData querybuf;
  	Relation	matviewRel;
--- 529,536 ----
   * this command.
   */
  static void
! refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
! 					   int save_sec_context)
  {
  	StringInfoData querybuf;
  	Relation	matviewRel;
***************
*** 534,542 **** refresh_by_match_merge(Oid matviewOid, Oid tempOid)
  	ListCell   *indexoidscan;
  	int16		relnatts;
  	bool	   *usedForQual;
- 	Oid			save_userid;
- 	int			save_sec_context;
- 	int			save_nestlevel;
  
  	initStringInfo(&querybuf);
  	matviewRel = heap_open(matviewOid, NoLock);
--- 544,549 ----
***************
*** 587,592 **** refresh_by_match_merge(Oid matviewOid, Oid tempOid)
--- 594,602 ----
  			SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1))));
  	}
  
+ 	SetUserIdAndSecContext(relowner,
+ 						   save_sec_context | SECURITY_LOCAL_USERID_CHANGE);
+ 
  	/* Start building the query for creating the diff table. */
  	resetStringInfo(&querybuf);
  	appendStringInfo(&querybuf,
***************
*** 681,689 **** refresh_by_match_merge(Oid matviewOid, Oid tempOid)
  	if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
  		elog(ERROR, "SPI_exec failed: %s", querybuf.data);
  
  	/*
  	 * We have no further use for data from the "full-data" temp table, but we
! 	 * must keep it around because its type is reference from the diff table.
  	 */
  
  	/* Analyze the diff table. */
--- 691,702 ----
  	if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
  		elog(ERROR, "SPI_exec failed: %s", querybuf.data);
  
+ 	SetUserIdAndSecContext(relowner,
+ 						   save_sec_context | SECURITY_RESTRICTED_OPERATION);
+ 
  	/*
  	 * We have no further use for data from the "full-data" temp table, but we
! 	 * must keep it around because its type is referenced from the diff table.
  	 */
  
  	/* Analyze the diff table. */
***************
*** 694,709 **** refresh_by_match_merge(Oid matviewOid, Oid tempOid)
  
  	OpenMatViewIncrementalMaintenance();
  
- 	/*
- 	 * 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.
- 	 */
- 	GetUserIdAndSecContext(&save_userid, &save_sec_context);
- 	SetUserIdAndSecContext(matviewRel->rd_rel->relowner,
- 						   save_sec_context | SECURITY_RESTRICTED_OPERATION);
- 	save_nestlevel = NewGUCNestLevel();
- 
  	/* Deletes must come before inserts; do them first. */
  	resetStringInfo(&querybuf);
  	appendStringInfo(&querybuf,
--- 707,712 ----
***************
*** 724,735 **** refresh_by_match_merge(Oid matviewOid, Oid tempOid)
  	if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT)
  		elog(ERROR, "SPI_exec failed: %s", querybuf.data);
  
- 	/* Roll back any GUC changes */
- 	AtEOXact_GUC(false, save_nestlevel);
- 
- 	/* Restore userid and security context */
- 	SetUserIdAndSecContext(save_userid, save_sec_context);
- 
  	/* We're done maintaining the materialized view. */
  	CloseMatViewIncrementalMaintenance();
  	heap_close(tempRel, NoLock);
--- 727,732 ----
*** a/src/test/regress/expected/matview.out
--- b/src/test/regress/expected/matview.out
***************
*** 502,504 **** SELECT * FROM mv_v;
--- 502,515 ----
  
  DROP TABLE v CASCADE;
  NOTICE:  drop cascades to materialized view mv_v
+ -- make sure running as superuser works when MV owned by another role (bug #11208)
+ CREATE ROLE user_dw;
+ SET ROLE user_dw;
+ CREATE TABLE foo_data AS SELECT i, md5(random()::text)
+   FROM generate_series(1, 10) i;
+ CREATE MATERIALIZED VIEW mv_foo AS SELECT * FROM foo_data;
+ CREATE UNIQUE INDEX ON mv_foo (i);
+ RESET ROLE;
+ REFRESH MATERIALIZED VIEW CONCURRENTLY mv_foo;
+ DROP OWNED BY user_dw CASCADE;
+ DROP ROLE user_dw;
*** a/src/test/regress/sql/matview.sql
--- b/src/test/regress/sql/matview.sql
***************
*** 194,196 **** DELETE FROM v WHERE EXISTS ( SELECT * FROM mv_v WHERE mv_v.a = v.a );
--- 194,208 ----
  SELECT * FROM v;
  SELECT * FROM mv_v;
  DROP TABLE v CASCADE;
+ 
+ -- make sure running as superuser works when MV owned by another role (bug #11208)
+ CREATE ROLE user_dw;
+ SET ROLE user_dw;
+ CREATE TABLE foo_data AS SELECT i, md5(random()::text)
+   FROM generate_series(1, 10) i;
+ CREATE MATERIALIZED VIEW mv_foo AS SELECT * FROM foo_data;
+ CREATE UNIQUE INDEX ON mv_foo (i);
+ RESET ROLE;
+ REFRESH MATERIALIZED VIEW CONCURRENTLY mv_foo;
+ DROP OWNED BY user_dw CASCADE;
+ DROP ROLE user_dw;