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

Commit50c6bb0

Browse files
committed
Fix enforcement of SELECT FOR UPDATE permissions with nested views.
SELECT FOR UPDATE on a view should require UPDATE (as well as SELECT)permissions on the view, and then the view's owner needs those samepermissions against the relations it references, and so on all the waydown to base tables. But ApplyRetrieveRule did things in the wrong order,resulting in failure to mark intermediate view levels as needing UPDATEpermission. Thus for example, if user A creates a table T and an updatableview V1 on T, then grants only SELECT permissions on V1 to user B, B couldcreate a second view V2 on V1 and then would be allowed to perform SELECTFOR UPDATE via V2 (since V1 wouldn't be checked for UPDATE permissions).To fix, just switch the order of expanding sub-views and marking referencedobjects as needing UPDATE permission. I think additional simplificationsare now possible, but that's distinct from the bug fix proper.This is certainly a security issue, but the consequences are pretty minor(just the ability to lock rows that shouldn't be lockable). Against thatwe have a small risk of breaking applications that are working as-desired,since nested views have behaved this way since such cases worked at all.On balance I'm inclined not to back-patch.Per report from Alexander Lakhin.Discussion:https://postgr.es/m/24db7b8f-3de5-e25f-7ab9-d8848351d42c@gmail.com
1 parent2a67d64 commit50c6bb0

File tree

3 files changed

+219
-12
lines changed

3 files changed

