Skip to content

Commit 878f3a1

Browse files
michaelpqmichaelpq
committed
Remove INSERT privilege check at table creation of CTAS and matview
As per discussion with Peter Eisentraunt, the SQL standard specifies that any tuple insertion done as part of CREATE TABLE AS happens without any extra ACL check, so it makes little sense to keep a check for INSERT privileges when using WITH DATA. Materialized views are not part of the standard, but similarly, this check can be confusing as this refers to an access check on a table created within the same command as the one that would insert data into this table. This commit removes the INSERT privilege check for WITH DATA, the default, that 846005e removed partially, but only for WITH NO DATA. Author: Bharath Rupireddy Discussion: https://postgr.es/m/d049c272-9a47-d783-46b0-46665b011598@enterprisedb.com
1 parent a47834d commit 878f3a1

File tree

7 files changed

+75
-118
lines changed

7 files changed

+75
-118
lines changed

‎doc/src/sgml/ref/create_materialized_view.sgml

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -52,8 +52,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
5252
<para>
5353
<command>CREATE MATERIALIZED VIEW</command> requires
5454
<literal>CREATE</literal> privilege on the schema used for the materialized
55-
view. If using <command>WITH DATA</command>, the default,
56-
<literal>INSERT</literal> privilege is also required.
55+
view.
5756
</para>
5857
</refsect1>
5958

‎doc/src/sgml/ref/create_table_as.sgml

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -56,9 +56,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
5656

5757
<para>
5858
<command>CREATE TABLE AS</command> requires <literal>CREATE</literal>
59-
privilege on the schema used for the table. If using
60-
<command>WITH DATA</command>, the default, <literal>INSERT</literal>
61-
privilege is also required.
59+
privilege on the schema used for the table.
6260
</para>
6361
</refsect1>
6462

‎src/backend/commands/createas.c

Lines changed: 0 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -432,7 +432,6 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
432432
DR_intorel *myState = (DR_intorel *) self;
433433
IntoClause *into = myState->into;
434434
bool is_matview;
435-
char relkind;
436435
List *attrList;
437436
ObjectAddress intoRelationAddr;
438437
Relation intoRelationDesc;
@@ -443,7 +442,6 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
443442

444443
/* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */
445444
is_matview = (into->viewQuery != NULL);
446-
relkind = is_matview ? RELKIND_MATVIEW : RELKIND_RELATION;
447445

448446
/*
449447
* Build column definitions using "pre-cooked" type and collation info. If
@@ -505,30 +503,6 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
505503
*/
506504
intoRelationDesc = table_open(intoRelationAddr.objectId, AccessExclusiveLock);
507505

