Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit846005e

Browse files
committed
Relax INSERT privilege requirement for CTAS and matviews WITH NO DATA
When specified, WITH NO DATA does not insert any data into the relationcreated, so skip checking for the insert permissions. With WITH DATA orWITH NO DATA, it is always required for the user to have CREATEprivileges on the schema targeted for the relation.Note that plain CREATE TABLE AS or CREATE MATERIALIZED VIEW queries havebegun to work accidentally without INSERT privilege checks as of874fe3a, while using EXECUTE or EXPLAIN ANALYZE would fail with the ACLcheck, so this makes the behavior for all the command flavors consistentwith each other. This is arguably a bug fix, but there have been nocomplaints about the current behavior either so stable branches are notchanged.While on it, document properly the privileges requirements for eachcommands with more tests for all the scenarios possible, and avoid auseless bulk-insert allocation when using WITH NO DATA.Author: Bharath RupireddyReviewed-by: Anastasia Lubennikova, Michael PaquierDiscussion:https://postgr.es/m/CALj2ACWc3N8j0_9nMPz9wcAUnVcdKHzFdDZJ3hVFNEbqtcyG9w@mail.gmail.com
1 parent29d29d6 commit846005e

File tree

7 files changed

+212
-48
lines changed

7 files changed

+212
-48
lines changed

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -48,6 +48,13 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
4848
A materialized view has many of the same properties as a table, but there
4949
is no support for temporary materialized views.
5050
</para>
51+
52+
<para>
53+
<command>CREATE MATERIALIZED VIEW</command> requires
54+
<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.
57+
</para>
5158
</refsect1>
5259

5360
<refsect1>

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,13 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
5353
defining <command>SELECT</command> statement whenever it is
5454
queried.
5555
</para>
56+
57+
<para>
58+
<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.
62+
</para>
5663
</refsect1>
5764

5865
<refsect1>

‎src/backend/commands/createas.c

Lines changed: 51 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -436,7 +436,6 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
436436
List*attrList;
437437
ObjectAddressintoRelationAddr;
438438
RelationintoRelationDesc;
439-
RangeTblEntry*rte;
440439
ListCell*lc;
441440
intattnum;
442441

@@ -507,23 +506,28 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
507506
intoRelationDesc=table_open(intoRelationAddr.objectId,AccessExclusiveLock);
508507

509508
/*
510-
* Check INSERT permission on the constructed table.
511-
*
512-
*XXX: It would arguably make sense to skip this check if into->skipData
513-
*is true.
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.
514513
*/
515-
rte=makeNode(RangeTblEntry);
516-
rte->rtekind=RTE_RELATION;
517-
rte->relid=intoRelationAddr.objectId;
518-
rte->relkind=relkind;
519-
rte->rellockmode=RowExclusiveLock;
520-
rte->requiredPerms=ACL_INSERT;
514+
if (!into->skipData)
515+
{
516+
RangeTblEntry*rte;
521517

522-
for (attnum=1;attnum <=intoRelationDesc->rd_att->natts;attnum++)
523-
rte->insertedCols=bms_add_member(rte->insertedCols,
524-
attnum-FirstLowInvalidHeapAttributeNumber);
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;
525524

526-
ExecCheckRTPerms(list_make1(rte), true);
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+
}
527531

528532
/*
529533
* Make sure the constructed table does not have RLS enabled.
@@ -552,7 +556,15 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
552556
myState->reladdr=intoRelationAddr;
553557
myState->output_cid=GetCurrentCommandId(true);
554558
myState->ti_options=TABLE_INSERT_SKIP_FSM;
555-
myState->bistate=GetBulkInsertState();
559+
560+
/*
561+
* If WITH NO DATA is specified, there is no need to set up the state for
562+
* bulk inserts as there are no tuples to insert.
563+
*/
564+
if (!into->skipData)
565+
myState->bistate=GetBulkInsertState();
566+
else
567+
myState->bistate=NULL;
556568

557569
/*
558570
* Valid smgr_targblock implies something already wrote to the relation.
@@ -569,20 +581,23 @@ intorel_receive(TupleTableSlot *slot, DestReceiver *self)
569581
{
570582
DR_intorel*myState= (DR_intorel*)self;
571583

572-
/*
573-
* Note that the input slot might not be of the type of the target
574-
* relation. That's supported by table_tuple_insert(), but slightly less
575-
* efficient than inserting with the right slot - but the alternative
576-
* would be to copy into a slot of the right type, which would not be
577-
* cheap either. This also doesn't allow accessing per-AM data (say a
578-
* tuple's xmin), but since we don't do that here...
579-
*/
580-
581-
table_tuple_insert(myState->rel,
582-
slot,
583-
myState->output_cid,
584-
myState->ti_options,
585-
myState->bistate);
584+
/* Nothing to insert if WITH NO DATA is specified. */
585+
if (!myState->into->skipData)
586+
{
587+
/*
588+
* Note that the input slot might not be of the type of the target
589+
* relation. That's supported by table_tuple_insert(), but slightly
590+
* less efficient than inserting with the right slot - but the
591+
* alternative would be to copy into a slot of the right type, which
592+
* would not be cheap either. This also doesn't allow accessing per-AM
593+
* data (say a tuple's xmin), but since we don't do that here...
594+
*/
595+
table_tuple_insert(myState->rel,
596+
slot,
597+
myState->output_cid,
598+
myState->ti_options,
599+
myState->bistate);
600+
}
586601