+219
-12
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 19 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1559,8 +1559,27 @@ ApplyRetrieveRule(Query *parsetree,
15591559

15601560
AcquireRewriteLocks(rule_action, true,forUpdatePushedDown);
15611561

1562+
/*
1563+
* If FOR [KEY] UPDATE/SHARE of view, mark all the contained tables as
1564+
* implicit FOR [KEY] UPDATE/SHARE, the same as the parser would have done
1565+
* if the view's subquery had been written out explicitly.
1566+
*
1567+
* Note: we needn't consider forUpdatePushedDown for this; if there was an
1568+
* ancestor query level with a relevant FOR [KEY] UPDATE/SHARE clause,
1569+
* that's already been pushed down to here and is reflected in "rc".
1570+
*/
1571+
if (rc!=NULL)
1572+
markQueryForLocking(rule_action, (Node*)rule_action->jointree,
1573+
rc->strength,rc->waitPolicy, true);
1574+
15621575
/*
15631576
* Recursively expand any view references inside the view.
1577+
*
1578+
* Note: this must happen after markQueryForLocking. That way, any UPDATE
1579+
* permission bits needed for sub-views are initially applied to their
1580+
* RTE_RELATION RTEs by markQueryForLocking, and then transferred to their
1581+
* OLD rangetable entries by the action below (in a recursive call of this
1582+
* routine).
15641583
*/
15651584
rule_action=fireRIRrules(rule_action,activeRIRs,forUpdatePushedDown);
15661585

@@ -1594,18 +1613,6 @@ ApplyRetrieveRule(Query *parsetree,
15941613
rte->insertedCols=NULL;
15951614
rte->updatedCols=NULL;
15961615

1597-
/*
1598-
* If FOR [KEY] UPDATE/SHARE of view, mark all the contained tables as
1599-
* implicit FOR [KEY] UPDATE/SHARE, the same as the parser would have done
1600-
* if the view's subquery had been written out explicitly.
1601-
*
1602-
* Note: we don't consider forUpdatePushedDown here; such marks will be
1603-
* made by recursing from the upper level in markQueryForLocking.
1604-
*/
1605-
if (rc!=NULL)
1606-
markQueryForLocking(rule_action, (Node*)rule_action->jointree,
1607-
rc->strength,rc->waitPolicy, true);
1608-
16091616
returnparsetree;
16101617
}
16111618

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

Lines changed: 124 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1053,6 +1053,130 @@ SELECT * FROM base_tbl;
10531053
5 | Row 5 | 5
10541054
(2 rows)
10551055

1056+
RESET SESSION AUTHORIZATION;
1057+
DROP TABLE base_tbl CASCADE;
1058+
NOTICE: drop cascades to 2 other objects
1059+
DETAIL: drop cascades to view rw_view1
1060+
drop cascades to view rw_view2
1061+
-- nested-view permissions
1062+
CREATE TABLE base_tbl(a int, b text, c float);
1063+
INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);
1064+
SET SESSION AUTHORIZATION regress_view_user1;
1065+
CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;
1066+
SELECT * FROM rw_view1; -- not allowed
1067+
ERROR: permission denied for table base_tbl
1068+
SELECT * FROM rw_view1 FOR UPDATE; -- not allowed
1069+
ERROR: permission denied for table base_tbl
1070+
UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed
1071+
ERROR: permission denied for table base_tbl
1072+
SET SESSION AUTHORIZATION regress_view_user2;
1073+
CREATE VIEW rw_view2 AS SELECT * FROM rw_view1;
1074+
SELECT * FROM rw_view2; -- not allowed
1075+
ERROR: permission denied for view rw_view1
1076+
SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1077+
ERROR: permission denied for view rw_view1
1078+
UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1079+
ERROR: permission denied for view rw_view1
1080+
RESET SESSION AUTHORIZATION;
1081+
GRANT SELECT ON base_tbl TO regress_view_user1;
1082+
SET SESSION AUTHORIZATION regress_view_user1;
1083+
SELECT * FROM rw_view1;
1084+
a | b | c
1085+
---+-------+---
1086+
1 | Row 1 | 1
1087+
(1 row)
1088+
1089+
SELECT * FROM rw_view1 FOR UPDATE; -- not allowed
1090+
ERROR: permission denied for table base_tbl
1091+
UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed
1092+
ERROR: permission denied for table base_tbl
1093+
SET SESSION AUTHORIZATION regress_view_user2;
1094+
SELECT * FROM rw_view2; -- not allowed
1095+
ERROR: permission denied for view rw_view1
1096+
SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1097+
ERROR: permission denied for view rw_view1
1098+
UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1099+
ERROR: permission denied for view rw_view1
1100+
SET SESSION AUTHORIZATION regress_view_user1;
1101+
GRANT SELECT ON rw_view1 TO regress_view_user2;
1102+
SET SESSION AUTHORIZATION regress_view_user2;
1103+
SELECT * FROM rw_view2;
1104+
a | b | c
1105+
---+-------+---
1106+
1 | Row 1 | 1
1107+
(1 row)
1108+
1109+
SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1110+
ERROR: permission denied for view rw_view1
1111+
UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1112+
ERROR: permission denied for view rw_view1
1113+
RESET SESSION AUTHORIZATION;
1114+
GRANT UPDATE ON base_tbl TO regress_view_user1;
1115+
SET SESSION AUTHORIZATION regress_view_user1;
1116+
SELECT * FROM rw_view1;
1117+
a | b | c
1118+
---+-------+---
1119+
1 | Row 1 | 1
1120+
(1 row)
1121+
1122+
SELECT * FROM rw_view1 FOR UPDATE;
1123+
a | b | c
1124+
---+-------+---
1125+
1 | Row 1 | 1
1126+
(1 row)
1127+
1128+
UPDATE rw_view1 SET b = 'foo' WHERE a = 1;
1129+
SET SESSION AUTHORIZATION regress_view_user2;
1130+
SELECT * FROM rw_view2;
1131+
a | b | c
1132+
---+-----+---
1133+
1 | foo | 1
1134+
(1 row)
1135+
1136+
SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1137+
ERROR: permission denied for view rw_view1
1138+
UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1139+
ERROR: permission denied for view rw_view1
1140+
SET SESSION AUTHORIZATION regress_view_user1;
1141+
GRANT UPDATE ON rw_view1 TO regress_view_user2;
1142+
SET SESSION AUTHORIZATION regress_view_user2;
1143+
SELECT * FROM rw_view2;
1144+
a | b | c
1145+
---+-----+---
1146+
1 | foo | 1
1147+
(1 row)
1148+
1149+
SELECT * FROM rw_view2 FOR UPDATE;
1150+
a | b | c
1151+
---+-----+---
1152+
1 | foo | 1
1153+
(1 row)
1154+
1155+
UPDATE rw_view2 SET b = 'bar' WHERE a = 1;
1156+
RESET SESSION AUTHORIZATION;
1157+
REVOKE UPDATE ON base_tbl FROM regress_view_user1;
1158+
SET SESSION AUTHORIZATION regress_view_user1;
1159+
SELECT * FROM rw_view1;
1160+
a | b | c
1161+
---+-----+---
1162+
1 | bar | 1
1163+
(1 row)
1164+
1165+
SELECT * FROM rw_view1 FOR UPDATE; -- not allowed
1166+
ERROR: permission denied for table base_tbl
1167+
UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed
1168+
ERROR: permission denied for table base_tbl
1169+
SET SESSION AUTHORIZATION regress_view_user2;
1170+
SELECT * FROM rw_view2;
1171+
a | b | c
1172+
---+-----+---
1173+
1 | bar | 1
1174+
(1 row)
1175+
1176+
SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1177+
ERROR: permission denied for table base_tbl
1178+
UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1179+
ERROR: permission denied for table base_tbl
10561180
RESET SESSION AUTHORIZATION;
10571181
DROP TABLE base_tbl CASCADE;
10581182
NOTICE: drop cascades to 2 other objects

