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

Commit9b69d5c

Browse files
committed
Fix incorrect varlevelsup in security_barrier_replace_vars().
When converting an RTE with securityQuals into a security barriersubquery RTE, ensure that the Vars in the new subquery's targetlistall have varlevelsup = 0 so that they correctly refer to theunderlying base relation being wrapped.The original code was creating new Vars by copying them from existingVars referencing the base relation found elsewhere in the query, butfailed to account for the fact that such Vars could come from sublinksubqueries, and hence have varlevelsup > 0. In practice it looks likethis could only happen with nested security barrier views, where theouter view has a WHERE clause containing a correlated subquery, due tothe order in which the Vars are processed.Bug: #13988Reported-by: Adam GuthrieBackpatch-to: 9.4, where updatable SB views were introduced
1 parent80c925c commit9b69d5c

File tree

3 files changed

+74
-0
lines changed

3 files changed

+74
-0
lines changed

‎src/backend/optimizer/prep/prepsecurity.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -454,6 +454,7 @@ security_barrier_replace_vars_walker(Node *node,
454454
/* New variable for subquery targetlist */
455455
newvar=copyObject(var);
456456
newvar->varno=newvar->varnoold=1;
457+
newvar->varlevelsup=0;
457458

458459
attno=list_length(context->targetlist)+1;
459460
tle=makeTargetEntry((Expr*)newvar,

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

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2351,3 +2351,42 @@ DROP VIEW vx1;
23512351
DROP TABLE tx1;
23522352
DROP TABLE tx2;
23532353
DROP TABLE tx3;
2354+
--
2355+
-- Test handling of vars from correlated subqueries in quals from outer
2356+
-- security barrier views, per bug #13988
2357+
--
2358+
CREATE TABLE t1 (a int, b text, c int);
2359+
INSERT INTO t1 VALUES (1, 'one', 10);
2360+
CREATE TABLE t2 (cc int);
2361+
INSERT INTO t2 VALUES (10), (20);
2362+
CREATE VIEW v1 WITH (security_barrier = true) AS
2363+
SELECT * FROM t1 WHERE (a > 0)
2364+
WITH CHECK OPTION;
2365+
CREATE VIEW v2 WITH (security_barrier = true) AS
2366+
SELECT * FROM v1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.cc = v1.c)
2367+
WITH CHECK OPTION;
2368+
INSERT INTO v2 VALUES (2, 'two', 20); -- ok
2369+
INSERT INTO v2 VALUES (-2, 'minus two', 20); -- not allowed
2370+
ERROR: new row violates WITH CHECK OPTION for view "v1"
2371+
DETAIL: Failing row contains (-2, minus two, 20).
2372+
INSERT INTO v2 VALUES (3, 'three', 30); -- not allowed
2373+
ERROR: new row violates WITH CHECK OPTION for view "v2"
2374+
DETAIL: Failing row contains (3, three, 30).
2375+
UPDATE v2 SET b = 'ONE' WHERE a = 1; -- ok
2376+
UPDATE v2 SET a = -1 WHERE a = 1; -- not allowed
2377+
ERROR: new row violates WITH CHECK OPTION for view "v1"
2378+
DETAIL: Failing row contains (-1, ONE, 10).
2379+
UPDATE v2 SET c = 30 WHERE a = 1; -- not allowed
2380+
ERROR: new row violates WITH CHECK OPTION for view "v2"
2381+
DETAIL: Failing row contains (1, ONE, 30).
2382+
DELETE FROM v2 WHERE a = 2; -- ok
2383+
SELECT * FROM v2;
2384+
a | b | c
2385+
---+-----+----
2386+
1 | ONE | 10
2387+
(1 row)
2388+
2389+
DROP VIEW v2;
2390+
DROP VIEW v1;
2391+
DROP TABLE t2;
2392+
DROP TABLE t1;

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

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1055,3 +1055,37 @@ DROP VIEW vx1;
10551055
DROPTABLE tx1;
10561056
DROPTABLE tx2;
10571057
DROPTABLE tx3;
1058+
1059+
--
1060+
-- Test handling of vars from correlated subqueries in quals from outer
1061+
-- security barrier views, per bug #13988
1062+
--
1063+
CREATETABLEt1 (aint, btext, cint);
1064+
INSERT INTO t1VALUES (1,'one',10);
1065+
1066+
CREATETABLEt2 (ccint);
1067+
INSERT INTO t2VALUES (10), (20);
1068+
1069+
CREATEVIEWv1 WITH (security_barrier= true)AS
1070+
SELECT*FROM t1WHERE (a>0)
1071+
WITHCHECK OPTION;
1072+
1073+
CREATEVIEWv2 WITH (security_barrier= true)AS
1074+
SELECT*FROM v1WHERE EXISTS (SELECT1FROM t2WHEREt2.cc=v1.c)
1075+
WITHCHECK OPTION;
1076+
1077+
INSERT INTO v2VALUES (2,'two',20);-- ok
1078+
INSERT INTO v2VALUES (-2,'minus two',20);-- not allowed
1079+
INSERT INTO v2VALUES (3,'three',30);-- not allowed
1080+
1081+
UPDATE v2SET b='ONE'WHERE a=1;-- ok
1082+
UPDATE v2SET a=-1WHERE a=1;-- not allowed
1083+
UPDATE v2SET c=30WHERE a=1;-- not allowed
1084+
1085+
DELETEFROM v2WHERE a=2;-- ok
1086+
SELECT*FROM v2;
1087+
1088+
DROPVIEW v2;
1089+
DROPVIEW v1;
1090+
DROPTABLE t2;
1091+
DROPTABLE t1;

0 commit comments

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