587602
/* We know this is a newly created relation, so there are no indexes */
588603

@@ -596,10 +611,13 @@ static void
596611
intorel_shutdown(DestReceiver*self)
597612
{
598613
DR_intorel*myState= (DR_intorel*)self;
614+
IntoClause*into=myState->into;
599615

600-
FreeBulkInsertState(myState->bistate);
601-
602-
table_finish_bulk_insert(myState->rel,myState->ti_options);
616+
if (!into->skipData)
617+
{
618+
FreeBulkInsertState(myState->bistate);
619+
table_finish_bulk_insert(myState->rel,myState->ti_options);
620+
}
603621

604622
/* close rel, but keep lock until commit */
605623
table_close(myState->rel,NoLock);

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

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -589,3 +589,38 @@ SELECT * FROM mvtest2;
589589
ERROR: materialized view "mvtest2" has not been populated
590590
HINT: Use the REFRESH MATERIALIZED VIEW command.
591591
ROLLBACK;
592+
-- INSERT privileges if relation owner is not allowed to insert.
593+
CREATE SCHEMA matview_schema;
594+
CREATE USER regress_matview_user;
595+
ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
596+
REVOKE INSERT ON TABLES FROM regress_matview_user;
597+
GRANT ALL ON SCHEMA matview_schema TO public;
598+
SET SESSION AUTHORIZATION regress_matview_user;
599+
-- WITH DATA fails.
600+
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
603+
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.
608+
CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS
609+
SELECT generate_series(1, 10) WITH NO DATA;
610+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
611+
CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS
612+
SELECT generate_series(1, 10) WITH NO DATA;
613+
QUERY PLAN
614+
-------------------------------
615+
ProjectSet (never executed)
616+
-> Result (never executed)
617+
(2 rows)
618+
619+
RESET SESSION AUTHORIZATION;
620+
ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
621+
GRANT INSERT ON TABLES TO regress_matview_user;
622+
DROP SCHEMA matview_schema CASCADE;
623+
NOTICE: drop cascades to 2 other objects
624+
DETAIL: drop cascades to materialized view matview_schema.mv_nodata1
625+
drop cascades to materialized view matview_schema.mv_nodata2
626+
DROP USER regress_matview_user;

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

Lines changed: 53 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -21,16 +21,56 @@ ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
2121
GRANT ALL ON SCHEMA selinto_schema TO public;
2222
SET SESSION AUTHORIZATION regress_selinto_user;
2323
SELECT * INTO TABLE selinto_schema.tmp1
24-
FROM pg_class WHERE relname like '%a%';-- Error
24+
FROM pg_class WHERE relname like '%a%';
2525
ERROR: permission denied for table tmp1
2626
SELECT oid AS clsoid, relname, relnatts + 10 AS x
27-
INTO selinto_schema.tmp2
28-
FROM pg_class WHERE relname like '%b%';-- Error
27+
INTO selinto_schema.tmp2
28+
FROM pg_class WHERE relname like '%b%';
2929
ERROR: permission denied for table tmp2
30-
CREATE TABLE selinto_schema.tmp3 (a,b,c)
31-
AS SELECT oid,relname,relacl FROM pg_class
32-
WHERE relname like '%c%';-- Error
33-
ERROR: permission denied for table tmp3
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
35+
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
40+
-- WITH NO DATA, passes.
41+
CREATE TABLE selinto_schema.tbl_nodata1 (a) AS
42+
SELECT oid FROM pg_class WHERE relname like '%c%' WITH NO DATA;
43+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
44+
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)
50+
(2 rows)
51+
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
56+
EXECUTE data_sel WITH DATA;
57+
ERROR: permission denied for table tbl_withdata
58+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
59+
CREATE TABLE selinto_schema.tbl_withdata (a) AS
60+
EXECUTE data_sel WITH DATA;
61+
ERROR: permission denied for table tbl_withdata
62+
-- EXECUTE and WITH NO DATA, passes.
63+
CREATE TABLE selinto_schema.tbl_nodata3 (a) AS
64+
EXECUTE data_sel WITH NO DATA;
65+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
66+
CREATE TABLE selinto_schema.tbl_nodata4 (a) AS
67+
EXECUTE data_sel WITH NO DATA;
68+
QUERY PLAN
69+
---------------------------------------
70+
Seq Scan on pg_class (never executed)
71+
Filter: (relname ~~ '%c%'::text)
72+
(2 rows)
73+
3474
RESET SESSION AUTHORIZATION;
3575
ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
3676
GRANT INSERT ON TABLES TO regress_selinto_user;
@@ -45,8 +85,12 @@ CREATE TABLE selinto_schema.tmp3 (a,b,c)
4585
WHERE relname like '%c%';-- OK
4686
RESET SESSION AUTHORIZATION;
4787
DROP SCHEMA selinto_schema CASCADE;
48-
NOTICE: drop cascades to 3 other objects
49-
DETAIL: drop cascades to table selinto_schema.tmp1
88+
NOTICE: drop cascades to 7 other objects
89+
DETAIL: drop cascades to table selinto_schema.tbl_nodata1
90+
drop cascades to table selinto_schema.tbl_nodata2
91+
drop cascades to table selinto_schema.tbl_nodata3
92+
drop cascades to table selinto_schema.tbl_nodata4
93+
drop cascades to table selinto_schema.tmp1
5094
drop cascades to table selinto_schema.tmp2
5195
drop cascades to table selinto_schema.tmp3
5296
DROP USER regress_selinto_user;

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

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -236,3 +236,31 @@ SELECT mvtest_func();
236236
SELECT*FROM mvtest1;
237237
SELECT*FROM mvtest2;
238238
ROLLBACK;
239+
240+
-- INSERT privileges if relation owner is not allowed to insert.
241+
CREATESCHEMAmatview_schema;
242+
CREATEUSERregress_matview_user;
243+
ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
244+
REVOKE INSERTON TABLESFROM regress_matview_user;
245+
GRANT ALLON SCHEMA matview_schema TO public;
246+
247+
SET SESSION AUTHORIZATION regress_matview_user;
248+
-- WITH DATA fails.
249+
CREATE MATERIALIZED VIEWmatview_schema.mv_withdata1 (a)AS
250+
SELECT generate_series(1,10) WITH DATA;-- error
251+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
252+
CREATE MATERIALIZED VIEWmatview_schema.mv_withdata1 (a)AS
253+
SELECT generate_series(1,10) WITH DATA;-- error
254+
-- WITH NO DATA passes.
255+
CREATE MATERIALIZED VIEWmatview_schema.mv_nodata1 (a)AS
256+
SELECT generate_series(1,10) WITH NO DATA;
257+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
258+
CREATE MATERIALIZED VIEWmatview_schema.mv_nodata2 (a)AS
259+
SELECT generate_series(1,10) WITH NO DATA;
260+
RESET SESSION AUTHORIZATION;
261+
262+
ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
263+
GRANT INSERTON TABLES TO regress_matview_user;
264+
265+
DROPSCHEMA matview_schema CASCADE;
266+
DROPUSER regress_matview_user;

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

