Skip to content

Commit 9b69d5c

Browse files
deanrasheeddeanrasheed
committed
Fix incorrect varlevelsup in security_barrier_replace_vars().
When converting an RTE with securityQuals into a security barrier subquery RTE, ensure that the Vars in the new subquery's targetlist all have varlevelsup = 0 so that they correctly refer to the underlying base relation being wrapped. The original code was creating new Vars by copying them from existing Vars referencing the base relation found elsewhere in the query, but failed to account for the fact that such Vars could come from sublink subqueries, and hence have varlevelsup > 0. In practice it looks like this could only happen with nested security barrier views, where the outer view has a WHERE clause containing a correlated subquery, due to the order in which the Vars are processed. Bug: #13988 Reported-by: Adam Guthrie Backpatch-to: 9.4, where updatable SB views were introduced
1 parent 80c925c commit 9b69d5c

File tree

3 files changed

+74
-0
lines changed

3 files changed

+74
-0
lines changed

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

+1
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

+39
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

+34
Original file line numberDiff line numberDiff line change
@@ -1055,3 +1055,37 @@ DROP VIEW vx1;
10551055
DROP TABLE tx1;
10561056
DROP TABLE tx2;
10571057
DROP TABLE tx3;
1058+
1059+
--
1060+
-- Test handling of vars from correlated subqueries in quals from outer
1061+
-- security barrier views, per bug #13988
1062+
--
1063+
CREATE TABLE t1 (a int, b text, c int);
1064+
INSERT INTO t1 VALUES (1, 'one', 10);
1065+
1066+
CREATE TABLE t2 (cc int);
1067+
INSERT INTO t2 VALUES (10), (20);
1068+
1069+
CREATE VIEW v1 WITH (security_barrier = true) AS
1070+
SELECT * FROM t1 WHERE (a > 0)
1071+
WITH CHECK OPTION;
1072+
1073+
CREATE VIEW v2 WITH (security_barrier = true) AS
1074+
SELECT * FROM v1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.cc = v1.c)
1075+
WITH CHECK OPTION;
1076+
1077+
INSERT INTO v2 VALUES (2, 'two', 20); -- ok
1078+
INSERT INTO v2 VALUES (-2, 'minus two', 20); -- not allowed
1079+
INSERT INTO v2 VALUES (3, 'three', 30); -- not allowed
1080+
1081+
UPDATE v2 SET b = 'ONE' WHERE a = 1; -- ok
1082+
UPDATE v2 SET a = -1 WHERE a = 1; -- not allowed
1083+
UPDATE v2 SET c = 30 WHERE a = 1; -- not allowed
1084+
1085+
DELETE FROM v2 WHERE a = 2; -- ok
1086+
SELECT * FROM v2;
1087+
1088+
DROP VIEW v2;
1089+
DROP VIEW v1;
1090+
DROP TABLE t2;
1091+
DROP TABLE t1;

0 commit comments

Comments
 (0)