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

Commitb26e3d6

Browse files
committed
Make RLS work with UPDATE ... WHERE CURRENT OF
UPDATE ... WHERE CURRENT OF would not work in conjunction withRLS. Arrange to allow the CURRENT OF expression to be pushed down.Issue noted by Peter Geoghegan. Patch by Dean Rasheed. Back patchto 9.5 where RLS was introduced.
1 parentd9a356f commitb26e3d6

File tree

4 files changed

+206
-0
lines changed

4 files changed

+206
-0
lines changed

‎src/backend/optimizer/path/allpaths.c

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2177,6 +2177,46 @@ subquery_push_qual(Query *subquery, RangeTblEntry *rte, Index rti, Node *qual)
21772177
recurse_push_qual(subquery->setOperations,subquery,
21782178
rte,rti,qual);
21792179
}
2180+
elseif (IsA(qual,CurrentOfExpr))
2181+
{
2182+
/*
2183+
* This is possible when a WHERE CURRENT OF expression is applied to a
2184+
* table with row-level security. In that case, the subquery should
2185+
* contain precisely one rtable entry for the table, and we can safely
2186+
* push the expression down into the subquery. This will cause a TID
2187+
* scan subquery plan to be generated allowing the target relation to
2188+
* be updated.
2189+
*
2190+
* Someday we might also be able to use a WHERE CURRENT OF expression
2191+
* on a view, but currently the rewriter prevents that, so we should
2192+
* never see any other case here, but generate sane error messages in
2193+
* case it does somehow happen.
2194+
*/
2195+
if (subquery->rtable==NIL)
2196+
ereport(ERROR,
2197+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2198+
errmsg("WHERE CURRENT OF is not supported on a view with no underlying relation")));
2199+
2200+
if (list_length(subquery->rtable)>1)
2201+
ereport(ERROR,
2202+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2203+
errmsg("WHERE CURRENT OF is not supported on a view with more than one underlying relation")));
2204+
2205+
if (subquery->hasAggs||subquery->groupClause||subquery->groupingSets||subquery->havingQual)
2206+
ereport(ERROR,
2207+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2208+
errmsg("WHERE CURRENT OF is not supported on a view with grouping or aggregation")));
2209+
2210+
/*
2211+
* Adjust the CURRENT OF expression to refer to the underlying table
2212+
* in the subquery, and attach it to the subquery's WHERE clause.
2213+
*/
2214+
qual=copyObject(qual);
2215+
((CurrentOfExpr*)qual)->cvarno=1;
2216+
2217+
subquery->jointree->quals=
2218+
make_and_qual(subquery->jointree->quals,qual);
2219+
}
21802220
else
21812221
{
21822222
/*

‎src/backend/optimizer/util/clauses.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1492,6 +1492,16 @@ contain_leaked_vars_walker(Node *node, void *context)
14921492
}
14931493
break;
14941494

1495+
caseT_CurrentOfExpr:
1496+
1497+
/*
1498+
* WHERE CURRENT OF doesn't contain function calls. Moreover, it
1499+
* is important that this can be pushed down into a
1500+
* security_barrier view, since the planner must always generate
1501+
* a TID scan when CURRENT OF is present -- c.f. cost_tidscan.
1502+
*/
1503+
return false;
1504+
14951505
default:
14961506

14971507
/*

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

Lines changed: 107 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2729,6 +2729,113 @@ COPY copy_t FROM STDIN; --fail - permission denied.
27292729
ERROR: permission denied for relation copy_t
27302730
RESET SESSION AUTHORIZATION;
27312731
DROP TABLE copy_t;
2732+
-- Check WHERE CURRENT OF
2733+
SET SESSION AUTHORIZATION rls_regress_user0;
2734+
CREATE TABLE current_check (currentid int, payload text, rlsuser text);
2735+
GRANT ALL ON current_check TO PUBLIC;
2736+
INSERT INTO current_check VALUES
2737+
(1, 'abc', 'rls_regress_user1'),
2738+
(2, 'bcd', 'rls_regress_user1'),
2739+
(3, 'cde', 'rls_regress_user1'),
2740+
(4, 'def', 'rls_regress_user1');
2741+
CREATE POLICY p1 ON current_check FOR SELECT USING (currentid % 2 = 0);
2742+
CREATE POLICY p2 ON current_check FOR DELETE USING (currentid = 4 AND rlsuser = current_user);
2743+
CREATE POLICY p3 ON current_check FOR UPDATE USING (currentid = 4) WITH CHECK (rlsuser = current_user);
2744+
ALTER TABLE current_check ENABLE ROW LEVEL SECURITY;
2745+
SET SESSION AUTHORIZATION rls_regress_user1;
2746+
-- Can SELECT even rows
2747+
SELECT * FROM current_check;
2748+
currentid | payload | rlsuser
2749+
-----------+---------+-------------------
2750+
2 | bcd | rls_regress_user1
2751+
4 | def | rls_regress_user1
2752+
(2 rows)
2753+
2754+
-- Cannot UPDATE row 2
2755+
UPDATE current_check SET payload = payload || '_new' WHERE currentid = 2 RETURNING *;
2756+
currentid | payload | rlsuser
2757+
-----------+---------+---------
2758+
(0 rows)
2759+
2760+
BEGIN;
2761+
DECLARE current_check_cursor SCROLL CURSOR FOR SELECT * FROM current_check;
2762+
-- Returns rows that can be seen according to SELECT policy, like plain SELECT
2763+
-- above (even rows)
2764+
FETCH ABSOLUTE 1 FROM current_check_cursor;
2765+
currentid | payload | rlsuser
2766+
-----------+---------+-------------------
2767+
2 | bcd | rls_regress_user1
2768+
(1 row)
2769+
2770+
-- Still cannot UPDATE row 2 through cursor
2771+
UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
2772+
currentid | payload | rlsuser
2773+
-----------+---------+---------
2774+
(0 rows)
2775+
2776+
-- Can update row 4 through cursor, which is the next visible row
2777+
FETCH RELATIVE 1 FROM current_check_cursor;
2778+
currentid | payload | rlsuser
2779+
-----------+---------+-------------------
2780+
4 | def | rls_regress_user1
2781+
(1 row)
2782+
2783+
UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
2784+
currentid | payload | rlsuser
2785+
-----------+---------+-------------------
2786+
4 | def_new | rls_regress_user1
2787+
(1 row)
2788+
2789+
SELECT * FROM current_check;
2790+
currentid | payload | rlsuser
2791+
-----------+---------+-------------------
2792+
2 | bcd | rls_regress_user1
2793+
4 | def_new | rls_regress_user1
2794+
(2 rows)
2795+
2796+
-- Plan should be a subquery TID scan
2797+
EXPLAIN (COSTS OFF) UPDATE current_check SET payload = payload WHERE CURRENT OF current_check_cursor;
2798+
QUERY PLAN
2799+
---------------------------------------------------------------
2800+
Update on current_check current_check_1
2801+
-> Subquery Scan on current_check
2802+
-> LockRows
2803+
-> Tid Scan on current_check current_check_2
2804+
TID Cond: CURRENT OF current_check_cursor
2805+
Filter: (currentid = 4)
2806+
(6 rows)
2807+
2808+
-- Similarly can only delete row 4
2809+
FETCH ABSOLUTE 1 FROM current_check_cursor;
2810+
currentid | payload | rlsuser
2811+
-----------+---------+-------------------
2812+
2 | bcd | rls_regress_user1
2813+
(1 row)
2814+
2815+
DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
2816+
currentid | payload | rlsuser
2817+
-----------+---------+---------
2818+
(0 rows)
2819+
2820+
FETCH RELATIVE 1 FROM current_check_cursor;
2821+
currentid | payload | rlsuser
2822+
-----------+---------+-------------------
2823+
4 | def | rls_regress_user1
2824+
(1 row)
2825+
2826+
DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
2827+
currentid | payload | rlsuser
2828+
-----------+---------+-------------------
2829+
4 | def_new | rls_regress_user1
2830+
(1 row)
2831+
2832+
SELECT * FROM current_check;
2833+
currentid | payload | rlsuser
2834+
-----------+---------+-------------------
2835+
2 | bcd | rls_regress_user1
2836+
(1 row)
2837+
2838+
COMMIT;
27322839
--
27332840
-- Collation support
27342841
--

‎src/test/regress/sql/rowsecurity.sql

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1087,6 +1087,55 @@ COPY copy_t FROM STDIN; --fail - permission denied.
10871087
RESET SESSION AUTHORIZATION;
10881088
DROPTABLE copy_t;
10891089

1090+
-- Check WHERE CURRENT OF
1091+
SET SESSION AUTHORIZATION rls_regress_user0;
1092+
1093+
CREATETABLEcurrent_check (currentidint, payloadtext, rlsusertext);
1094+
GRANT ALLON current_check TO PUBLIC;
1095+
1096+
INSERT INTO current_checkVALUES
1097+
(1,'abc','rls_regress_user1'),
1098+
(2,'bcd','rls_regress_user1'),
1099+
(3,'cde','rls_regress_user1'),
1100+
(4,'def','rls_regress_user1');
1101+
1102+
CREATE POLICY p1ON current_check FORSELECT USING (currentid %2=0);
1103+
CREATE POLICY p2ON current_check FORDELETE USING (currentid=4AND rlsuser=current_user);
1104+
CREATE POLICY p3ON current_check FORUPDATE USING (currentid=4) WITHCHECK (rlsuser=current_user);
1105+
1106+
ALTERTABLE current_check ENABLE ROW LEVEL SECURITY;
1107+
1108+
SET SESSION AUTHORIZATION rls_regress_user1;
1109+
1110+
-- Can SELECT even rows
1111+
SELECT*FROM current_check;
1112+
1113+
-- Cannot UPDATE row 2
1114+
UPDATE current_checkSET payload= payload||'_new'WHERE currentid=2 RETURNING*;
1115+
1116+
BEGIN;
1117+
1118+
DECLARE current_check_cursor SCROLL CURSOR FORSELECT*FROM current_check;
1119+
-- Returns rows that can be seen according to SELECT policy, like plain SELECT
1120+
-- above (even rows)
1121+
FETCH ABSOLUTE1FROM current_check_cursor;
1122+
-- Still cannot UPDATE row 2 through cursor
1123+
UPDATE current_checkSET payload= payload||'_new'WHERE CURRENT OF current_check_cursor RETURNING*;
1124+
-- Can update row 4 through cursor, which is the next visible row
1125+
FETCH RELATIVE1FROM current_check_cursor;
1126+
UPDATE current_checkSET payload= payload||'_new'WHERE CURRENT OF current_check_cursor RETURNING*;
1127+
SELECT*FROM current_check;
1128+
-- Plan should be a subquery TID scan
1129+
EXPLAIN (COSTS OFF)UPDATE current_checkSET payload= payloadWHERE CURRENT OF current_check_cursor;
1130+
-- Similarly can only delete row 4
1131+
FETCH ABSOLUTE1FROM current_check_cursor;
1132+
DELETEFROM current_checkWHERE CURRENT OF current_check_cursor RETURNING*;
1133+
FETCH RELATIVE1FROM current_check_cursor;
1134+
DELETEFROM current_checkWHERE CURRENT OF current_check_cursor RETURNING*;
1135+
SELECT*FROM current_check;
1136+
1137+
COMMIT;
1138+
10901139
--
10911140
-- Collation support
10921141
--

0 commit comments

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