508-
/*
509-
* Check INSERT permission on the constructed table. Skip this check if
510-
* WITH NO DATA is specified as only a table gets created with no tuples
511-
* inserted, that is a case possible when using EXPLAIN ANALYZE or
512-
* EXECUTE.
513-
*/
514-
if (!into->skipData)
515-
{
516-
RangeTblEntry *rte;
517-
518-
rte = makeNode(RangeTblEntry);
519-
rte->rtekind = RTE_RELATION;
520-
rte->relid = intoRelationAddr.objectId;
521-
rte->relkind = relkind;
522-
rte->rellockmode = RowExclusiveLock;
523-
rte->requiredPerms = ACL_INSERT;
524-
525-
for (attnum = 1; attnum <= intoRelationDesc->rd_att->natts; attnum++)
526-
rte->insertedCols = bms_add_member(rte->insertedCols,
527-
attnum - FirstLowInvalidHeapAttributeNumber);
528-
529-
ExecCheckRTPerms(list_make1(rte), true);
530-
}
531-
532506
/*
533507
* Make sure the constructed table does not have RLS enabled.
534508
*

‎src/test/regress/expected/matview.out

Lines changed: 15 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -596,15 +596,18 @@ ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
596596
REVOKE INSERT ON TABLES FROM regress_matview_user;
597597
GRANT ALL ON SCHEMA matview_schema TO public;
598598
SET SESSION AUTHORIZATION regress_matview_user;
599-
-- WITH DATA fails.
600599
CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS
601-
SELECT generate_series(1, 10) WITH DATA; -- error
602-
ERROR: permission denied for materialized view mv_withdata1
600+
SELECT generate_series(1, 10) WITH DATA;
603601
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
604-
CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS
605-
SELECT generate_series(1, 10) WITH DATA; -- error
606-
ERROR: permission denied for materialized view mv_withdata1
607-
-- WITH NO DATA passes.
602+
CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS
603+
SELECT generate_series(1, 10) WITH DATA;
604+
QUERY PLAN
605+
--------------------------------------
606+
ProjectSet (actual rows=10 loops=1)
607+
-> Result (actual rows=1 loops=1)
608+
(2 rows)
609+
610+
REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2;
608611
CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS
609612
SELECT generate_series(1, 10) WITH NO DATA;
610613
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
@@ -616,11 +619,14 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
616619
-> Result (never executed)
617620
(2 rows)
618621

622+
REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2;
619623
RESET SESSION AUTHORIZATION;
620624
ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
621625
GRANT INSERT ON TABLES TO regress_matview_user;
622626
DROP SCHEMA matview_schema CASCADE;
623-
NOTICE: drop cascades to 2 other objects
624-
DETAIL: drop cascades to materialized view matview_schema.mv_nodata1
627+
NOTICE: drop cascades to 4 other objects
628+
DETAIL: drop cascades to materialized view matview_schema.mv_withdata1
629+
drop cascades to materialized view matview_schema.mv_withdata2
630+
drop cascades to materialized view matview_schema.mv_nodata1
625631
drop cascades to materialized view matview_schema.mv_nodata2
626632
DROP USER regress_matview_user;

‎src/test/regress/expected/select_into.out

Lines changed: 40 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -20,79 +20,73 @@ ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
2020
REVOKE INSERT ON TABLES FROM regress_selinto_user;
2121
GRANT ALL ON SCHEMA selinto_schema TO public;
2222
SET SESSION AUTHORIZATION regress_selinto_user;
23-
SELECT * INTO TABLE selinto_schema.tmp1
24-
FROM pg_class WHERE relname like '%a%';
25-
ERROR: permission denied for table tmp1
26-
SELECT oid AS clsoid, relname, relnatts + 10 AS x
27-
INTO selinto_schema.tmp2
28-
FROM pg_class WHERE relname like '%b%';
29-
ERROR: permission denied for table tmp2
30-
-- WITH DATA, fails
31-
CREATE TABLE selinto_schema.tbl_withdata (a,b,c)
32-
AS SELECT oid,relname,relacl FROM pg_class
33-
WHERE relname like '%c%' WITH DATA;
34-
ERROR: permission denied for table tbl_withdata
23+
-- WITH DATA, passes.
24+
CREATE TABLE selinto_schema.tbl_withdata1 (a)
25+
AS SELECT generate_series(1,3) WITH DATA;
26+
INSERT INTO selinto_schema.tbl_withdata1 VALUES (4);
27+
ERROR: permission denied for table tbl_withdata1
3528
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
36-
CREATE TABLE selinto_schema.tbl_withdata (a,b,c)
37-
AS SELECT oid,relname,relacl FROM pg_class
38-
WHERE relname like '%c%' WITH DATA;
39-
ERROR: permission denied for table tbl_withdata
29+
CREATE TABLE selinto_schema.tbl_withdata2 (a) AS
30+
SELECT generate_series(1,3) WITH DATA;
31+
QUERY PLAN
32+
--------------------------------------
33+
ProjectSet (actual rows=3 loops=1)
34+
-> Result (actual rows=1 loops=1)
35+
(2 rows)
36+
4037
-- WITH NO DATA, passes.
4138
CREATE TABLE selinto_schema.tbl_nodata1 (a) AS
42-
SELECT oid FROM pg_class WHERE relname like '%c%' WITH NO DATA;
39+
SELECT generate_series(1,3) WITH NO DATA;
4340
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
4441
CREATE TABLE selinto_schema.tbl_nodata2 (a) AS
45-
SELECT oid FROM pg_class WHERE relname like '%c%' WITH NO DATA;
46-
QUERY PLAN
47-
---------------------------------------
48-
Seq Scan on pg_class (never executed)
49-
Filter: (relname ~~ '%c%'::text)
42+
SELECT generate_series(1,3) WITH NO DATA;
43+
QUERY PLAN
44+
-------------------------------
45+
ProjectSet (never executed)
46+
-> Result (never executed)
5047
(2 rows)
5148

52-
-- EXECUTE and WITH DATA, fails.
53-
PREPARE data_sel AS
54-
SELECT oid FROM pg_class WHERE relname like '%c%';
55-
CREATE TABLE selinto_schema.tbl_withdata (a) AS
49+
-- EXECUTE and WITH DATA, passes.
50+
PREPARE data_sel AS SELECT generate_series(1,3);
51+
CREATE TABLE selinto_schema.tbl_withdata3 (a) AS
5652
EXECUTE data_sel WITH DATA;
57-
ERROR: permission denied for table tbl_withdata
5853
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
59-
CREATE TABLE selinto_schema.tbl_withdata (a) AS
54+
CREATE TABLE selinto_schema.tbl_withdata4 (a) AS
6055
EXECUTE data_sel WITH DATA;
61-
ERROR: permission denied for table tbl_withdata
56+
QUERY PLAN
57+
--------------------------------------
58+
ProjectSet (actual rows=3 loops=1)
59+
-> Result (actual rows=1 loops=1)
60+
(2 rows)
61+
6262
-- EXECUTE and WITH NO DATA, passes.
6363
CREATE TABLE selinto_schema.tbl_nodata3 (a) AS
6464
EXECUTE data_sel WITH NO DATA;
6565
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
6666
CREATE TABLE selinto_schema.tbl_nodata4 (a) AS
6767
EXECUTE data_sel WITH NO DATA;
68-
QUERY PLAN
69-
---------------------------------------
70-
Seq Scan on pg_class (never executed)
71-
Filter: (relname ~~ '%c%'::text)
68+
QUERY PLAN
69+
-------------------------------
70+
ProjectSet (never executed)
71+
-> Result (never executed)
7272
(2 rows)
7373

7474
RESET SESSION AUTHORIZATION;
7575
ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
7676
GRANT INSERT ON TABLES TO regress_selinto_user;
7777
SET SESSION AUTHORIZATION regress_selinto_user;
78-
SELECT * INTO TABLE selinto_schema.tmp1
79-
FROM pg_class WHERE relname like '%a%'; -- OK
80-
SELECT oid AS clsoid, relname, relnatts + 10 AS x
81-
INTO selinto_schema.tmp2
82-
FROM pg_class WHERE relname like '%b%'; -- OK
83-
CREATE TABLE selinto_schema.tmp3 (a,b,c)
84-
AS SELECT oid,relname,relacl FROM pg_class
85-
WHERE relname like '%c%'; -- OK
8678
RESET SESSION AUTHORIZATION;
79+
DEALLOCATE data_sel;
8780
DROP SCHEMA selinto_schema CASCADE;
88-
NOTICE: drop cascades to 7 other objects
89-
DETAIL: drop cascades to table selinto_schema.tbl_nodata1
81+
NOTICE: drop cascades to 8 other objects
82+
DETAIL: drop cascades to table selinto_schema.tbl_withdata1
83+
drop cascades to table selinto_schema.tbl_withdata2
84+
drop cascades to table selinto_schema.tbl_nodata1
9085
drop cascades to table selinto_schema.tbl_nodata2
86+
drop cascades to table selinto_schema.tbl_withdata3
87+
drop cascades to table selinto_schema.tbl_withdata4
9188
drop cascades to table selinto_schema.tbl_nodata3
9289
drop cascades to table selinto_schema.tbl_nodata4
93-
drop cascades to table selinto_schema.tmp1
94-
drop cascades to table selinto_schema.tmp2
95-
drop cascades to table selinto_schema.tmp3
9690
DROP USER regress_selinto_user;
9791
-- Tests for WITH NO DATA and column name consistency
9892
CREATE TABLE ctas_base (i int, j int);

‎src/test/regress/sql/matview.sql

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -245,18 +245,18 @@ ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
245245
GRANT ALL ON SCHEMA matview_schema TO public;
246246

247247
SET SESSION AUTHORIZATION regress_matview_user;
248-
-- WITH DATA fails.
249248
CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS
250-
SELECT generate_series(1, 10) WITH DATA; -- error
249+
SELECT generate_series(1, 10) WITH DATA;
251250
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
252-
CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS
253-
SELECT generate_series(1, 10) WITH DATA; -- error
254-
-- WITH NO DATA passes.
251+
CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS
252+
SELECT generate_series(1, 10) WITH DATA;
253+
REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2;
255254
CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS
256255
SELECT generate_series(1, 10) WITH NO DATA;
257256
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
258257
CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS
259258
SELECT generate_series(1, 10) WITH NO DATA;
259+
REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2;
260260
RESET SESSION AUTHORIZATION;
261261

262262
ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user

‎src/test/regress/sql/select_into.sql

Lines changed: 13 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -26,32 +26,25 @@ ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
2626
GRANT ALL ON SCHEMA selinto_schema TO public;
2727

2828
SET SESSION AUTHORIZATION regress_selinto_user;
29-
SELECT * INTO TABLE selinto_schema.tmp1
30-
FROM pg_class WHERE relname like '%a%';
31-
SELECT oid AS clsoid, relname, relnatts + 10 AS x
32-
INTO selinto_schema.tmp2
33-
FROM pg_class WHERE relname like '%b%';
34-
-- WITH DATA, fails
35-
CREATE TABLE selinto_schema.tbl_withdata (a,b,c)
36-
AS SELECT oid,relname,relacl FROM pg_class
37-
WHERE relname like '%c%' WITH DATA;
29+
-- WITH DATA, passes.
30+
CREATE TABLE selinto_schema.tbl_withdata1 (a)
31+
AS SELECT generate_series(1,3) WITH DATA;
32+
INSERT INTO selinto_schema.tbl_withdata1 VALUES (4);
3833
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
39-
CREATE TABLE selinto_schema.tbl_withdata (a,b,c)
40-
AS SELECT oid,relname,relacl FROM pg_class
41-
WHERE relname like '%c%' WITH DATA;
34+
CREATE TABLE selinto_schema.tbl_withdata2 (a) AS
35+
SELECT generate_series(1,3) WITH DATA;
4236
-- WITH NO DATA, passes.
4337
CREATE TABLE selinto_schema.tbl_nodata1 (a) AS
44-
SELECT oid FROM pg_class WHERE relname like '%c%' WITH NO DATA;
38+
SELECT generate_series(1,3) WITH NO DATA;
4539
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
4640
CREATE TABLE selinto_schema.tbl_nodata2 (a) AS
47-
SELECT oid FROM pg_class WHERE relname like '%c%' WITH NO DATA;
48-
-- EXECUTE and WITH DATA, fails.
49-
PREPARE data_sel AS
50-
SELECT oid FROM pg_class WHERE relname like '%c%';
51-
CREATE TABLE selinto_schema.tbl_withdata (a) AS
41+
SELECT generate_series(1,3) WITH NO DATA;
42+
-- EXECUTE and WITH DATA, passes.
43+
PREPARE data_sel AS SELECT generate_series(1,3);
44+
CREATE TABLE selinto_schema.tbl_withdata3 (a) AS
5245
EXECUTE data_sel WITH DATA;
5346
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
54-
CREATE TABLE selinto_schema.tbl_withdata (a) AS
47+
CREATE TABLE selinto_schema.tbl_withdata4 (a) AS
5548
EXECUTE data_sel WITH DATA;
5649
-- EXECUTE and WITH NO DATA, passes.
5750
CREATE TABLE selinto_schema.tbl_nodata3 (a) AS
@@ -65,16 +58,9 @@ ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
6558
GRANT INSERT ON TABLES TO regress_selinto_user;
6659

6760
SET SESSION AUTHORIZATION regress_selinto_user;
68-
SELECT * INTO TABLE selinto_schema.tmp1
69-
FROM pg_class WHERE relname like '%a%'; -- OK
70-
SELECT oid AS clsoid, relname, relnatts + 10 AS x
71-
INTO selinto_schema.tmp2
72-
FROM pg_class WHERE relname like '%b%'; -- OK
73-
CREATE TABLE selinto_schema.tmp3 (a,b,c)
74-
AS SELECT oid,relname,relacl FROM pg_class
75-
WHERE relname like '%c%'; -- OK
7661
RESET SESSION AUTHORIZATION;
7762

63+
DEALLOCATE data_sel;
7864
DROP SCHEMA selinto_schema CASCADE;
7965
DROP USER regress_selinto_user;
8066

0 commit comments

Comments
 (0)