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

Commit41fedc2

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 parent907e4dd commit41fedc2

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
@@ -446,6 +446,7 @@ security_barrier_replace_vars_walker(Node *node,
446446
/* New variable for subquery targetlist */
447447
newvar=copyObject(var);
448448
newvar->varno=newvar->varnoold=1;
449+
newvar->varlevelsup=0;
449450

450451
attno=list_length(context->targetlist)+1;
451452
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
@@ -2420,3 +2420,42 @@ DROP VIEW vx1;
24202420
DROP TABLE tx1;
24212421
DROP TABLE tx2;
24222422
DROP TABLE tx3;
2423+
--
2424+
-- Test handling of vars from correlated subqueries in quals from outer
2425+
-- security barrier views, per bug #13988
2426+
--
2427+
CREATE TABLE t1 (a int, b text, c int);
2428+
INSERT INTO t1 VALUES (1, 'one', 10);
2429+
CREATE TABLE t2 (cc int);
2430+
INSERT INTO t2 VALUES (10), (20);
2431+
CREATE VIEW v1 WITH (security_barrier = true) AS
2432+
SELECT * FROM t1 WHERE (a > 0)
2433+
WITH CHECK OPTION;
2434+
CREATE VIEW v2 WITH (security_barrier = true) AS
2435+
SELECT * FROM v1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.cc = v1.c)
2436+
WITH CHECK OPTION;
2437+
INSERT INTO v2 VALUES (2, 'two', 20); -- ok
2438+
INSERT INTO v2 VALUES (-2, 'minus two', 20); -- not allowed
2439+
ERROR: new row violates check option for view "v1"
2440+
DETAIL: Failing row contains (-2, minus two, 20).
2441+
INSERT INTO v2 VALUES (3, 'three', 30); -- not allowed
2442+
ERROR: new row violates check option for view "v2"
2443+
DETAIL: Failing row contains (3, three, 30).
2444+
UPDATE v2 SET b = 'ONE' WHERE a = 1; -- ok
2445+
UPDATE v2 SET a = -1 WHERE a = 1; -- not allowed
2446+
ERROR: new row violates check option for view "v1"
2447+
DETAIL: Failing row contains (-1, ONE, 10).
2448+
UPDATE v2 SET c = 30 WHERE a = 1; -- not allowed
2449+
ERROR: new row violates check option for view "v2"
2450+
DETAIL: Failing row contains (1, ONE, 30).
2451+
DELETE FROM v2 WHERE a = 2; -- ok
2452+
SELECT * FROM v2;
2453+
a | b | c
2454+
---+-----+----
2455+
1 | ONE | 10
2456+
(1 row)
2457+
2458+
DROP VIEW v2;
2459+
DROP VIEW v1;
2460+
DROP TABLE t2;
2461+
DROP TABLE t1;

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

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

0 commit comments

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