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

Commitca74e3e

Browse files
committed
Use checkAsUser for selectivity estimator checks, if it's set.
In examine_variable() and examine_simple_variable(), when checking theuser's table and column privileges to determine whether to grantaccess to the pg_statistic data, use checkAsUser for the privilegechecks, if it's set. This will be the case if we're accessing thetable via a view, to indicate that we should perform privilege checksas the view owner rather than the current user.This change makes this planner check consistent with the check in theexecutor, so the planner will be able to make use of statistics if thetable is accessible via the view. This fixes a performance regressionintroduced by commite2d4ef8, which affects queries againstnon-security barrier views in the case where the user doesn't haveprivileges on the underlying table, but the view owner does.Note that it continues to provide the same safeguards controllingaccess to pg_statistic for direct table access (in which casecheckAsUser won't be set) and for security barrier views, because ofthe nearby checks on rte->security_barrier and rte->securityQuals.Back-patch to all supported branches becausee2d4ef8 was.Dean Rasheed, reviewed by Jonathan Katz and Stephen Frost.
1 parent9408028 commitca74e3e

File tree

3 files changed

+101
-15
lines changed

3 files changed

+101
-15
lines changed

‎src/backend/utils/adt/selfuncs.c

Lines changed: 16 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -4778,10 +4778,17 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
47784778
{
47794779
/* Get index's table for permission check */
47804780
RangeTblEntry*rte;
4781+
Oiduserid;
47814782

47824783
rte=planner_rt_fetch(index->rel->relid,root);
47834784
Assert(rte->rtekind==RTE_RELATION);
47844785

4786+
/*
4787+
* Use checkAsUser if it's set, in case we're
4788+
* accessing the table via a view.
4789+
*/
4790+
userid=rte->checkAsUser ?rte->checkAsUser :GetUserId();
4791+
47854792
/*
47864793
* For simplicity, we insist on the whole
47874794
* table being selectable, rather than trying
@@ -4793,7 +4800,7 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
47934800
*/
47944801
vardata->acl_ok=
47954802
rte->securityQuals==NIL&&
4796-
(pg_class_aclcheck(rte->relid,GetUserId(),
4803+
(pg_class_aclcheck(rte->relid,userid,
47974804
ACL_SELECT)==ACLCHECK_OK);
47984805
}
47994806
else
@@ -4856,16 +4863,21 @@ examine_simple_variable(PlannerInfo *root, Var *var,
48564863

48574864
if (HeapTupleIsValid(vardata->statsTuple))
48584865
{
4866+
Oiduserid;
4867+
48594868
/*
48604869
* Check if user has permission to read this column. We require
48614870
* all rows to be accessible, so there must be no securityQuals
4862-
* from security barrier views or RLS policies.
4871+
* from security barrier views or RLS policies. Use checkAsUser
4872+
* if it's set, in case we're accessing the table via a view.
48634873
*/
4874+
userid=rte->checkAsUser ?rte->checkAsUser :GetUserId();
4875+
48644876
vardata->acl_ok=
48654877
rte->securityQuals==NIL&&
4866-
((pg_class_aclcheck(rte->relid,GetUserId(),
4878+
((pg_class_aclcheck(rte->relid,userid,
48674879
ACL_SELECT)==ACLCHECK_OK)||
4868-
(pg_attribute_aclcheck(rte->relid,var->varattno,GetUserId(),
4880+
(pg_attribute_aclcheck(rte->relid,var->varattno,userid,
48694881
ACL_SELECT)==ACLCHECK_OK));
48704882
}
48714883
else

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

Lines changed: 64 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -185,7 +185,7 @@ SELECT * FROM atest1; -- ok
185185
(2 rows)
186186

187187
-- test leaky-function protections in selfuncs
188-
-- regress_user1 will own a table and providea view for it.
188+
-- regress_user1 will own a table and provideviews for it.
189189
SET SESSION AUTHORIZATION regress_user1;
190190
CREATE TABLE atest12 as
191191
SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x;
@@ -197,10 +197,13 @@ CREATE FUNCTION leak(integer,integer) RETURNS boolean
197197
LANGUAGE plpgsql immutable;
198198
CREATE OPERATOR <<< (procedure = leak, leftarg = integer, rightarg = integer,
199199
restrict = scalarltsel);
200-
--view with leaky operator
200+
--views with leaky operator
201201
CREATE VIEW atest12v AS
202202
SELECT * FROM atest12 WHERE b <<< 5;
203+
CREATE VIEW atest12sbv WITH (security_barrier=true) AS
204+
SELECT * FROM atest12 WHERE b <<< 5;
203205
GRANT SELECT ON atest12v TO PUBLIC;
206+
GRANT SELECT ON atest12sbv TO PUBLIC;
204207
-- This plan should use nestloop, knowing that few rows will be selected.
205208
EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
206209
QUERY PLAN
@@ -225,6 +228,20 @@ EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y
225228
Index Cond: (a = y.b)
226229
(5 rows)
227230

231+
-- This should also be a nestloop, but the security barrier forces the inner
232+
-- scan to be materialized
233+
EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b;
234+
QUERY PLAN
235+
-------------------------------------------
236+
Nested Loop
237+
Join Filter: (atest12.a = atest12_1.b)
238+
-> Seq Scan on atest12
239+
Filter: (b <<< 5)
240+
-> Materialize
241+
-> Seq Scan on atest12 atest12_1
242+
Filter: (b <<< 5)
243+
(7 rows)
244+
228245
-- Check if regress_user2 can break security.
229246
SET SESSION AUTHORIZATION regress_user2;
230247
CREATE FUNCTION leak2(integer,integer) RETURNS boolean
@@ -235,24 +252,64 @@ CREATE OPERATOR >>> (procedure = leak2, leftarg = integer, rightarg = integer,
235252
-- This should not show any "leak" notices before failing.
236253
EXPLAIN (COSTS OFF) SELECT * FROM atest12 WHERE a >>> 0;
237254
ERROR: permission denied for relation atest12
238-
-- This plan should use hashjoin, as it will expect many rows to be selected.
255+
-- These plans should continue to use a nestloop, since they execute with the
256+
-- privileges of the view owner.
239257
EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
258+
QUERY PLAN
259+
-------------------------------------------------
260+
Nested Loop
261+
-> Seq Scan on atest12 atest12_1
262+
Filter: (b <<< 5)
263+
-> Index Scan using atest12_a_idx on atest12
264+
Index Cond: (a = atest12_1.b)
265+
Filter: (b <<< 5)
266+
(6 rows)
267+
268+
EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b;
240269
QUERY PLAN
241270
-------------------------------------------
242-
Hash Join
243-
Hash Cond: (atest12.a = atest12_1.b)
271+
Nested Loop
272+
Join Filter: (atest12.a = atest12_1.b)
244273
-> Seq Scan on atest12
245274
Filter: (b <<< 5)
246-
->Hash
275+
->Materialize
247276
-> Seq Scan on atest12 atest12_1
248277
Filter: (b <<< 5)
249278
(7 rows)
250279

280+
-- A non-security barrier view does not guard against information leakage.
281+
EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y
282+
WHERE x.a = y.b and abs(y.a) <<< 5;
283+
QUERY PLAN
284+
-------------------------------------------------
285+
Nested Loop
286+
-> Seq Scan on atest12 atest12_1
287+
Filter: ((b <<< 5) AND (abs(a) <<< 5))
288+
-> Index Scan using atest12_a_idx on atest12
289+
Index Cond: (a = atest12_1.b)
290+
Filter: (b <<< 5)
291+
(6 rows)
292+
293+
-- But a security barrier view isolates the leaky operator.
294+
EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y
295+
WHERE x.a = y.b and abs(y.a) <<< 5;
296+
QUERY PLAN
297+
-------------------------------------
298+
Nested Loop
299+
Join Filter: (atest12_1.a = y.b)
300+
-> Subquery Scan on y
301+
Filter: (abs(y.a) <<< 5)
302+
-> Seq Scan on atest12
303+
Filter: (b <<< 5)
304+
-> Seq Scan on atest12 atest12_1
305+
Filter: (b <<< 5)
306+
(8 rows)
307+
251308
-- Now regress_user1 grants sufficient access to regress_user2.
252309
SET SESSION AUTHORIZATION regress_user1;
253310
GRANT SELECT (a, b) ON atest12 TO PUBLIC;
254311
SET SESSION AUTHORIZATION regress_user2;
255-
--Nowregress_user2will also get a good row estimate.
312+
-- regress_user2should continue to get a good row estimate.
256313
EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
257314
QUERY PLAN
258315
-------------------------------------------------

‎src/test/regress/sql/privileges.sql

Lines changed: 21 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -129,7 +129,7 @@ SELECT * FROM atest1; -- ok
129129

130130
-- test leaky-function protections in selfuncs
131131

132-
-- regress_user1 will own a table and providea view for it.
132+
-- regress_user1 will own a table and provideviews for it.
133133
SET SESSION AUTHORIZATION regress_user1;
134134

135135
CREATETABLEatest12as
@@ -144,10 +144,13 @@ CREATE FUNCTION leak(integer,integer) RETURNS boolean
144144
CREATE OPERATOR<<< (procedure= leak, leftarg=integer, rightarg=integer,
145145
restrict= scalarltsel);
146146

147-
--view with leaky operator
147+
--views with leaky operator
148148
CREATEVIEWatest12vAS
149149
SELECT*FROM atest12WHERE b<<<5;
150+
CREATEVIEWatest12sbv WITH (security_barrier=true)AS
151+
SELECT*FROM atest12WHERE b<<<5;
150152
GRANTSELECTON atest12v TO PUBLIC;
153+
GRANTSELECTON atest12sbv TO PUBLIC;
151154

152155
-- This plan should use nestloop, knowing that few rows will be selected.
153156
EXPLAIN (COSTS OFF)SELECT*FROM atest12v x, atest12v yWHEREx.a=y.b;
@@ -156,6 +159,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
156159
EXPLAIN (COSTS OFF)SELECT*FROM atest12 x, atest12 y
157160
WHEREx.a=y.band abs(y.a)<<<5;
158161

162+
-- This should also be a nestloop, but the security barrier forces the inner
163+
-- scan to be materialized
164+
EXPLAIN (COSTS OFF)SELECT*FROM atest12sbv x, atest12sbv yWHEREx.a=y.b;
165+
159166
-- Check if regress_user2 can break security.
160167
SET SESSION AUTHORIZATION regress_user2;
161168

@@ -168,15 +175,25 @@ CREATE OPERATOR >>> (procedure = leak2, leftarg = integer, rightarg = integer,
168175
-- This should not show any "leak" notices before failing.
169176
EXPLAIN (COSTS OFF)SELECT*FROM atest12WHERE a>>>0;
170177

171-
-- This plan should use hashjoin, as it will expect many rows to be selected.
178+
-- These plans should continue to use a nestloop, since they execute with the
179+
-- privileges of the view owner.
172180
EXPLAIN (COSTS OFF)SELECT*FROM atest12v x, atest12v yWHEREx.a=y.b;
181+
EXPLAIN (COSTS OFF)SELECT*FROM atest12sbv x, atest12sbv yWHEREx.a=y.b;
182+
183+
-- A non-security barrier view does not guard against information leakage.
184+
EXPLAIN (COSTS OFF)SELECT*FROM atest12v x, atest12v y
185+
WHEREx.a=y.band abs(y.a)<<<5;
186+
187+
-- But a security barrier view isolates the leaky operator.
188+
EXPLAIN (COSTS OFF)SELECT*FROM atest12sbv x, atest12sbv y
189+
WHEREx.a=y.band abs(y.a)<<<5;
173190

174191
-- Now regress_user1 grants sufficient access to regress_user2.
175192
SET SESSION AUTHORIZATION regress_user1;
176193
GRANTSELECT (a, b)ON atest12 TO PUBLIC;
177194
SET SESSION AUTHORIZATION regress_user2;
178195

179-
--Nowregress_user2will also get a good row estimate.
196+
-- regress_user2should continue to get a good row estimate.
180197
EXPLAIN (COSTS OFF)SELECT*FROM atest12v x, atest12v yWHEREx.a=y.b;
181198

182199
-- But not for this, due to lack of table-wide permissions needed

0 commit comments

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