Relax INSERT privilege requirement for CTAS and matviews WITH NO DATA

When specified, WITH NO DATA does not insert any data into the relation
created, so skip checking for the insert permissions.  With WITH DATA or
WITH NO DATA, it is always required for the user to have CREATE
privileges on the schema targeted for the relation.

Note that plain CREATE TABLE AS or CREATE MATERIALIZED VIEW queries have
begun to work accidentally without INSERT privilege checks as of
874fe3ae, while using EXECUTE or EXPLAIN ANALYZE would fail with the ACL
check, so this makes the behavior for all the command flavors consistent
with each other.  This is arguably a bug fix, but there have been no
complaints about the current behavior either so stable branches are not
changed.

While on it, document properly the privileges requirements for each
commands with more tests for all the scenarios possible, and avoid a
useless bulk-insert allocation when using WITH NO DATA.

Author: Bharath Rupireddy
Reviewed-by: Anastasia Lubennikova, Michael Paquier
Discussion: https://postgr.es/m/CALj2ACWc3N8j0_9nMPz9wcAUnVcdKHzFdDZJ3hVFNEbqtcyG9w@mail.gmail.com

2 files changed, 88 insertions, 9 deletions

diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index d0121a7b0b8..328c3118b68 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -589,3 +589,38 @@ SELECT * FROM mvtest2;
 ERROR:  materialized view "mvtest2" has not been populated
 HINT:  Use the REFRESH MATERIALIZED VIEW command.
 ROLLBACK;
+-- INSERT privileges if relation owner is not allowed to insert.
+CREATE SCHEMA matview_schema;
+CREATE USER regress_matview_user;
+ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
+  REVOKE INSERT ON TABLES FROM regress_matview_user;
+GRANT ALL ON SCHEMA matview_schema TO public;
+SET SESSION AUTHORIZATION regress_matview_user;
+-- WITH DATA fails.
+CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS
+  SELECT generate_series(1, 10) WITH DATA; -- error
+ERROR:  permission denied for materialized view mv_withdata1
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS
+  SELECT generate_series(1, 10) WITH DATA; -- error
+ERROR:  permission denied for materialized view mv_withdata1
+-- WITH NO DATA passes.
+CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS
+  SELECT generate_series(1, 10) WITH NO DATA;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS
+  SELECT generate_series(1, 10) WITH NO DATA;
+          QUERY PLAN           
+-------------------------------
+ ProjectSet (never executed)
+   ->  Result (never executed)
+(2 rows)
+
+RESET SESSION AUTHORIZATION;
+ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
+  GRANT INSERT ON TABLES TO regress_matview_user;
+DROP SCHEMA matview_schema CASCADE;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to materialized view matview_schema.mv_nodata1
+drop cascades to materialized view matview_schema.mv_nodata2
+DROP USER regress_matview_user;
diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out
index f373fae6796..45068afca70 100644
--- a/src/test/regress/expected/select_into.out
+++ b/src/test/regress/expected/select_into.out
@@ -21,16 +21,56 @@ ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
 GRANT ALL ON SCHEMA selinto_schema TO public;
 SET SESSION AUTHORIZATION regress_selinto_user;
 SELECT * INTO TABLE selinto_schema.tmp1
-	  FROM pg_class WHERE relname like '%a%';	-- Error
+  FROM pg_class WHERE relname like '%a%';
 ERROR:  permission denied for table tmp1
 SELECT oid AS clsoid, relname, relnatts + 10 AS x
-	  INTO selinto_schema.tmp2
-	  FROM pg_class WHERE relname like '%b%';	-- Error
+  INTO selinto_schema.tmp2
+  FROM pg_class WHERE relname like '%b%';
 ERROR:  permission denied for table tmp2
-CREATE TABLE selinto_schema.tmp3 (a,b,c)
-	   AS SELECT oid,relname,relacl FROM pg_class
-	   WHERE relname like '%c%';	-- Error
-ERROR:  permission denied for table tmp3
+-- WITH DATA, fails
+CREATE TABLE selinto_schema.tbl_withdata (a,b,c)
+  AS SELECT oid,relname,relacl FROM pg_class
+  WHERE relname like '%c%' WITH DATA;
+ERROR:  permission denied for table tbl_withdata
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE selinto_schema.tbl_withdata (a,b,c)
+  AS SELECT oid,relname,relacl FROM pg_class
+  WHERE relname like '%c%' WITH DATA;
+ERROR:  permission denied for table tbl_withdata
+-- WITH NO DATA, passes.
+CREATE TABLE selinto_schema.tbl_nodata1 (a) AS
+  SELECT oid FROM pg_class WHERE relname like '%c%' WITH NO DATA;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE selinto_schema.tbl_nodata2 (a) AS
+  SELECT oid FROM pg_class WHERE relname like '%c%' WITH NO DATA;
+              QUERY PLAN               
+---------------------------------------
+ Seq Scan on pg_class (never executed)
+   Filter: (relname ~~ '%c%'::text)
+(2 rows)
+
+-- EXECUTE and WITH DATA, fails.
+PREPARE data_sel AS
+  SELECT oid FROM pg_class WHERE relname like '%c%';
+CREATE TABLE selinto_schema.tbl_withdata (a) AS
+  EXECUTE data_sel WITH DATA;
+ERROR:  permission denied for table tbl_withdata
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE selinto_schema.tbl_withdata (a) AS
+  EXECUTE data_sel WITH DATA;
+ERROR:  permission denied for table tbl_withdata
+-- EXECUTE and WITH NO DATA, passes.
+CREATE TABLE selinto_schema.tbl_nodata3 (a) AS
+  EXECUTE data_sel WITH NO DATA;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE selinto_schema.tbl_nodata4 (a) AS
+  EXECUTE data_sel WITH NO DATA;
+              QUERY PLAN               
+---------------------------------------
+ Seq Scan on pg_class (never executed)
+   Filter: (relname ~~ '%c%'::text)
+(2 rows)
+
 RESET SESSION AUTHORIZATION;
 ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
 	  GRANT INSERT ON TABLES TO regress_selinto_user;
@@ -45,8 +85,12 @@ CREATE TABLE selinto_schema.tmp3 (a,b,c)
 	   WHERE relname like '%c%';	-- OK
 RESET SESSION AUTHORIZATION;
 DROP SCHEMA selinto_schema CASCADE;
-NOTICE:  drop cascades to 3 other objects
-DETAIL:  drop cascades to table selinto_schema.tmp1
+NOTICE:  drop cascades to 7 other objects
+DETAIL:  drop cascades to table selinto_schema.tbl_nodata1
+drop cascades to table selinto_schema.tbl_nodata2
+drop cascades to table selinto_schema.tbl_nodata3
+drop cascades to table selinto_schema.tbl_nodata4
+drop cascades to table selinto_schema.tmp1
 drop cascades to table selinto_schema.tmp2
 drop cascades to table selinto_schema.tmp3
 DROP USER regress_selinto_user;