diff options
| -rw-r--r-- | src/backend/rewrite/rewriteHandler.c | 31 | ||||
| -rw-r--r-- | src/test/regress/expected/updatable_views.out | 124 | ||||
| -rw-r--r-- | src/test/regress/sql/updatable_views.sql | 76 |
3 files changed, 219 insertions, 12 deletions
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 88140bc6877..981a233d107 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -1560,7 +1560,26 @@ ApplyRetrieveRule(Query *parsetree, AcquireRewriteLocks(rule_action, true, forUpdatePushedDown); /* + * If FOR [KEY] UPDATE/SHARE of view, mark all the contained tables as + * implicit FOR [KEY] UPDATE/SHARE, the same as the parser would have done + * if the view's subquery had been written out explicitly. + * + * Note: we needn't consider forUpdatePushedDown for this; if there was an + * ancestor query level with a relevant FOR [KEY] UPDATE/SHARE clause, + * that's already been pushed down to here and is reflected in "rc". + */ + if (rc != NULL) + markQueryForLocking(rule_action, (Node *) rule_action->jointree, + rc->strength, rc->waitPolicy, true); + + /* * Recursively expand any view references inside the view. + * + * Note: this must happen after markQueryForLocking. That way, any UPDATE + * permission bits needed for sub-views are initially applied to their + * RTE_RELATION RTEs by markQueryForLocking, and then transferred to their + * OLD rangetable entries by the action below (in a recursive call of this + * routine). */ rule_action = fireRIRrules(rule_action, activeRIRs, forUpdatePushedDown); @@ -1594,18 +1613,6 @@ ApplyRetrieveRule(Query *parsetree, rte->insertedCols = NULL; rte->updatedCols = NULL; - /* - * If FOR [KEY] UPDATE/SHARE of view, mark all the contained tables as - * implicit FOR [KEY] UPDATE/SHARE, the same as the parser would have done - * if the view's subquery had been written out explicitly. - * - * Note: we don't consider forUpdatePushedDown here; such marks will be - * made by recursing from the upper level in markQueryForLocking. - */ - if (rc != NULL) - markQueryForLocking(rule_action, (Node *) rule_action->jointree, - rc->strength, rc->waitPolicy, true); - return parsetree; } diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index aedf8ce4044..b34bab4b297 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -1058,6 +1058,130 @@ DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 +-- nested-view permissions +CREATE TABLE base_tbl(a int, b text, c float); +INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0); +SET SESSION AUTHORIZATION regress_view_user1; +CREATE VIEW rw_view1 AS SELECT * FROM base_tbl; +SELECT * FROM rw_view1; -- not allowed +ERROR: permission denied for table base_tbl +SELECT * FROM rw_view1 FOR UPDATE; -- not allowed +ERROR: permission denied for table base_tbl +UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed +ERROR: permission denied for table base_tbl +SET SESSION AUTHORIZATION regress_view_user2; +CREATE VIEW rw_view2 AS SELECT * FROM rw_view1; +SELECT * FROM rw_view2; -- not allowed +ERROR: permission denied for view rw_view1 +SELECT * FROM rw_view2 FOR UPDATE; -- not allowed +ERROR: permission denied for view rw_view1 +UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed +ERROR: permission denied for view rw_view1 +RESET SESSION AUTHORIZATION; +GRANT SELECT ON base_tbl TO regress_view_user1; +SET SESSION AUTHORIZATION regress_view_user1; +SELECT * FROM rw_view1; + a | b | c +---+-------+--- + 1 | Row 1 | 1 +(1 row) + +SELECT * FROM rw_view1 FOR UPDATE; -- not allowed +ERROR: permission denied for table base_tbl +UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed +ERROR: permission denied for table base_tbl +SET SESSION AUTHORIZATION regress_view_user2; +SELECT * FROM rw_view2; -- not allowed +ERROR: permission denied for view rw_view1 +SELECT * FROM rw_view2 FOR UPDATE; -- not allowed +ERROR: permission denied for view rw_view1 +UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed +ERROR: permission denied for view rw_view1 +SET SESSION AUTHORIZATION regress_view_user1; +GRANT SELECT ON rw_view1 TO regress_view_user2; +SET SESSION AUTHORIZATION regress_view_user2; +SELECT * FROM rw_view2; + a | b | c +---+-------+--- + 1 | Row 1 | 1 +(1 row) + +SELECT * FROM rw_view2 FOR UPDATE; -- not allowed +ERROR: permission denied for view rw_view1 +UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed +ERROR: permission denied for view rw_view1 +RESET SESSION AUTHORIZATION; +GRANT UPDATE ON base_tbl TO regress_view_user1; +SET SESSION AUTHORIZATION regress_view_user1; +SELECT * FROM rw_view1; + a | b | c +---+-------+--- + 1 | Row 1 | 1 +(1 row) + +SELECT * FROM rw_view1 FOR UPDATE; + a | b | c +---+-------+--- + 1 | Row 1 | 1 +(1 row) + +UPDATE rw_view1 SET b = 'foo' WHERE a = 1; +SET SESSION AUTHORIZATION regress_view_user2; +SELECT * FROM rw_view2; + a | b | c +---+-----+--- + 1 | foo | 1 +(1 row) + +SELECT * FROM rw_view2 FOR UPDATE; -- not allowed +ERROR: permission denied for view rw_view1 +UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed +ERROR: permission denied for view rw_view1 +SET SESSION AUTHORIZATION regress_view_user1; +GRANT UPDATE ON rw_view1 TO regress_view_user2; +SET SESSION AUTHORIZATION regress_view_user2; +SELECT * FROM rw_view2; + a | b | c +---+-----+--- + 1 | foo | 1 +(1 row) + +SELECT * FROM rw_view2 FOR UPDATE; + a | b | c +---+-----+--- + 1 | foo | 1 +(1 row) + +UPDATE rw_view2 SET b = 'bar' WHERE a = 1; +RESET SESSION AUTHORIZATION; +REVOKE UPDATE ON base_tbl FROM regress_view_user1; +SET SESSION AUTHORIZATION regress_view_user1; +SELECT * FROM rw_view1; + a | b | c +---+-----+--- + 1 | bar | 1 +(1 row) + +SELECT * FROM rw_view1 FOR UPDATE; -- not allowed +ERROR: permission denied for table base_tbl +UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed +ERROR: permission denied for table base_tbl +SET SESSION AUTHORIZATION regress_view_user2; +SELECT * FROM rw_view2; + a | b | c +---+-----+--- + 1 | bar | 1 +(1 row) + +SELECT * FROM rw_view2 FOR UPDATE; -- not allowed +ERROR: permission denied for table base_tbl +UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed +ERROR: permission denied for table base_tbl +RESET SESSION AUTHORIZATION; +DROP TABLE base_tbl CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to view rw_view1 +drop cascades to view rw_view2 DROP USER regress_view_user1; DROP USER regress_view_user2; -- column defaults diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index 6a9005243bb..a7786b26e97 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -459,6 +459,82 @@ RESET SESSION AUTHORIZATION; DROP TABLE base_tbl CASCADE; +-- nested-view permissions + +CREATE TABLE base_tbl(a int, b text, c float); +INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0); + +SET SESSION AUTHORIZATION regress_view_user1; +CREATE VIEW rw_view1 AS SELECT * FROM base_tbl; +SELECT * FROM rw_view1; -- not allowed +SELECT * FROM rw_view1 FOR UPDATE; -- not allowed +UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed + +SET SESSION AUTHORIZATION regress_view_user2; +CREATE VIEW rw_view2 AS SELECT * FROM rw_view1; +SELECT * FROM rw_view2; -- not allowed +SELECT * FROM rw_view2 FOR UPDATE; -- not allowed +UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed + +RESET SESSION AUTHORIZATION; +GRANT SELECT ON base_tbl TO regress_view_user1; + +SET SESSION AUTHORIZATION regress_view_user1; +SELECT * FROM rw_view1; +SELECT * FROM rw_view1 FOR UPDATE; -- not allowed +UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed + +SET SESSION AUTHORIZATION regress_view_user2; +SELECT * FROM rw_view2; -- not allowed +SELECT * FROM rw_view2 FOR UPDATE; -- not allowed +UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed + +SET SESSION AUTHORIZATION regress_view_user1; +GRANT SELECT ON rw_view1 TO regress_view_user2; + +SET SESSION AUTHORIZATION regress_view_user2; +SELECT * FROM rw_view2; +SELECT * FROM rw_view2 FOR UPDATE; -- not allowed +UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed + +RESET SESSION AUTHORIZATION; +GRANT UPDATE ON base_tbl TO regress_view_user1; + +SET SESSION AUTHORIZATION regress_view_user1; +SELECT * FROM rw_view1; +SELECT * FROM rw_view1 FOR UPDATE; +UPDATE rw_view1 SET b = 'foo' WHERE a = 1; + +SET SESSION AUTHORIZATION regress_view_user2; +SELECT * FROM rw_view2; +SELECT * FROM rw_view2 FOR UPDATE; -- not allowed +UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed + +SET SESSION AUTHORIZATION regress_view_user1; +GRANT UPDATE ON rw_view1 TO regress_view_user2; + +SET SESSION AUTHORIZATION regress_view_user2; +SELECT * FROM rw_view2; +SELECT * FROM rw_view2 FOR UPDATE; +UPDATE rw_view2 SET b = 'bar' WHERE a = 1; + +RESET SESSION AUTHORIZATION; +REVOKE UPDATE ON base_tbl FROM regress_view_user1; + +SET SESSION AUTHORIZATION regress_view_user1; +SELECT * FROM rw_view1; +SELECT * FROM rw_view1 FOR UPDATE; -- not allowed +UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed + +SET SESSION AUTHORIZATION regress_view_user2; +SELECT * FROM rw_view2; +SELECT * FROM rw_view2 FOR UPDATE; -- not allowed +UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed + +RESET SESSION AUTHORIZATION; + +DROP TABLE base_tbl CASCADE; + DROP USER regress_view_user1; DROP USER regress_view_user2; |