‎src/test/regress/sql/updatable_views.sql

Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -459,6 +459,82 @@ RESET SESSION AUTHORIZATION;
459459

460460
DROPTABLE base_tbl CASCADE;
461461

462+
-- nested-view permissions
463+
464+
CREATETABLEbase_tbl(aint, btext, c float);
465+
INSERT INTO base_tblVALUES (1,'Row 1',1.0);
466+
467+
SET SESSION AUTHORIZATION regress_view_user1;
468+
CREATEVIEWrw_view1ASSELECT*FROM base_tbl;
469+
SELECT*FROM rw_view1;-- not allowed
470+
SELECT*FROM rw_view1 FORUPDATE;-- not allowed
471+
UPDATE rw_view1SET b='foo'WHERE a=1;-- not allowed
472+
473+
SET SESSION AUTHORIZATION regress_view_user2;
474+
CREATEVIEWrw_view2ASSELECT*FROM rw_view1;
475+
SELECT*FROM rw_view2;-- not allowed
476+
SELECT*FROM rw_view2 FORUPDATE;-- not allowed
477+
UPDATE rw_view2SET b='bar'WHERE a=1;-- not allowed
478+
479+
RESET SESSION AUTHORIZATION;
480+
GRANTSELECTON base_tbl TO regress_view_user1;
481+
482+
SET SESSION AUTHORIZATION regress_view_user1;
483+
SELECT*FROM rw_view1;
484+
SELECT*FROM rw_view1 FORUPDATE;-- not allowed
485+
UPDATE rw_view1SET b='foo'WHERE a=1;-- not allowed
486+
487+
SET SESSION AUTHORIZATION regress_view_user2;
488+
SELECT*FROM rw_view2;-- not allowed
489+
SELECT*FROM rw_view2 FORUPDATE;-- not allowed
490+
UPDATE rw_view2SET b='bar'WHERE a=1;-- not allowed
491+
492+
SET SESSION AUTHORIZATION regress_view_user1;
493+
GRANTSELECTON rw_view1 TO regress_view_user2;
494+
495+
SET SESSION AUTHORIZATION regress_view_user2;
496+
SELECT*FROM rw_view2;
497+
SELECT*FROM rw_view2 FORUPDATE;-- not allowed
498+
UPDATE rw_view2SET b='bar'WHERE a=1;-- not allowed
499+
500+
RESET SESSION AUTHORIZATION;
501+
GRANTUPDATEON base_tbl TO regress_view_user1;
502+
503+
SET SESSION AUTHORIZATION regress_view_user1;
504+
SELECT*FROM rw_view1;
505+
SELECT*FROM rw_view1 FORUPDATE;
506+
UPDATE rw_view1SET b='foo'WHERE a=1;
507+
508+
SET SESSION AUTHORIZATION regress_view_user2;
509+
SELECT*FROM rw_view2;
510+
SELECT*FROM rw_view2 FORUPDATE;-- not allowed
511+
UPDATE rw_view2SET b='bar'WHERE a=1;-- not allowed
512+
513+
SET SESSION AUTHORIZATION regress_view_user1;
514+
GRANTUPDATEON rw_view1 TO regress_view_user2;
515+
516+
SET SESSION AUTHORIZATION regress_view_user2;
517+
SELECT*FROM rw_view2;
518+
SELECT*FROM rw_view2 FORUPDATE;
519+
UPDATE rw_view2SET b='bar'WHERE a=1;
520+
521+
RESET SESSION AUTHORIZATION;
522+
REVOKEUPDATEON base_tblFROM regress_view_user1;
523+
524+
SET SESSION AUTHORIZATION regress_view_user1;
525+
SELECT*FROM rw_view1;
526+
SELECT*FROM rw_view1 FORUPDATE;-- not allowed
527+
UPDATE rw_view1SET b='foo'WHERE a=1;-- not allowed
528+
529+
SET SESSION AUTHORIZATION regress_view_user2;
530+
SELECT*FROM rw_view2;
531+
SELECT*FROM rw_view2 FORUPDATE;-- not allowed
532+
UPDATE rw_view2SET b='bar'WHERE a=1;-- not allowed
533+
534+
RESET SESSION AUTHORIZATION;
535+
536+
DROPTABLE base_tbl CASCADE;
537+
462538
DROPUSER regress_view_user1;
463539
DROPUSER regress_view_user2;
464540

0 commit comments

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