On Fri, Nov 20, 2020 at 11:07 AM Michael Paquier <mich...@paquier.xyz> wrote:
>
> Thanks. Based on what Peter has said, the ACL_INSERT check in
> intorel_startup() could just be removed, and the tests of matview.sql
> and select_into.sql would need some cleanup. We could keep around
> some scenarios with some follow-up INSERT queries after the initial
> creation.
>
Thanks! Attaching the patch. Please review it.
With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com
From 1275f235611a4f8badb1edd6f999b557bb1ed291 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddy@enterprisedb.com>
Date: Fri, 20 Nov 2020 14:36:33 +0530
Subject: [PATCH v1] Do not check INSERT privilege in CTAS and MatView
To keep in sync CTAS with SQL standard, do not check for INSERT
privilege.
---
.../sgml/ref/create_materialized_view.sgml | 3 +-
doc/src/sgml/ref/create_table_as.sgml | 5 +-
src/backend/commands/createas.c | 26 +----
src/test/regress/expected/matview.out | 30 +++--
src/test/regress/expected/select_into.out | 109 +++++++++---------
src/test/regress/sql/matview.sql | 16 ++-
src/test/regress/sql/select_into.sql | 59 ++++------
7 files changed, 111 insertions(+), 137 deletions(-)
diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml
index e4ea049eff..c8dee0f99e 100644
--- a/doc/src/sgml/ref/create_materialized_view.sgml
+++ b/doc/src/sgml/ref/create_materialized_view.sgml
@@ -52,8 +52,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
<para>
<command>CREATE MATERIALIZED VIEW</command> requires
<literal>CREATE</literal> privilege on the schema used for the materialized
- view. If using <command>WITH DATA</command>, the default,
- <literal>INSERT</literal> privilege is also required.
+ view. <literal>INSERT</literal> privilege is not required.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/create_table_as.sgml b/doc/src/sgml/ref/create_table_as.sgml
index 2cac4e3ec0..6fb4fa6f44 100644
--- a/doc/src/sgml/ref/create_table_as.sgml
+++ b/doc/src/sgml/ref/create_table_as.sgml
@@ -56,9 +56,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<para>
<command>CREATE TABLE AS</command> requires <literal>CREATE</literal>
- privilege on the schema used for the table. If using
- <command>WITH DATA</command>, the default, <literal>INSERT</literal>
- privilege is also required.
+ privilege on the schema used for the table. However,
+ <literal>INSERT</literal> privilege is not required as per the SQL standard.
</para>
</refsect1>
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 37649eafa8..1f47ea99b5 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -432,7 +432,6 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
DR_intorel *myState = (DR_intorel *) self;
IntoClause *into = myState->into;
bool is_matview;
- char relkind;
List *attrList;
ObjectAddress intoRelationAddr;
Relation intoRelationDesc;
@@ -443,7 +442,6 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
/* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */
is_matview = (into->viewQuery != NULL);
- relkind = is_matview ? RELKIND_MATVIEW : RELKIND_RELATION;
/*
* Build column definitions using "pre-cooked" type and collation info. If
@@ -505,29 +503,7 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
*/
intoRelationDesc = table_open(intoRelationAddr.objectId, AccessExclusiveLock);
- /*
- * Check INSERT permission on the constructed table. Skip this check if
- * WITH NO DATA is specified as only a table gets created with no tuples
- * inserted, that is a case possible when using EXPLAIN ANALYZE or
- * EXECUTE.
- */
- if (!into->skipData)
- {
- RangeTblEntry *rte;
-
- rte = makeNode(RangeTblEntry);
- rte->rtekind = RTE_RELATION;
- rte->relid = intoRelationAddr.objectId;
- rte->relkind = relkind;
- rte->rellockmode = RowExclusiveLock;
- rte->requiredPerms = ACL_INSERT;
-
- for (attnum = 1; attnum <= intoRelationDesc->rd_att->natts; attnum++)
- rte->insertedCols = bms_add_member(rte->insertedCols,
- attnum - FirstLowInvalidHeapAttributeNumber);
-
- ExecCheckRTPerms(list_make1(rte), true);
- }
+ /* We do not check INSERT privilege here for SQL standard compatibility. */
/*
* Make sure the constructed table does not have RLS enabled.
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 328c3118b6..da4c73aa4f 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -589,22 +589,29 @@ 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.
+--
+-- INSERT privilege for CREATE and REFRESH MATERIALIZED VIEW.
+-- Queries succeed even though the owner has no INSERT privilege as we do not
+-- check for it.
+--
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
+ SELECT generate_series(1, 10) WITH DATA;
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_withdata2 (a) AS
+ SELECT generate_series(1, 10) WITH DATA;
+ QUERY PLAN
+--------------------------------------
+ ProjectSet (actual rows=10 loops=1)
+ -> Result (actual rows=1 loops=1)
+(2 rows)
+
+REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2;
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)
@@ -616,11 +623,14 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
-> Result (never executed)
(2 rows)
+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;
DROP SCHEMA matview_schema CASCADE;
-NOTICE: drop cascades to 2 other objects
-DETAIL: drop cascades to materialized view matview_schema.mv_nodata1
+NOTICE: drop cascades to 4 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 USER regress_matview_user;
diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out
index 45068afca7..1ee2313fb0 100644
--- a/src/test/regress/expected/select_into.out
+++ b/src/test/regress/expected/select_into.out
@@ -12,7 +12,9 @@ SELECT *
WHERE onek2.unique1 < 2;
DROP TABLE sitmp1;
--
--- SELECT INTO and INSERT permission, if owner is not allowed to insert.
+-- INSERT privilege for SELECT INTO and CREATE TABLE AS.
+-- Queries succeed even though the owner has no INSERT privilege as we do not
+-- check for it.
--
CREATE SCHEMA selinto_schema;
CREATE USER regress_selinto_user;
@@ -20,79 +22,76 @@ ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
REVOKE INSERT ON TABLES FROM 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%';
+CREATE TABLE selinto_schema.tmp1 (col1 int);
+-- Direct insert fails, as owner has no INSERT privilege.
+INSERT INTO selinto_schema.tmp1 VALUES (1);
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: permission denied for table tmp2
--- 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
+SELECT * INTO TABLE selinto_schema.tmp2 FROM generate_series(1,3);
+-- WITH DATA, passes.
+CREATE TABLE selinto_schema.tmp3 (a) AS SELECT generate_series(1,3) WITH DATA;
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
+ CREATE TABLE selinto_schema.tmp4 (a) AS SELECT generate_series(1,3) WITH DATA;
+ QUERY PLAN
+--------------------------------------
+ ProjectSet (actual rows=3 loops=1)
+ -> Result (actual rows=1 loops=1)
+(2 rows)
+
-- WITH NO DATA, passes.
-CREATE TABLE selinto_schema.tbl_nodata1 (a) AS
- SELECT oid FROM pg_class WHERE relname like '%c%' WITH NO DATA;
+CREATE TABLE selinto_schema.tmp5 (a) AS
+ SELECT generate_series(1,3) 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)
+ CREATE TABLE selinto_schema.tmp6 (a) AS
+ SELECT generate_series(1,3) WITH NO DATA;
+ QUERY PLAN
+-------------------------------
+ ProjectSet (never executed)
+ -> Result (never executed)
(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
+-- EXECUTE and WITH DATA, passes.
+PREPARE data_sel AS SELECT generate_series(1,3);
+CREATE TABLE selinto_schema.tmp7 (a) AS EXECUTE data_sel WITH DATA;
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
+ CREATE TABLE selinto_schema.tmp8 (a) AS EXECUTE data_sel WITH DATA;
+ QUERY PLAN
+--------------------------------------
+ ProjectSet (actual rows=3 loops=1)
+ -> Result (actual rows=1 loops=1)
+(2 rows)
+
-- EXECUTE and WITH NO DATA, passes.
-CREATE TABLE selinto_schema.tbl_nodata3 (a) AS
- EXECUTE data_sel WITH NO DATA;
+CREATE TABLE selinto_schema.tmp9 (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)
+ CREATE TABLE selinto_schema.tmp10 (a) AS EXECUTE data_sel WITH NO DATA;
+ QUERY PLAN
+-------------------------------
+ ProjectSet (never executed)
+ -> Result (never executed)
(2 rows)
RESET SESSION AUTHORIZATION;
ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
GRANT INSERT ON TABLES TO regress_selinto_user;
SET SESSION AUTHORIZATION regress_selinto_user;
-SELECT * INTO TABLE selinto_schema.tmp1
- FROM pg_class WHERE relname like '%a%'; -- OK
-SELECT oid AS clsoid, relname, relnatts + 10 AS x
- INTO selinto_schema.tmp2
- FROM pg_class WHERE relname like '%b%'; -- OK
-CREATE TABLE selinto_schema.tmp3 (a,b,c)
- AS SELECT oid,relname,relacl FROM pg_class
- WHERE relname like '%c%'; -- OK
+SELECT * INTO TABLE selinto_schema.tmp11 FROM generate_series(1,3); -- OK
+CREATE TABLE selinto_schema.tmp12 (a) AS SELECT generate_series(1,3); -- OK
RESET SESSION AUTHORIZATION;
+DEALLOCATE data_sel;
DROP SCHEMA selinto_schema CASCADE;
-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
+NOTICE: drop cascades to 12 other objects
+DETAIL: drop cascades to table selinto_schema.tmp1
drop cascades to table selinto_schema.tmp2
drop cascades to table selinto_schema.tmp3
+drop cascades to table selinto_schema.tmp4
+drop cascades to table selinto_schema.tmp5
+drop cascades to table selinto_schema.tmp6
+drop cascades to table selinto_schema.tmp7
+drop cascades to table selinto_schema.tmp8
+drop cascades to table selinto_schema.tmp9
+drop cascades to table selinto_schema.tmp10
+drop cascades to table selinto_schema.tmp11
+drop cascades to table selinto_schema.tmp12
DROP USER regress_selinto_user;
-- Tests for WITH NO DATA and column name consistency
CREATE TABLE ctas_base (i int, j int);
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 419eba2075..51a94eda2c 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -237,7 +237,11 @@ SELECT * FROM mvtest1;
SELECT * FROM mvtest2;
ROLLBACK;
--- INSERT privileges if relation owner is not allowed to insert.
+--
+-- INSERT privilege for CREATE and REFRESH MATERIALIZED VIEW.
+-- Queries succeed even though the owner has no INSERT privilege as we do not
+-- check for it.
+--
CREATE SCHEMA matview_schema;
CREATE USER regress_matview_user;
ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
@@ -245,18 +249,18 @@ ALTER DEFAULT PRIVILEGES FOR ROLE 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
+ SELECT generate_series(1, 10) WITH DATA;
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
--- WITH NO DATA passes.
+ CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS
+ SELECT generate_series(1, 10) WITH DATA;
+REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2;
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;
+REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2;
RESET SESSION AUTHORIZATION;
ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql
index 0faba72bec..2daada106c 100644
--- a/src/test/regress/sql/select_into.sql
+++ b/src/test/regress/sql/select_into.sql
@@ -17,7 +17,9 @@ SELECT *
DROP TABLE sitmp1;
--
--- SELECT INTO and INSERT permission, if owner is not allowed to insert.
+-- INSERT privilege for SELECT INTO and CREATE TABLE AS.
+-- Queries succeed even though the owner has no INSERT privilege as we do not
+-- check for it.
--
CREATE SCHEMA selinto_schema;
CREATE USER regress_selinto_user;
@@ -26,55 +28,40 @@ 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%';
-SELECT oid AS clsoid, relname, relnatts + 10 AS x
- INTO selinto_schema.tmp2
- FROM pg_class WHERE relname like '%b%';
--- 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;
+CREATE TABLE selinto_schema.tmp1 (col1 int);
+-- Direct insert fails, as owner has no INSERT privilege.
+INSERT INTO selinto_schema.tmp1 VALUES (1);
+SELECT * INTO TABLE selinto_schema.tmp2 FROM generate_series(1,3);
+-- WITH DATA, passes.
+CREATE TABLE selinto_schema.tmp3 (a) AS SELECT generate_series(1,3) WITH DATA;
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;
+ CREATE TABLE selinto_schema.tmp4 (a) AS SELECT generate_series(1,3) WITH DATA;
-- WITH NO DATA, passes.
-CREATE TABLE selinto_schema.tbl_nodata1 (a) AS
- SELECT oid FROM pg_class WHERE relname like '%c%' WITH NO DATA;
+CREATE TABLE selinto_schema.tmp5 (a) AS
+ SELECT generate_series(1,3) 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;
--- 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;
+ CREATE TABLE selinto_schema.tmp6 (a) AS
+ SELECT generate_series(1,3) WITH NO DATA;
+-- EXECUTE and WITH DATA, passes.
+PREPARE data_sel AS SELECT generate_series(1,3);
+CREATE TABLE selinto_schema.tmp7 (a) AS EXECUTE data_sel WITH DATA;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
- CREATE TABLE selinto_schema.tbl_withdata (a) AS
- EXECUTE data_sel WITH DATA;
+ CREATE TABLE selinto_schema.tmp8 (a) AS EXECUTE data_sel WITH DATA;
-- EXECUTE and WITH NO DATA, passes.
-CREATE TABLE selinto_schema.tbl_nodata3 (a) AS
- EXECUTE data_sel WITH NO DATA;
+CREATE TABLE selinto_schema.tmp9 (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;
+ CREATE TABLE selinto_schema.tmp10 (a) AS EXECUTE data_sel WITH NO DATA;
RESET SESSION AUTHORIZATION;
ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
GRANT INSERT ON TABLES TO regress_selinto_user;
SET SESSION AUTHORIZATION regress_selinto_user;
-SELECT * INTO TABLE selinto_schema.tmp1
- FROM pg_class WHERE relname like '%a%'; -- OK
-SELECT oid AS clsoid, relname, relnatts + 10 AS x
- INTO selinto_schema.tmp2
- FROM pg_class WHERE relname like '%b%'; -- OK
-CREATE TABLE selinto_schema.tmp3 (a,b,c)
- AS SELECT oid,relname,relacl FROM pg_class
- WHERE relname like '%c%'; -- OK
+SELECT * INTO TABLE selinto_schema.tmp11 FROM generate_series(1,3); -- OK
+CREATE TABLE selinto_schema.tmp12 (a) AS SELECT generate_series(1,3); -- OK
RESET SESSION AUTHORIZATION;
+DEALLOCATE data_sel;
DROP SCHEMA selinto_schema CASCADE;
DROP USER regress_selinto_user;
--
2.25.1
