diff options
| -rw-r--r-- | src/backend/optimizer/prep/prepsecurity.c | 1 | ||||
| -rw-r--r-- | src/test/regress/expected/updatable_views.out | 39 | ||||
| -rw-r--r-- | src/test/regress/sql/updatable_views.sql | 34 |
3 files changed, 74 insertions, 0 deletions
diff --git a/src/backend/optimizer/prep/prepsecurity.c b/src/backend/optimizer/prep/prepsecurity.c index c47b349fd40..0599ade8171 100644 --- a/src/backend/optimizer/prep/prepsecurity.c +++ b/src/backend/optimizer/prep/prepsecurity.c @@ -454,6 +454,7 @@ security_barrier_replace_vars_walker(Node *node, /* New variable for subquery targetlist */ newvar = copyObject(var); newvar->varno = newvar->varnoold = 1; + newvar->varlevelsup = 0; attno = list_length(context->targetlist) + 1; tle = makeTargetEntry((Expr *) newvar, diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index c9a5d30f1e7..f41386be1f9 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -2351,3 +2351,42 @@ DROP VIEW vx1; DROP TABLE tx1; DROP TABLE tx2; DROP TABLE tx3; +-- +-- Test handling of vars from correlated subqueries in quals from outer +-- security barrier views, per bug #13988 +-- +CREATE TABLE t1 (a int, b text, c int); +INSERT INTO t1 VALUES (1, 'one', 10); +CREATE TABLE t2 (cc int); +INSERT INTO t2 VALUES (10), (20); +CREATE VIEW v1 WITH (security_barrier = true) AS + SELECT * FROM t1 WHERE (a > 0) + WITH CHECK OPTION; +CREATE VIEW v2 WITH (security_barrier = true) AS + SELECT * FROM v1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.cc = v1.c) + WITH CHECK OPTION; +INSERT INTO v2 VALUES (2, 'two', 20); -- ok +INSERT INTO v2 VALUES (-2, 'minus two', 20); -- not allowed +ERROR: new row violates WITH CHECK OPTION for view "v1" +DETAIL: Failing row contains (-2, minus two, 20). +INSERT INTO v2 VALUES (3, 'three', 30); -- not allowed +ERROR: new row violates WITH CHECK OPTION for view "v2" +DETAIL: Failing row contains (3, three, 30). +UPDATE v2 SET b = 'ONE' WHERE a = 1; -- ok +UPDATE v2 SET a = -1 WHERE a = 1; -- not allowed +ERROR: new row violates WITH CHECK OPTION for view "v1" +DETAIL: Failing row contains (-1, ONE, 10). +UPDATE v2 SET c = 30 WHERE a = 1; -- not allowed +ERROR: new row violates WITH CHECK OPTION for view "v2" +DETAIL: Failing row contains (1, ONE, 30). +DELETE FROM v2 WHERE a = 2; -- ok +SELECT * FROM v2; + a | b | c +---+-----+---- + 1 | ONE | 10 +(1 row) + +DROP VIEW v2; +DROP VIEW v1; +DROP TABLE t2; +DROP TABLE t1; diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index 69d6b8e0ad0..78a3b0256a2 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -1055,3 +1055,37 @@ DROP VIEW vx1; DROP TABLE tx1; DROP TABLE tx2; DROP TABLE tx3; + +-- +-- Test handling of vars from correlated subqueries in quals from outer +-- security barrier views, per bug #13988 +-- +CREATE TABLE t1 (a int, b text, c int); +INSERT INTO t1 VALUES (1, 'one', 10); + +CREATE TABLE t2 (cc int); +INSERT INTO t2 VALUES (10), (20); + +CREATE VIEW v1 WITH (security_barrier = true) AS + SELECT * FROM t1 WHERE (a > 0) + WITH CHECK OPTION; + +CREATE VIEW v2 WITH (security_barrier = true) AS + SELECT * FROM v1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.cc = v1.c) + WITH CHECK OPTION; + +INSERT INTO v2 VALUES (2, 'two', 20); -- ok +INSERT INTO v2 VALUES (-2, 'minus two', 20); -- not allowed +INSERT INTO v2 VALUES (3, 'three', 30); -- not allowed + +UPDATE v2 SET b = 'ONE' WHERE a = 1; -- ok +UPDATE v2 SET a = -1 WHERE a = 1; -- not allowed +UPDATE v2 SET c = 30 WHERE a = 1; -- not allowed + +DELETE FROM v2 WHERE a = 2; -- ok +SELECT * FROM v2; + +DROP VIEW v2; +DROP VIEW v1; +DROP TABLE t2; +DROP TABLE t1; |