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

Commitf1b4aa2

Browse files
committed
Check for INSERT privileges in SELECT INTO / CREATE TABLE AS.
In the normal course of events, this matters only if ALTER DEFAULTPRIVILEGES has been used to revoke default INSERT permission. Whetheror not the new behavior is more or less likely to be what the user wantswhen dealing only with the built-in privilege facilities is arguable,but it's clearly better when using a loadable module such as sepgsqlthat may use the hook in ExecCheckRTPerms to enforce additionalpermissions checks.KaiGai Kohei, reviewed by Albe Laurenz
1 parent766948b commitf1b4aa2

File tree

3 files changed

+93
-0
lines changed

3 files changed

+93
-0
lines changed

‎src/backend/executor/execMain.c

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2395,6 +2395,8 @@ OpenIntoRel(QueryDesc *queryDesc)
23952395
Datumreloptions;
23962396
OidintoRelationId;
23972397
DR_intorel*myState;
2398+
RangeTblEntry*rte;
2399+
AttrNumberattnum;
23982400
staticchar*validnsps[]=HEAP_RELOPT_NAMESPACES;
23992401

24002402
Assert(into);
@@ -2516,6 +2518,21 @@ OpenIntoRel(QueryDesc *queryDesc)
25162518
*/
25172519
intoRelationDesc=heap_open(intoRelationId,AccessExclusiveLock);
25182520

2521+
/*
2522+
* check INSERT permission on the constructed table.
2523+
*/
2524+
rte=makeNode(RangeTblEntry);
2525+
rte->rtekind=RTE_RELATION;
2526+
rte->relid=intoRelationId;
2527+
rte->relkind=RELKIND_RELATION;
2528+
rte->requiredPerms=ACL_INSERT;
2529+
2530+
for (attnum=1;attnum <=queryDesc->tupDesc->natts;attnum++)
2531+
rte->modifiedCols=bms_add_member(rte->modifiedCols,
2532+
attnum-FirstLowInvalidHeapAttributeNumber);
2533+
2534+
ExecCheckRTPerms(list_make1(rte), true);
2535+
25192536
/*
25202537
* Now replace the query's DestReceiver with one for SELECT INTO
25212538
*/

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

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11,3 +11,42 @@ SELECT *
1111
FROM onek2
1212
WHERE onek2.unique1 < 2;
1313
DROP TABLE tmp1;
14+
--
15+
-- SELECT INTO and INSERT permission, if owner is not allowed to insert.
16+
--
17+
CREATE SCHEMA selinto_schema;
18+
CREATE USER selinto_user;
19+
ALTER DEFAULT PRIVILEGES FOR ROLE selinto_user
20+
REVOKE INSERT ON TABLES FROM selinto_user;
21+
GRANT ALL ON SCHEMA selinto_schema TO public;
22+
SET SESSION AUTHORIZATION selinto_user;
23+
SELECT * INTO TABLE selinto_schema.tmp1
24+
FROM pg_class WHERE relname like '%a%';-- Error
25+
ERROR: permission denied for relation tmp1
26+
SELECT oid AS clsoid, relname, relnatts + 10 AS x
27+
INTO selinto_schema.tmp2
28+
FROM pg_class WHERE relname like '%b%';-- Error
29+
ERROR: permission denied for relation 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 relation tmp3
34+
RESET SESSION AUTHORIZATION;
35+
ALTER DEFAULT PRIVILEGES FOR ROLE selinto_user
36+
GRANT INSERT ON TABLES TO selinto_user;
37+
SET SESSION AUTHORIZATION selinto_user;
38+
SELECT * INTO TABLE selinto_schema.tmp1
39+
FROM pg_class WHERE relname like '%a%';-- OK
40+
SELECT oid AS clsoid, relname, relnatts + 10 AS x
41+
INTO selinto_schema.tmp2
42+
FROM pg_class WHERE relname like '%b%';-- OK
43+
CREATE TABLE selinto_schema.tmp3 (a,b,c)
44+
AS SELECT oid,relname,relacl FROM pg_class
45+
WHERE relname like '%c%';-- OK
46+
RESET SESSION AUTHORIZATION;
47+
DROP SCHEMA selinto_schema CASCADE;
48+
NOTICE: drop cascades to 3 other objects
49+
DETAIL: drop cascades to table selinto_schema.tmp1
50+
drop cascades to table selinto_schema.tmp2
51+
drop cascades to table selinto_schema.tmp3
52+
DROP USER selinto_user;

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

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,3 +15,40 @@ SELECT *
1515
WHEREonek2.unique1<2;
1616

1717
DROPTABLE tmp1;
18+
19+
--
20+
-- SELECT INTO and INSERT permission, if owner is not allowed to insert.
21+
--
22+
CREATESCHEMAselinto_schema;
23+
CREATEUSERselinto_user;
24+
ALTER DEFAULT PRIVILEGES FOR ROLE selinto_user
25+
REVOKE INSERTON TABLESFROM selinto_user;
26+
GRANT ALLON SCHEMA selinto_schema TO public;
27+
28+
SET SESSION AUTHORIZATION selinto_user;
29+
SELECT* INTO TABLEselinto_schema.tmp1
30+
FROM pg_classWHERE relnamelike'%a%';-- Error
31+
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
37+
RESET SESSION AUTHORIZATION;
38+
39+
ALTER DEFAULT PRIVILEGES FOR ROLE selinto_user
40+
GRANT INSERTON TABLES TO selinto_user;
41+
42+
SET SESSION AUTHORIZATION selinto_user;
43+
SELECT* INTO TABLEselinto_schema.tmp1
44+
FROM pg_classWHERE relnamelike'%a%';-- OK
45+
SELECToidAS clsoid, relname, relnatts+10AS x
46+
INTOselinto_schema.tmp2
47+
FROM pg_classWHERE relnamelike'%b%';-- OK
48+
CREATETABLEselinto_schema.tmp3 (a,b,c)
49+
ASSELECToid,relname,relaclFROM pg_class
50+
WHERE relnamelike'%c%';-- OK
51+
RESET SESSION AUTHORIZATION;
52+
53+
DROPSCHEMA selinto_schema CASCADE;
54+
DROPUSER selinto_user;

0 commit comments

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