Skip to content

Commit d7813de

Browse files
vbwagnervbwagner
committed
Merge branch 'REL9_5_STABLE' into PGPRO9_5
2 parents 8df054d + f1dfcf6 commit d7813de

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
DROP TABLE tx1;
10651065
DROP TABLE tx2;
10661066
DROP TABLE tx3;
1067+
1068+
--
1069+
-- Test handling of vars from correlated subqueries in quals from outer
1070+
-- security barrier views, per bug #13988
1071+
--
1072+
CREATE TABLE t1 (a int, b text, c int);
1073+
INSERT INTO t1 VALUES (1, 'one', 10);
1074+
1075+
CREATE TABLE t2 (cc int);
1076+
INSERT INTO t2 VALUES (10), (20);
1077+
1078+
CREATE VIEW v1 WITH (security_barrier = true) AS
1079+
SELECT * FROM t1 WHERE (a > 0)
1080+
WITH CHECK OPTION;
1081+
1082+
CREATE VIEW v2 WITH (security_barrier = true) AS
1083+
SELECT * FROM v1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.cc = v1.c)
1084+
WITH CHECK OPTION;
1085+
1086+
INSERT INTO v2 VALUES (2, 'two', 20); -- ok
1087+
INSERT INTO v2 VALUES (-2, 'minus two', 20); -- not allowed
1088+
INSERT INTO v2 VALUES (3, 'three', 30); -- not allowed
1089+
1090+
UPDATE v2 SET b = 'ONE' WHERE a = 1; -- ok
1091+
UPDATE v2 SET a = -1 WHERE a = 1; -- not allowed
1092+
UPDATE v2 SET c = 30 WHERE a = 1; -- not allowed
1093+
1094+
DELETE FROM v2 WHERE a = 2; -- ok
1095+
SELECT * FROM v2;
1096+
1097+
DROP VIEW v2;
1098+
DROP VIEW v1;
1099+
DROP TABLE t2;
1100+
DROP TABLE t1;

0 commit comments

Comments
 (0)