Lines changed: 31 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -27,13 +27,38 @@ GRANT ALL ON SCHEMA selinto_schema TO public;
2727

2828
SET SESSION AUTHORIZATION regress_selinto_user;
2929
SELECT* INTO TABLEselinto_schema.tmp1
30-
FROM pg_classWHERE relnamelike'%a%';-- Error
30+
FROM pg_classWHERE relnamelike'%a%';
3131
SELECToidAS clsoid, relname, relnatts+10AS x
32-
INTOselinto_schema.tmp2
33-
FROM pg_classWHERE relnamelike'%b%';-- Error
34-
CREATETABLEselinto_schema.tmp3 (a,b,c)
35-
ASSELECToid,relname,relaclFROM pg_class
36-
WHERE relnamelike'%c%';-- Error
32+
INTOselinto_schema.tmp2
33+
FROM pg_classWHERE relnamelike'%b%';
34+
-- WITH DATA, fails
35+
CREATETABLEselinto_schema.tbl_withdata (a,b,c)
36+
ASSELECToid,relname,relaclFROM pg_class
37+
WHERE relnamelike'%c%' WITH DATA;
38+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
39+
CREATETABLEselinto_schema.tbl_withdata (a,b,c)
40+
ASSELECToid,relname,relaclFROM pg_class
41+
WHERE relnamelike'%c%' WITH DATA;
42+
-- WITH NO DATA, passes.
43+
CREATETABLEselinto_schema.tbl_nodata1 (a)AS
44+
SELECToidFROM pg_classWHERE relnamelike'%c%' WITH NO DATA;
45+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
46+
CREATETABLEselinto_schema.tbl_nodata2 (a)AS
47+
SELECToidFROM pg_classWHERE relnamelike'%c%' WITH NO DATA;
48+
-- EXECUTE and WITH DATA, fails.
49+
PREPARE data_selAS
50+
SELECToidFROM pg_classWHERE relnamelike'%c%';
51+
CREATETABLEselinto_schema.tbl_withdata (a)AS
52+
EXECUTE data_sel WITH DATA;
53+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
54+
CREATETABLEselinto_schema.tbl_withdata (a)AS
55+
EXECUTE data_sel WITH DATA;
56+
-- EXECUTE and WITH NO DATA, passes.
57+
CREATETABLEselinto_schema.tbl_nodata3 (a)AS
58+
EXECUTE data_sel WITH NO DATA;
59+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
60+
CREATETABLEselinto_schema.tbl_nodata4 (a)AS
61+
EXECUTE data_sel WITH NO DATA;
3762
RESET SESSION AUTHORIZATION;
3863

3964
ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp