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

Commit75096c4

Browse files
committed
Include policies based on ACLs needed
When considering which policies should be included, rather than look atindividual bits of the query (eg: if a RETURNING clause exists, or if aWHERE clause exists which is referencing the table, or if it's aFOR SHARE/UPDATE query), consider any case where we've determinedthe user needs SELECT rights on the relation while doing an UPDATE orDELETE to be a case where we apply SELECT policies, and any case wherewe've deteremind that the user needs UPDATE rights on the relation whiledoing a SELECT to be a case where we apply UPDATE policies.This simplifies the logic and addresses concerns that a user could useUPDATE or DELETE with a WHERE clauses to determine if rows exist, orthey could use SELECT .. FOR UPDATE to lock rows which they are notactually allowed to modify through UPDATE policies.Use list_append_unique() to avoid adding the same quals multiple times,as, on balance, the cost of checking when adding the quals will almostalways be cheaper than keeping them and doing busywork for each tupleduring execution.Back-patch to 9.5 where RLS was added.
1 parent3c4c5ac commit75096c4

File tree

2 files changed

+101
-67
lines changed

2 files changed

+101
-67
lines changed

‎src/backend/rewrite/rowsecurity.c

Lines changed: 74 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -165,18 +165,58 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
165165
commandType=rt_index==root->resultRelation ?
166166
root->commandType :CMD_SELECT;
167167

168-
get_policies_for_relation(rel,commandType,user_id,&permissive_policies,
169-
&restrictive_policies);
168+
/*
169+
* In some cases, we need to apply USING policies (which control the
170+
* visibility of records) associated with multiple command types (see
171+
* specific cases below).
172+
*
173+
* When considering the order in which to apply these USING policies,
174+
* we prefer to apply higher privileged policies, those which allow the
175+
* user to lock records (UPDATE and DELETE), first, followed by policies
176+
* which don't (SELECT).
177+
*
178+
* Note that the optimizer is free to push down and reorder quals which
179+
* use leakproof functions.
180+
*
181+
* In all cases, if there are no policy clauses allowing access to rows in
182+
* the table for the specific type of operation, then a single always-false
183+
* clause (a default-deny policy) will be added (see add_security_quals).
184+
*/
185+
186+
/*
187+
* For a SELECT, if UPDATE privileges are required (eg: the user has
188+
* specified FOR [KEY] UPDATE/SHARE), then add the UPDATE USING quals first.
189+
*
190+
* This way, we filter out any records from the SELECT FOR SHARE/UPDATE
191+
* which the user does not have access to via the UPDATE USING policies,
192+
* similar to how we require normal UPDATE rights for these queries.
193+
*/
194+
if (commandType==CMD_SELECT&&rte->requiredPerms&ACL_UPDATE)
195+
{
196+
List*update_permissive_policies;
197+
List*update_restrictive_policies;
198+
199+
get_policies_for_relation(rel,CMD_UPDATE,user_id,
200+
&update_permissive_policies,
201+
&update_restrictive_policies);
202+
203+
add_security_quals(rt_index,
204+
update_permissive_policies,
205+
update_restrictive_policies,
206+
securityQuals,
207+
hasSubLinks);
208+
}
170209

171210
/*
172-
* For SELECT, UPDATE and DELETE, add security quals to enforcethese
211+
* For SELECT, UPDATE and DELETE, add security quals to enforcethe USING
173212
* policies. These security quals control access to existing table rows.
174213
* Restrictive policies are "AND"d together, and permissive policies are
175214
* "OR"d together.
176-
*
177-
* If there are no policy clauses controlling access to the table, this
178-
* will add a single always-false clause (a default-deny policy).
179215
*/
216+
217+
get_policies_for_relation(rel,commandType,user_id,&permissive_policies,
218+
&restrictive_policies);
219+
180220
if (commandType==CMD_SELECT||
181221
commandType==CMD_UPDATE||
182222
commandType==CMD_DELETE)
@@ -187,28 +227,27 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
187227
hasSubLinks);
188228

189229
/*
190-
*For the target relation, when there is a returning list, we need to
191-
*collect up CMD_SELECT policies and add them via add_security_quals.
192-
*This is because, for the RETURNING case, wehave to filter any records
193-
*which are not visible through an ALL or SELECT USING policy.
230+
*Similar to above, during an UPDATE or DELETE, if SELECT rights are also
231+
*required (eg: when a RETURNING clause exists, or the user has provided
232+
*a WHERE clause which involves columns from the relation), wecollect up
233+
*CMD_SELECT policies and add them via add_security_quals first.
194234
*
195-
* We don't need to worry about the non-target relation case because we are
196-
* checking the ALL and SELECT policies for those relations anyway (see
197-
* above).
235+
* This way, we filter out any records which are not visible through an ALL
236+
* or SELECT USING policy.
198237
*/
199-
if (root->returningList!=NIL&&
200-
(commandType==CMD_UPDATE||commandType==CMD_DELETE))
238+
if ((commandType==CMD_UPDATE||commandType==CMD_DELETE)&&
239+
rte->requiredPerms&ACL_SELECT)
201240
{
202-
List*returning_permissive_policies;
203-
List*returning_restrictive_policies;
241+
List*select_permissive_policies;
242+
List*select_restrictive_policies;
204243

205244
get_policies_for_relation(rel,CMD_SELECT,user_id,
206-
&returning_permissive_policies,
207-
&returning_restrictive_policies);
245+
&select_permissive_policies,
246+
&select_restrictive_policies);
208247

209248
add_security_quals(rt_index,
210-
returning_permissive_policies,
211-
returning_restrictive_policies,
249+
select_permissive_policies,
250+
select_restrictive_policies,
212251
securityQuals,
213252
hasSubLinks);
214253
}
@@ -261,21 +300,22 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
261300
hasSubLinks);
262301

263302
/*
264-
* Get and add ALL/SELECT policies, if there is a RETURNING clause,
265-
* also as WCO policies, again, to avoid silently dropping data.
303+
* Get and add ALL/SELECT policies, if SELECT rights are required
304+
* for this relation, also as WCO policies, again, to avoid
305+
* silently dropping data. See above.
266306
*/
267-
if (root->returningList!=NIL)
307+
if (rte->requiredPerms&ACL_SELECT)
268308
{
269-
List*conflict_returning_permissive_policies=NIL;
270-
List*conflict_returning_restrictive_policies=NIL;
309+
List*conflict_select_permissive_policies=NIL;
310+
List*conflict_select_restrictive_policies=NIL;
271311

272312
get_policies_for_relation(rel,CMD_SELECT,user_id,
273-
&conflict_returning_permissive_policies,
274-
&conflict_returning_restrictive_policies);
313+
&conflict_select_permissive_policies,
314+
&conflict_select_restrictive_policies);
275315
add_with_check_options(rel,rt_index,
276316
WCO_RLS_CONFLICT_CHECK,
277-
conflict_returning_permissive_policies,
278-
conflict_returning_restrictive_policies,
317+
conflict_select_permissive_policies,
318+
conflict_select_restrictive_policies,
279319
withCheckOptions,
280320
hasSubLinks);
281321
}
@@ -524,7 +564,7 @@ add_security_quals(int rt_index,
524564
qual=copyObject(policy->qual);
525565
ChangeVarNodes((Node*)qual,1,rt_index,0);
526566

527-
*securityQuals=lappend(*securityQuals,qual);
567+
*securityQuals=list_append_unique(*securityQuals,qual);
528568
*hasSubLinks |=policy->hassublinks;
529569
}
530570
}
@@ -539,7 +579,7 @@ add_security_quals(int rt_index,
539579
rowsec_expr=makeBoolExpr(OR_EXPR,permissive_quals,-1);
540580

541581
ChangeVarNodes((Node*)rowsec_expr,1,rt_index,0);
542-
*securityQuals=lappend(*securityQuals,rowsec_expr);
582+
*securityQuals=list_append_unique(*securityQuals,rowsec_expr);
543583
}
544584
else
545585
/*
@@ -631,7 +671,7 @@ add_with_check_options(Relation rel,
631671

632672
ChangeVarNodes(wco->qual,1,rt_index,0);
633673

634-
*withCheckOptions=lappend(*withCheckOptions,wco);
674+
*withCheckOptions=list_append_unique(*withCheckOptions,wco);
635675

636676
/*
637677
* Now add WithCheckOptions for each of the restrictive policy clauses
@@ -657,7 +697,7 @@ add_with_check_options(Relation rel,
657697
wco->qual= (Node*)qual;
658698
wco->cascaded= false;
659699

660-
*withCheckOptions=lappend(*withCheckOptions,wco);
700+
*withCheckOptions=list_append_unique(*withCheckOptions,wco);
661701
*hasSubLinks |=policy->hassublinks;
662702
}
663703
}

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

Lines changed: 27 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -1220,23 +1220,21 @@ NOTICE: f_leak => cde
12201220
EXPLAIN (COSTS OFF) UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
12211221
WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
12221222
AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
1223-
QUERY PLAN
1224-
---------------------------------------------------------------------
1223+
QUERY PLAN
1224+
---------------------------------------------------------------
12251225
Update on t2 t2_1_1
12261226
-> Nested Loop
12271227
Join Filter: (t2_1.b = t2_2.b)
12281228
-> Subquery Scan on t2_1
12291229
Filter: f_leak(t2_1.b)
1230-
-> Subquery Scan on t2_1_2
1231-
Filter: ((t2_1_2.a % 2) = 1)
1232-
-> LockRows
1233-
-> Seq Scan on t2 t2_1_3
1234-
Filter: ((a = 3) AND ((a % 2) = 1))
1230+
-> LockRows
1231+
-> Seq Scan on t2 t2_1_2
1232+
Filter: ((a = 3) AND ((a % 2) = 1))
12351233
-> Subquery Scan on t2_2
12361234
Filter: f_leak(t2_2.b)
12371235
-> Seq Scan on t2 t2_2_1
12381236
Filter: ((a = 3) AND ((a % 2) = 1))
1239-
(14 rows)
1237+
(12 rows)
12401238

12411239
UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
12421240
WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
@@ -1251,8 +1249,8 @@ NOTICE: f_leak => cde
12511249
EXPLAIN (COSTS OFF) UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
12521250
WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
12531251
AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
1254-
QUERY PLAN
1255-
---------------------------------------------------------------------
1252+
QUERY PLAN
1253+
---------------------------------------------------------------
12561254
Update on t1 t1_1_3
12571255
Update on t1 t1_1_3
12581256
Update on t2 t1_1
@@ -1261,11 +1259,9 @@ AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
12611259
Join Filter: (t1_1.b = t1_2.b)
12621260
-> Subquery Scan on t1_1
12631261
Filter: f_leak(t1_1.b)
1264-
-> Subquery Scan on t1_1_4
1265-
Filter: ((t1_1_4.a % 2) = 0)
1266-
-> LockRows
1267-
-> Seq Scan on t1 t1_1_5
1268-
Filter: ((a = 4) AND ((a % 2) = 0))
1262+
-> LockRows
1263+
-> Seq Scan on t1 t1_1_4
1264+
Filter: ((a = 4) AND ((a % 2) = 0))
12691265
-> Subquery Scan on t1_2
12701266
Filter: f_leak(t1_2.b)
12711267
-> Append
@@ -1279,11 +1275,9 @@ AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
12791275
Join Filter: (t1_1_1.b = t1_2_1.b)
12801276
-> Subquery Scan on t1_1_1
12811277
Filter: f_leak(t1_1_1.b)
1282-
-> Subquery Scan on t1_1_6
1283-
Filter: ((t1_1_6.a % 2) = 0)
1284-
-> LockRows
1285-
-> Seq Scan on t2 t1_1_7
1286-
Filter: ((a = 4) AND ((a % 2) = 0))
1278+
-> LockRows
1279+
-> Seq Scan on t2 t1_1_5
1280+
Filter: ((a = 4) AND ((a % 2) = 0))
12871281
-> Subquery Scan on t1_2_1
12881282
Filter: f_leak(t1_2_1.b)
12891283
-> Append
@@ -1297,11 +1291,9 @@ AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
12971291
Join Filter: (t1_1_2.b = t1_2_2.b)
12981292
-> Subquery Scan on t1_1_2
12991293
Filter: f_leak(t1_1_2.b)
1300-
-> Subquery Scan on t1_1_8
1301-
Filter: ((t1_1_8.a % 2) = 0)
1302-
-> LockRows
1303-
-> Seq Scan on t3 t1_1_9
1304-
Filter: ((a = 4) AND ((a % 2) = 0))
1294+
-> LockRows
1295+
-> Seq Scan on t3 t1_1_6
1296+
Filter: ((a = 4) AND ((a % 2) = 0))
13051297
-> Subquery Scan on t1_2_2
13061298
Filter: f_leak(t1_2_2.b)
13071299
-> Append
@@ -1311,7 +1303,7 @@ AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
13111303
Filter: ((a = 4) AND ((a % 2) = 0))
13121304
-> Seq Scan on t3 t1_2_11
13131305
Filter: ((a = 4) AND ((a % 2) = 0))
1314-
(58 rows)
1306+
(52 rows)
13151307

13161308
UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
13171309
WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
@@ -2743,15 +2735,17 @@ SELECT * FROM current_check;
27432735

27442736
-- Plan should be a subquery TID scan
27452737
EXPLAIN (COSTS OFF) UPDATE current_check SET payload = payload WHERE CURRENT OF current_check_cursor;
2746-
QUERY PLAN
2747-
---------------------------------------------------------------
2738+
QUERY PLAN
2739+
---------------------------------------------------------------------
27482740
Update on current_check current_check_1
27492741
-> Subquery Scan on current_check
2750-
-> LockRows
2751-
-> Tid Scan on current_check current_check_2
2752-
TID Cond: CURRENT OF current_check_cursor
2753-
Filter: (currentid = 4)
2754-
(6 rows)
2742+
-> Subquery Scan on current_check_2
2743+
Filter: ((current_check_2.currentid % 2) = 0)
2744+
-> LockRows
2745+
-> Tid Scan on current_check current_check_3
2746+
TID Cond: CURRENT OF current_check_cursor
2747+
Filter: (currentid = 4)
2748+
(8 rows)
27552749

27562750
-- Similarly can only delete row 4
27572751
FETCH ABSOLUTE 1 FROM current_check_cursor;

0 commit comments

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