Making security barrier views automatically updatable

This pages describes an attempt to make security barrier views automatically updatable. See also Automatically updatable security barrier views which describes a different approach and some of the fundamental problems that make this difficult.
* Original proposal with most of the work done in the rewriter.
* Refined patch with the security barrier expansion code moved to the planner to solve issues with inheritance.
* Further update with fixes for rules
More up to date information will appear on the commitfest entry for this patch.
== How it works ==
The patch has 2 main components:
# A new field securityQuals on the RangeTblEntry structure.This is a list, allowing the quals from nested security barrier views to be kept separate from one another and from the user-supplied quals in the query's jointree. This field is populated in rewriteTargetView(), if the target view has the security barrier option, instead of merging the view's quals with any user-supplied quals. Ultimately these quals will be used to build security barrier subqueries to fetch the rows to update, but they are kept separate initially so that the remaining rewriter code, and the prep stages of the planner don't have to deal with a subquery result relation.
# New code in the preprocessing stage of the planner to handle any RTEs with securityQuals.Such RTEs are turned into (possibly nested) subquery RTEs with the security_barrier property set to prevent push-down of leaky user-supplied quals.
The first part is fairly straightforward. The new code in the planner, however, has to happen at just the right time, in order to work properly with inheritance and targetlist expansion. The following is an outline of the path through the planner, showing where the new code slots in (changes to existing code in bold):
* standard_planner():
** subquery_planner():
*** Process CTEs.
*** Pull up sublinks [ANY/EXISTS].
*** Inline set-returning functions.
*** Pull up subqueries.NOTE: This excludes subqueries with the security_barrier property.
*** Flatten a simple UNION ALL query into one with an append_rel_list.
*** Preprocess RowMarks. This includes adding rowmarks for all non-target relations not otherwise locked.This code treats RTEs with securityQuals as subqueries, because such RTEs will become subquery RTEs later.
*** Expand inherited tables, producing entries in the append_rel_list.
*** Preprocess expressions in the targetlist, WITH CHECK OPTION clauses, RETURNING list, WHERE quals, HAVING clause, window clauses, LIMIT and OFFSET clauses, the append_rel_list and functions and VALUES in the rangetable. This preprocessing includes recursive planning of any sublink subqueries contained in any of those expressions (recursive invocation of subquery_planner()). Thus all SubLink nodes in those parts of the Query are turned into SubPlan nodes.NOTE: This preprocessing excludes RTE subqueries in the rangetable, since those are processed by later recursion. For the same reason, this preprocessing also excludes RTE securityQuals.
*** Maybe turn HAVING into a WHERE clause.
*** Reduce outer joins where possible.
*** If the result relation has no inheritance, jump straight into grouping_planner().
*** Else inheritance_planner():
**** For each inheritance child:
***** Copy the whole query and use adjust_appendrel_attrs() to fix up any Vars to refer to the current child relation.NOTE: adjust_appendrel_attrs() skips subqueries in the rangetable, since they cannot contain references to the result relation. This would be broken if LATERAL references to the result relation were allowed.adjust_appendrel_attrs() does descend into RTE securityQuals, because they may contain up-references to the result relation. They may also contain sublink subqueries, so new code is needed to make adjust_appendrel_attrs() able to handle this (previously this was not possible, since all other SubLinks have been turned into SubPlans during expression preprocessing).
***** Careful rangetable mangling so that each copy of the query can share the same rangetable.
***** Plan the child query using grouping_planner():
****** Plan set operations using recursion.
****** Preprocess the targetlist. In the case of an UPDATE, this will add targetlist entries for each of the result relation's attributes. By this point, the result relation may be an inheritance child of the original result relation, so it may have additional attributes.
****** Expand any RTE securityQuals. This turns any RTEs with securityQuals into subquery RTEs with the security_barrier property. The targetlists of the new subqueries are built using the (now fully known) list of attributes of the result relation that the outer query refers to. If the RTE in question is the result relation, a copy of it is made and left unexpanded, to act as the new result relation. The expanded subquery RTE acts as the source of rows to update.
****** Find the best paths using query_planner():
******* ...
******* make_one_rel():
******** set_base_rel_size():
********* set_rel_size():
********** For subquery RTEs, set_subquery_pathlist():
*********** Push down any quals into the subquery where it is safe to do so.If the subquery has the security_barrier property, exclude quals that contain references to leaky functions. Why not examine the function arguments ???
*********** Recursively invoke subquery_planner() to plan the RTE's subquery.
=== The problem with LATERAL ===
In 9.3.2, and 9.4devel prior to commit 158b7fa6a34006bdc70b515e14e120d3e896589b, it was possible to write an UPDATE with a FROM clause containing LATERAL references to the result relation. The problem with that in the current code is illustrated above — if the result relation is the parent of an inheritance set, then it falls over because adjust_appendrel_attrs() does not descend into subqueries in the rangetable, and so those LATERAL references are not rewritten in the inheritance planner to refer to the correct append_rel child entry. This is illustrated by the following example:
 CREATE TABLE t1(x int);
 CREATE TABLE t2() INHERITS(t1);
 CREATE TABLE t3(a int, b int);
 UPDATE t1 SET x=b FROM LATERAL (SELECT * FROM t3 WHERE a=x OFFSET 0) t3;
which, if it's allowed, produces
 ERROR: no relation entry for relid 1
If adjust_appendrel_attrs() were to descend into subqueries in the rangetable to fixup such references, then it would have to be modified to handle any SubLinks that it might encounter there (just as this patch does), since subqueries in the rangetable are not planned in the initial preprocessing.
== Test cases ==
These tests make use of the following function which can snoop on values hidden behind a view's quals, if the view doesn't have the security_barrier property:
 CREATE OR REPLACE FUNCTION snoop(anyelement)
 RETURNS boolean AS
 $$
 BEGIN
 RAISE NOTICE 'Snooped value: %', $1;
 RETURN true;
 END;
 $$
 LANGUAGE plpgsql STRICT IMMUTABLE COST 0.000001;
and the following LEAKPROOF function, which doesn't reveal anything about the data passed to it, and which should therefore be safe to push down into security barrier views:
 CREATE OR REPLACE FUNCTION leakproof(anyelement)
 RETURNS boolean AS
 $$
 BEGIN
 RETURN true;
 END;
 $$
 LANGUAGE plpgsql STRICT IMMUTABLE LEAKPROOF;
=== Confirm non-security barrier views leak ===
Without the security_barrier property, the low cost of the snoop() function causes it to be executed first, leaking information that the view is supposed to hide:
 CREATE TABLE t1(person text, visibility text);
 INSERT INTO t1 VALUES ('Tom', 'public'),
 ('Dick', 'private'),
 ('Harry', 'public');
 
 CREATE VIEW v1 AS SELECT person FROM t1 WHERE visibility = 'public';
 
 EXPLAIN (VERBOSE, COSTS OFF)
 UPDATE v1 SET person=person WHERE NOT snoop(person);
 UPDATE v1 SET person=person WHERE NOT snoop(person);
 
 EXPLAIN (VERBOSE, COSTS OFF)
 DELETE FROM v1 WHERE NOT snoop(person);
 DELETE FROM v1 WHERE NOT snoop(person);
 
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT * FROM v1 WHERE snoop(person);
 SELECT * FROM v1 WHERE snoop(person);
 
 DROP TABLE t1 CASCADE;
Results:
 QUERY PLAN
 -------------------------------------------------------------------------------
 Update on public.t1
 -> Seq Scan on public.t1
 Output: t1.person, t1.visibility, t1.ctid
 Filter: ((NOT snoop(t1.person)) AND (t1.visibility = 'public'::text))
 (4 rows)
 
 NOTICE: Snooped value: Tom
 NOTICE: Snooped value: Dick
 NOTICE: Snooped value: Harry
 UPDATE 0
 
 QUERY PLAN
 -------------------------------------------------------------------------------
 Delete on public.t1
 -> Seq Scan on public.t1
 Output: t1.ctid
 Filter: ((NOT snoop(t1.person)) AND (t1.visibility = 'public'::text))
 (4 rows)
 
 NOTICE: Snooped value: Tom
 NOTICE: Snooped value: Dick
 NOTICE: Snooped value: Harry
 DELETE 0
 
 QUERY PLAN
 -------------------------------------------------------------------
 Seq Scan on public.t1
 Output: t1.person
 Filter: (snoop(t1.person) AND (t1.visibility = 'public'::text))
 (3 rows)
 
 NOTICE: Snooped value: Tom
 NOTICE: Snooped value: Dick
 NOTICE: Snooped value: Harry
 person
 --------
 Tom
 Harry
 (2 rows)
So a person with access to the view can see the person 'Dick' in the underlying table, even though he is not exposed through the view.
=== Confirm security barrier views don't leak ===
Setting the security_barrier option on the view should result in plans with subquery scans, and quals containing leaky functions should not be pushed down, but leakproof functions may be:
 CREATE TABLE t1(person text, visibility text);
 INSERT INTO t1 VALUES ('Tom', 'public'),
 ('Dick', 'private'),
 ('Harry', 'public');
 
 CREATE VIEW v1 WITH (security_barrier=true) AS
 SELECT person FROM t1 WHERE visibility = 'public';
 
 EXPLAIN (VERBOSE, COSTS OFF)
 UPDATE v1 SET person=person
 WHERE NOT snoop(person) AND leakproof(person);
 UPDATE v1 SET person=person
 WHERE NOT snoop(person) AND leakproof(person);
 
 EXPLAIN (VERBOSE, COSTS OFF)
 DELETE FROM v1 WHERE NOT snoop(person) AND leakproof(person);
 DELETE FROM v1 WHERE NOT snoop(person) AND leakproof(person);
 
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT * FROM v1 WHERE snoop(person) AND leakproof(person);
 SELECT * FROM v1 WHERE snoop(person) AND leakproof(person);
 
 DROP TABLE t1 CASCADE;
Results:
 QUERY PLAN 
 -------------------------------------------------------------------------------------------------------
 Update on public.t1 t1_1
 -> Subquery Scan on t1
 Output: t1.person, t1.visibility, t1.ctid
 Filter: (NOT snoop(t1.person))
 -> Seq Scan on public.t1 t1_2
 Output: t1_2.person, t1_2.ctid, t1_2.visibility
 Filter: ((t1_2.visibility = 'public'::text) AND leakproof(t1_2.person))
 (7 rows)
 
 NOTICE: Snooped value: Tom
 NOTICE: Snooped value: Harry
 UPDATE 0
 
 QUERY PLAN 
 -------------------------------------------------------------------------------------------------------
 Delete on public.t1 t1_1
 -> Subquery Scan on t1
 Output: t1.ctid
 Filter: (NOT snoop(t1.person))
 -> Seq Scan on public.t1 t1_2
 Output: t1_2.ctid, t1_2.person
 Filter: ((t1_2.visibility = 'public'::text) AND leakproof(t1_2.person))
 (7 rows)
 
 NOTICE: Snooped value: Tom
 NOTICE: Snooped value: Harry
 DELETE 0
 
 QUERY PLAN 
 -------------------------------------------------------------------------------------------
 Subquery Scan on v1
 Output: v1.person
 Filter: snoop(v1.person)
 -> Seq Scan on public.t1
 Output: t1.person
 Filter: ((t1.visibility = 'public'::text) AND leakproof(t1.person))
 (6 rows)
 
 NOTICE: Snooped value: Tom
 NOTICE: Snooped value: Harry
 person
 --------
 Tom
 Harry
 (2 rows)
So no information is leaked, since the snoop function isn't pushed down into the subquery, but the leakproof function is.
=== Nested security barrier views ===
Nothing stops a user defining their own security barrier views and putting malicious functions in them, so the quals from each view must be kept separate and only merged when it is safe to do so:
 CREATE TABLE t1(a int, b text, c text);
 INSERT INTO t1 VALUES (-1, 'Private', 'PRIVATE'), (1, 'Public', 'PUBLIC');
 
 CREATE VIEW v1 WITH (security_barrier=true) AS
 SELECT * FROM t1 WHERE a > 0;
 
 CREATE VIEW v2 AS
 SELECT * FROM v1 WHERE snoop(b) and leakproof(b);
 
 CREATE VIEW v3 WITH (security_barrier=true) AS
 SELECT * FROM v1 WHERE snoop(b) and leakproof(b);
 
 EXPLAIN (VERBOSE, COSTS OFF)
 UPDATE v2 SET a=a WHERE NOT snoop(c) AND leakproof(c);
 UPDATE v2 SET a=a WHERE NOT snoop(c) AND leakproof(c);
 
 EXPLAIN (VERBOSE, COSTS OFF)
 DELETE FROM v2 WHERE NOT snoop(c) AND leakproof(c);
 DELETE FROM v2 WHERE NOT snoop(c) AND leakproof(c);
 
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT * FROM v2 WHERE snoop(c) AND leakproof(c);
 SELECT * FROM v2 WHERE snoop(c) AND leakproof(c);
 
 EXPLAIN (VERBOSE, COSTS OFF)
 UPDATE v3 SET a=a WHERE NOT snoop(c) AND leakproof(c);
 UPDATE v3 SET a=a WHERE NOT snoop(c) AND leakproof(c);
 
 EXPLAIN (VERBOSE, COSTS OFF)
 DELETE FROM v3 WHERE NOT snoop(c) AND leakproof(c);
 DELETE FROM v3 WHERE NOT snoop(c) AND leakproof(c);
 
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT * FROM v3 WHERE snoop(c) AND leakproof(c);
 SELECT * FROM v3 WHERE snoop(c) AND leakproof(c);
 
 DROP TABLE t1 CASCADE;
Results:
 QUERY PLAN 
 --------------------------------------------------------------------------------------------------------
 Update on public.t1 t1_1
 -> Subquery Scan on t1
 Output: t1.a, t1.b, t1.c, t1.ctid
 Filter: ((NOT snoop(t1.c)) AND snoop(t1.b))
 -> Seq Scan on public.t1 t1_2
 Output: t1_2.a, t1_2.ctid, t1_2.c, t1_2.b
 Filter: ((t1_2.a > 0) AND leakproof(t1_2.c) AND leakproof(t1_2.b))
 (7 rows)
 
 NOTICE: Snooped value: PUBLIC
 UPDATE 0
 
 QUERY PLAN 
 --------------------------------------------------------------------------------------------------------
 Delete on public.t1 t1_1
 -> Subquery Scan on t1
 Output: t1.ctid
 Filter: ((NOT snoop(t1.c)) AND snoop(t1.b))
 -> Seq Scan on public.t1 t1_2
 Output: t1_2.ctid, t1_2.c, t1_2.b
 Filter: ((t1_2.a > 0) AND leakproof(t1_2.c) AND leakproof(t1_2.b))
 (7 rows)
 
 NOTICE: Snooped value: PUBLIC
 DELETE 0
 
 QUERY PLAN 
 ----------------------------------------------------------------------------------------
 Subquery Scan on v1
 Output: v1.a, v1.b, v1.c
 Filter: (snoop(v1.b) AND snoop(v1.c))
 -> Seq Scan on public.t1
 Output: t1.a, t1.b, t1.c
 Filter: ((t1.a > 0) AND leakproof(t1.b) AND leakproof(t1.c))
 (6 rows)
 
 NOTICE: Snooped value: Public
 NOTICE: Snooped value: PUBLIC
 a | b | c
 ---+--------+--------
 1 | Public | PUBLIC
 (1 row)
 
 QUERY PLAN 
 --------------------------------------------------------------------------------------------------------------
 Update on public.t1 t1_1
 -> Subquery Scan on t1
 Output: t1.a, t1.b, t1.c, t1.ctid
 Filter: (NOT snoop(t1.c))
 -> Subquery Scan on t1_2
 Output: t1_2.a, t1_2.ctid, t1_2.c, t1_2.b
 Filter: snoop(t1_2.b)
 -> Seq Scan on public.t1 t1_3
 Output: t1_3.a, t1_3.ctid, t1_3.c, t1_3.b
 Filter: ((t1_3.a > 0) AND leakproof(t1_3.b) AND leakproof(t1_3.c))
 (10 rows)
 
 NOTICE: Snooped value: Public
 NOTICE: Snooped value: PUBLIC
 UPDATE 0
 
 QUERY PLAN 
 --------------------------------------------------------------------------------------------------------------
 Delete on public.t1 t1_1
 -> Subquery Scan on t1
 Output: t1.ctid
 Filter: (NOT snoop(t1.c))
 -> Subquery Scan on t1_2
 Output: t1_2.ctid, t1_2.c, t1_2.b
 Filter: snoop(t1_2.b)
 -> Seq Scan on public.t1 t1_3
 Output: t1_3.ctid, t1_3.c, t1_3.b
 Filter: ((t1_3.a > 0) AND leakproof(t1_3.b) AND leakproof(t1_3.c))
 (10 rows)
 
 NOTICE: Snooped value: Public
 NOTICE: Snooped value: PUBLIC
 DELETE 0
 
 QUERY PLAN 
 ----------------------------------------------------------------------------------------------
 Subquery Scan on v3
 Output: v3.a, v3.b, v3.c
 Filter: snoop(v3.c)
 -> Subquery Scan on v1
 Output: v1.a, v1.b, v1.c
 Filter: snoop(v1.b)
 -> Seq Scan on public.t1
 Output: t1.a, t1.b, t1.c
 Filter: ((t1.a > 0) AND leakproof(t1.b) AND leakproof(t1.c))
 (9 rows)
 
 NOTICE: Snooped value: Public
 NOTICE: Snooped value: PUBLIC
 a | b | c
 ---+--------+--------
 1 | Public | PUBLIC
 (1 row)
So the unsafe top-level quals are kept separate from the outer view's quals, and the unsafe quals from the outer view are kept separate from the inner view's quals, but all safe quals are pushed all the way down to give the most efficient plan.
=== Security barrier view WITH CHECK OPTION ===
 CREATE TABLE t1(a int, b text);
 INSERT INTO t1 VALUES (-1, 'Private'), (1, 'Public');
 
 CREATE VIEW v1 WITH (security_barrier=true) AS
 SELECT * FROM t1 WHERE a > 0
 WITH CHECK OPTION;
 
 -- Should succeed 
 INSERT INTO v1 VALUES (2, 'Public 2');
 UPDATE v1 SET a=20 WHERE a=2;
 
 -- Should fail
 INSERT INTO v1 VALUES (-2, 'Private 2');
 UPDATE v1 SET a=-10 WHERE a=1;
 
 DROP TABLE t1 CASCADE;
Results:
 -- Should succeed
 INSERT 0 1
 UPDATE 1
 
 -- Should fail
 ERROR: new row violates WITH CHECK OPTION for view "v1"
 DETAIL: Failing row contains (-2, Private 2).
 ERROR: new row violates WITH CHECK OPTION for view "v1"
 DETAIL: Failing row contains (-10, Public).
=== Security barrier view WITH CHECK OPTION and BEFORE trigger on base table ===
The WITH CHECK OPTION should apply to NEW rows (after the action of the before trigger):
 CREATE TABLE t1(a int, b text);
 
 CREATE FUNCTION t1_before_trig_fn()
 RETURNS trigger AS
 $$
 BEGIN
 NEW.a := -NEW.a;
 NEW.b := NEW.b || ' (modified by trigger)';
 RETURN NEW;
 END;
 $$
 LANGUAGE plpgsql;
 
 CREATE TRIGGER t1_before_trig BEFORE INSERT OR UPDATE ON t1
 FOR EACH ROW EXECUTE PROCEDURE t1_before_trig_fn();
 
 CREATE VIEW v1 WITH (security_barrier=true) AS
 SELECT * FROM t1 WHERE a > 0
 WITH CHECK OPTION;
 
 -- Should succeed
 INSERT INTO v1 VALUES (-1, 'Test row');
 SELECT * FROM t1;
 UPDATE v1 SET a=-2 WHERE a=1;
 SELECT * FROM t1;
 
 -- Should fail
 INSERT INTO v1 VALUES (2, 'Failing row');
 UPDATE v1 SET a=3, b='Failing row' WHERE a=2;
 
 DROP TABLE t1 CASCADE;
 DROP FUNCTION t1_before_trig_fn();
Results:
 -- Should succeed
 INSERT 0 1
 
 a | b
 ---+--------------------------------
 1 | Test row (modified by trigger)
 (1 row)
 UPDATE 1
 
 a | b
 ---+------------------------------------------------------
 2 | Test row (modified by trigger) (modified by trigger)
 (1 row)
 
 -- Should fail
 ERROR: new row violates WITH CHECK OPTION for view "v1"
 DETAIL: Failing row contains (-2, Failing row (modified by trigger)).
 
 ERROR: new row violates WITH CHECK OPTION for view "v1"
 DETAIL: Failing row contains (-3, Failing row (modified by trigger)).
=== Security barrier view with ORDER BY and non-updatable columns ===
 CREATE TABLE t1 (a int, b int);
 INSERT INTO t1 VALUES (1,2), (4,5), (3,-3);
 
 CREATE VIEW v1 WITH (security_barrier=true) AS
 SELECT a, b, a-b AS c FROM t1 WHERE a*a+b*b != 0 ORDER BY a+b;
 
 SELECT * FROM v1;
 
 EXPLAIN (VERBOSE, COSTS OFF)
 INSERT INTO v1(a, b) VALUES (7,-8);
 INSERT INTO v1(a, b) VALUES (7,-8);
 SELECT * FROM v1;
 
 EXPLAIN (VERBOSE, COSTS OFF)
 UPDATE v1 SET b=b+1 WHERE snoop(a) AND leakproof(a) RETURNING *;
 UPDATE v1 SET b=b+1 WHERE snoop(a) AND leakproof(a) RETURNING *;
 
 DROP TABLE t1 CASCADE;
Results:
 a | b | c
 ---+----+----
 3 | -3 | 6
 1 | 2 | -1
 4 | 5 | -1
 (3 rows)
 
 QUERY PLAN
 -------------------------
 Insert on public.t1
 -> Result
 Output: 7, (-8)
 (3 rows)
 
 INSERT 0 1
 
 a | b | c
 ---+----+----
 7 | -8 | 15
 3 | -3 | 6
 1 | 2 | -1
 4 | 5 | -1
 (4 rows)
 
 QUERY PLAN 
 ---------------------------------------------------------------------------------------------------------
 Update on public.t1 t1_1
 Output: t1_1.a, t1_1.b, (t1_1.a - t1_1.b)
 -> Subquery Scan on t1
 Output: t1.a, (t1.b + 1), t1.ctid
 Filter: snoop(t1.a)
 -> Seq Scan on public.t1 t1_2
 Output: t1_2.b, t1_2.ctid, t1_2.a
 Filter: ((((t1_2.a * t1_2.a) + (t1_2.b * t1_2.b)) <> 0) AND leakproof(t1_2.a))
 (8 rows)
 
 NOTICE: Snooped value: 1
 NOTICE: Snooped value: 4
 NOTICE: Snooped value: 3
 NOTICE: Snooped value: 7
 a | b | c
 ---+----+----
 1 | 3 | -2
 4 | 6 | -2
 3 | -2 | 5
 7 | -7 | 14
 (4 rows)
 
 UPDATE 4
=== Security barrier view with subqueries ===
 CREATE TABLE t1 (a int);
 INSERT INTO t1 VALUES (1), (2), (2);
 
 CREATE TABLE t2 (a int);
 INSERT INTO t2 SELECT * FROM generate_series(1, 5);
 
 CREATE VIEW v1 WITH (security_barrier=true) AS
 SELECT a, (SELECT count(*) FROM t1 WHERE t1.a = t2.a) AS c
 FROM t2
 WHERE EXISTS(SELECT 1 FROM t1 WHERE t1.a = t2.a);
 
 SELECT * FROM v1;
 
 EXPLAIN (VERBOSE, COSTS OFF) 
 UPDATE v1 SET a=a+1
 WHERE snoop(a) AND snoop(c) AND leakproof(a)
 RETURNING *;
 
 UPDATE v1 SET a=a+1
 WHERE snoop(a) AND snoop(c) AND leakproof(a)
 RETURNING *;
 
 DROP TABLE t1, t2 CASCADE;
Results:
 a | c
 ---+---
 1 | 1
 2 | 2
 (2 rows)
 
 QUERY PLAN
 ----------------------------------------------------------------------
 Update on public.t2 t2_1
 Output: t2_1.a, (SubPlan 1)
 -> Subquery Scan on t2
 Output: (t2.a + 1), t2.ctid
 Filter: (snoop(t2.a) AND snoop((SubPlan 2)))
 -> Hash Join
 Output: t2_2.a, t2_2.ctid
 Hash Cond: (t2_2.a = t1.a)
 -> Seq Scan on public.t2 t2_2
 Output: t2_2.a, t2_2.ctid
 Filter: leakproof(t2_2.a)
 -> Hash
 Output: t1.a
 -> HashAggregate
 Output: t1.a
 Group Key: t1.a
 -> Seq Scan on public.t1
 Output: t1.a
 SubPlan 2
 -> Aggregate
 Output: count(*)
 -> Seq Scan on public.t1 t1_2
 Output: t1_2.a
 Filter: (t1_2.a = t2.a)
 SubPlan 1
 -> Aggregate
 Output: count(*)
 -> Seq Scan on public.t1 t1_1
 Output: t1_1.a
 Filter: (t1_1.a = t2_1.a)
 (30 rows)
 
 NOTICE: Snooped value: 1
 NOTICE: Snooped value: 1
 NOTICE: Snooped value: 2
 NOTICE: Snooped value: 2
 a | c
 ---+---
 2 | 2
 3 | 0
 (2 rows)
 
 UPDATE 2
=== Security barrier view based on an inheritance set ===
 CREATE TABLE t1 (a int, b float, c text);
 CREATE INDEX t1_a_idx ON t1(a);
 INSERT INTO t1
 SELECT i,i,'t1' FROM generate_series(1,1000) g(i);
 ANALYSE t1;
 
 CREATE TABLE t11 (d text) INHERITS (t1);
 CREATE INDEX t11_a_idx ON t11(a);
 INSERT INTO t11
 SELECT i,i,'t11','t11d' FROM generate_series(1,1000) g(i);
 ANALYSE t11;
 
 CREATE TABLE t12 (e int[]) INHERITS (t1);
 CREATE INDEX t12_a_idx ON t12(a);
 INSERT INTO t12
 SELECT i,i,'t12','{1,2}'::int[] FROM generate_series(1,1000) g(i);
 ANALYSE t12;
 
 CREATE TABLE t111 () INHERITS (t11, t12);
 CREATE INDEX t111_a_idx ON t111(a);
 INSERT INTO t111
 SELECT i,i,'t111','t111d','{1,1,1}'::int[] FROM generate_series(1,1000) g(i);
 ANALYSE t111;
 
 CREATE VIEW v1 WITH (security_barrier=true) AS
 SELECT *, (SELECT d FROM t11 WHERE t11.a = t1.a LIMIT 1) AS d
 FROM t1
 WHERE a > 0 AND EXISTS(SELECT 1 FROM t12 WHERE t12.a = t1.a);
 
 SELECT * FROM v1 WHERE a=123;
 
 EXPLAIN (VERBOSE, COSTS OFF)
 UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a=123;
 UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a=123;
 
 SELECT * FROM v1 WHERE b=123;
 
 DROP TABLE t1, t11, t12, t111 CASCADE;
Results:
 a | b | c | d
 -----+-----+------+------
 123 | 123 | t1 | t11d
 123 | 123 | t11 | t11d
 123 | 123 | t12 | t11d
 123 | 123 | t111 | t11d
 (4 rows)
 
 QUERY PLAN 
 -------------------------------------------------------------------------------------------
 Update on public.t1 t1_4
 -> Subquery Scan on t1
 Output: (t1.a + 1), t1.b, t1.c, t1.ctid
 Filter: snoop(t1.a)
 -> Nested Loop Semi Join
 Output: t1_5.a, t1_5.ctid, t1_5.b, t1_5.c
 -> Index Scan using t1_a_idx on public.t1 t1_5
 Output: t1_5.a, t1_5.ctid, t1_5.b, t1_5.c
 Index Cond: ((t1_5.a > 0) AND (t1_5.a = 123))
 Filter: leakproof(t1_5.a)
 -> Append
 -> Index Only Scan using t12_a_idx on public.t12
 Output: t12.a
 Index Cond: (t12.a = t1_5.a)
 -> Index Only Scan using t111_a_idx on public.t111
 Output: t111.a
 Index Cond: (t111.a = t1_5.a)
 -> Subquery Scan on t1_1
 Output: (t1_1.a + 1), t1_1.b, t1_1.c, t1_1.d, t1_1.ctid
 Filter: snoop(t1_1.a)
 -> Nested Loop Semi Join
 Output: t11.a, t11.ctid, t11.b, t11.c, t11.d
 -> Index Scan using t11_a_idx on public.t11
 Output: t11.a, t11.ctid, t11.b, t11.c, t11.d
 Index Cond: ((t11.a > 0) AND (t11.a = 123))
 Filter: leakproof(t11.a)
 -> Append
 -> Index Only Scan using t12_a_idx on public.t12 t12_1
 Output: t12_1.a
 Index Cond: (t12_1.a = t11.a)
 -> Index Only Scan using t111_a_idx on public.t111 t111_1
 Output: t111_1.a
 Index Cond: (t111_1.a = t11.a)
 -> Subquery Scan on t1_2
 Output: (t1_2.a + 1), t1_2.b, t1_2.c, t1_2.e, t1_2.ctid
 Filter: snoop(t1_2.a)
 -> Nested Loop Semi Join
 Output: t12_2.a, t12_2.ctid, t12_2.b, t12_2.c, t12_2.e
 -> Index Scan using t12_a_idx on public.t12 t12_2
 Output: t12_2.a, t12_2.ctid, t12_2.b, t12_2.c, t12_2.e
 Index Cond: ((t12_2.a > 0) AND (t12_2.a = 123))
 Filter: leakproof(t12_2.a)
 -> Append
 -> Index Only Scan using t12_a_idx on public.t12 t12_3
 Output: t12_3.a
 Index Cond: (t12_3.a = t12_2.a)
 -> Index Only Scan using t111_a_idx on public.t111 t111_2
 Output: t111_2.a
 Index Cond: (t111_2.a = t12_2.a)
 -> Subquery Scan on t1_3
 Output: (t1_3.a + 1), t1_3.b, t1_3.c, t1_3.d, t1_3.e, t1_3.ctid
 Filter: snoop(t1_3.a)
 -> Nested Loop Semi Join
 Output: t111_3.a, t111_3.ctid, t111_3.b, t111_3.c, t111_3.d, t111_3.e
 -> Index Scan using t111_a_idx on public.t111 t111_3
 Output: t111_3.a, t111_3.ctid, t111_3.b, t111_3.c, t111_3.d, t111_3.e
 Index Cond: ((t111_3.a > 0) AND (t111_3.a = 123))
 Filter: leakproof(t111_3.a)
 -> Append
 -> Index Only Scan using t12_a_idx on public.t12 t12_4
 Output: t12_4.a
 Index Cond: (t12_4.a = t111_3.a)
 -> Index Only Scan using t111_a_idx on public.t111 t111_4
 Output: t111_4.a
 Index Cond: (t111_4.a = t111_3.a)
 (65 rows)
 
 NOTICE: Snooped value: 123
 NOTICE: Snooped value: 123
 NOTICE: Snooped value: 123
 NOTICE: Snooped value: 123
 UPDATE 4
 
 a | b | c | d
 -----+-----+------+------
 124 | 123 | t1 | t11d
 124 | 123 | t11 | t11d
 124 | 123 | t12 | t11d
 124 | 123 | t111 | t11d
 (4 rows)
=== Update on security barrier view joined to subquery ===
 CREATE TABLE t1 (a int, b int);
 INSERT INTO t1 VALUES (1, 10);
 ANALYSE t1;
 
 CREATE TABLE t2 () INHERITS (t1);
 INSERT INTO t2 VALUES (2, 20);
 ANALYSE t2;
 
 CREATE TABLE t3 (x int, y int);
 INSERT INTO t3 VALUES (1,100);
 ANALYSE t3;
 
 CREATE TABLE t4 (x int, y int);
 INSERT INTO t4 VALUES (2,200);
 ANALYSE t4;
 
 CREATE VIEW v1 WITH (security_barrier=true) AS
 SELECT * FROM t1 WHERE a > 0;
 
 EXPLAIN (VERBOSE, COSTS OFF)
 UPDATE v1 SET b = y
 FROM (SELECT * FROM t3 UNION ALL SELECT * FROM t4) s WHERE a = x;
 UPDATE v1 SET b = y
 FROM (SELECT * FROM t3 UNION ALL SELECT * FROM t4) s WHERE a = x;
 
 SELECT * FROM t1;
 
 DROP TABLE t1, t2, t3, t4 CASCADE;
Results:
 QUERY PLAN 
 ------------------------------------------------------------
 Update on public.t1
 -> Nested Loop
 Output: t1_1.a, t3.y, t1_1.ctid, (ROW(t3.x, t3.y))
 Join Filter: (t1_1.a = t3.x)
 -> Seq Scan on public.t1 t1_1
 Output: t1_1.ctid, t1_1.a
 Filter: (t1_1.a > 0)
 -> Append
 -> Seq Scan on public.t3
 Output: t3.y, ROW(t3.x, t3.y), t3.x
 -> Seq Scan on public.t4
 Output: t4.y, ROW(t4.x, t4.y), t4.x
 -> Nested Loop
 Output: t2.a, t3.y, t2.ctid, (ROW(t3.x, t3.y))
 Join Filter: (t2.a = t3.x)
 -> Seq Scan on public.t2
 Output: t2.ctid, t2.a
 Filter: (t2.a > 0)
 -> Append
 -> Seq Scan on public.t3
 Output: t3.y, ROW(t3.x, t3.y), t3.x
 -> Seq Scan on public.t4
 Output: t4.y, ROW(t4.x, t4.y), t4.x
 (23 rows)
 
 UPDATE 2
 
 a | b
 ---+-----
 1 | 100
 2 | 200
 (2 rows)
=== Update on security barrier view with LATERAL join to subquery ===
Test removed --- LATERAL references to the result relation are no longer allowed.
Original test
=== Update on security barrier view with LATERAL join to JOIN subquery ===
Test removed --- LATERAL references to the result relation are no longer allowed.
Original test
=== Security barrier views with subqueries based on other security barrier views ===
 CREATE TABLE t1 (a int, b int, c int);
 CREATE INDEX t1_a_idx ON t1(a);
 INSERT INTO t1 SELECT i, i, i FROM generate_series(1,1000) g(i);
 ANALYSE t1;
 
 CREATE TABLE t2 () INHERITS (t1);
 CREATE INDEX t2_a_idx ON t2(a);
 INSERT INTO t2 SELECT i, i, i FROM generate_series(1001,2000) g(i);
 ANALYSE t2;
 
 CREATE VIEW v1 WITH (security_barrier=true) AS
 SELECT * FROM t1 WHERE a%2 = 0;
 
 CREATE VIEW v2 WITH (security_barrier=true) AS
 SELECT * FROM v1
 WHERE EXISTS(SELECT 1 FROM t1 WHERE t1.a = v1.a)
 AND a%3 = 0 AND snoop('b='||b);
 
 CREATE VIEW v3 WITH (security_barrier=true) AS
 SELECT * FROM v2
 WHERE EXISTS(SELECT 1 FROM v1 WHERE v1.a = v2.a)
 AND a%5 = 0 AND snoop('c='||c);
 
 EXPLAIN (VERBOSE, COSTS OFF)
 UPDATE v3 SET a=-a WHERE a >= 990 AND a <= 1020 AND snoop('a='||a);
 UPDATE v3 SET a=-a WHERE a >= 990 AND a <= 1020 AND snoop('a='||a);
 
 DROP TABLE t1 CASCADE;
Results:
 QUERY PLAN 
 --------------------------------------------------------------------------------------------------------
 Update on public.t1 t1_2
 -> Subquery Scan on t1
 Output: (- t1.a), t1.b, t1.c, t1.ctid
 Filter: snoop(('a='::text || (t1.a)::text))
 -> Nested Loop Semi Join
 Output: t1_3.a, t1_3.ctid, t1_3.b, t1_3.c
 Join Filter: (t1_3.a = t1_7.a)
 -> Subquery Scan on t1_3
 Output: t1_3.a, t1_3.ctid, t1_3.b, t1_3.c
 Filter: (((t1_3.a % 5) = 0) AND snoop(('c='::text || (t1_3.c)::text)))
 -> Nested Loop Semi Join
 Output: t1_4.a, t1_4.ctid, t1_4.b, t1_4.c
 -> Subquery Scan on t1_4
 Output: t1_4.a, t1_4.ctid, t1_4.b, t1_4.c
 Filter: (((t1_4.a % 3) = 0) AND snoop(('b='::text || (t1_4.b)::text)))
 -> Index Scan using t1_a_idx on public.t1 t1_6
 Output: t1_6.a, t1_6.ctid, t1_6.b, t1_6.c
 Index Cond: ((t1_6.a >= 990) AND (t1_6.a <= 1020))
 Filter: ((t1_6.a % 2) = 0)
 -> Append
 -> Index Only Scan using t1_a_idx on public.t1 t1_5
 Output: t1_5.a
 Index Cond: (t1_5.a = t1_4.a)
 -> Index Only Scan using t2_a_idx on public.t2
 Output: t2.a
 Index Cond: (t2.a = t1_4.a)
 -> Append
 -> Seq Scan on public.t1 t1_7
 Output: t1_7.a, t1_7.b, t1_7.c
 Filter: ((t1_7.a % 2) = 0)
 -> Seq Scan on public.t2 t2_1
 Output: t2_1.a, t2_1.b, t2_1.c
 Filter: ((t2_1.a % 2) = 0)
 -> Subquery Scan on t1_1
 Output: (- t1_1.a), t1_1.b, t1_1.c, t1_1.ctid
 Filter: snoop(('a='::text || (t1_1.a)::text))
 -> Nested Loop Semi Join
 Output: t1_8.a, t1_8.ctid, t1_8.b, t1_8.c
 Join Filter: (t1_8.a = t1_11.a)
 -> Subquery Scan on t1_8
 Output: t1_8.a, t1_8.ctid, t1_8.b, t1_8.c
 Filter: (((t1_8.a % 5) = 0) AND snoop(('c='::text || (t1_8.c)::text)))
 -> Nested Loop Semi Join
 Output: t1_9.a, t1_9.ctid, t1_9.b, t1_9.c
 -> Subquery Scan on t1_9
 Output: t1_9.a, t1_9.ctid, t1_9.b, t1_9.c
 Filter: (((t1_9.a % 3) = 0) AND snoop(('b='::text || (t1_9.b)::text)))
 -> Index Scan using t2_a_idx on public.t2 t2_3
 Output: t2_3.a, t2_3.ctid, t2_3.b, t2_3.c
 Index Cond: ((t2_3.a >= 990) AND (t2_3.a <= 1020))
 Filter: ((t2_3.a % 2) = 0)
 -> Append
 -> Index Only Scan using t1_a_idx on public.t1 t1_10
 Output: t1_10.a
 Index Cond: (t1_10.a = t1_9.a)
 -> Index Only Scan using t2_a_idx on public.t2 t2_2
 Output: t2_2.a
 Index Cond: (t2_2.a = t1_9.a)
 -> Append
 -> Seq Scan on public.t1 t1_11
 Output: t1_11.a, t1_11.b, t1_11.c
 Filter: ((t1_11.a % 2) = 0)
 -> Seq Scan on public.t2 t2_4
 Output: t2_4.a, t2_4.b, t2_4.c
 Filter: ((t2_4.a % 2) = 0)
 (65 rows)
 
 NOTICE: Snooped value: b=990
 NOTICE: Snooped value: c=990
 NOTICE: Snooped value: a=990
 NOTICE: Snooped value: b=996
 NOTICE: Snooped value: b=1002
 NOTICE: Snooped value: b=1008
 NOTICE: Snooped value: b=1014
 NOTICE: Snooped value: b=1020
 NOTICE: Snooped value: c=1020
 NOTICE: Snooped value: a=1020
 UPDATE 2
=== Update on security barrier view with rules on the base relation ===
Here the rules on the base table turn selected INSERTs and all DELETEs into UPDATEs. They also change the resultRelation index, so that the securityQuals are no longer attached to the resultRelation.
 CREATE TABLE t1 (id int PRIMARY KEY, data text, deleted boolean);
 INSERT INTO t1 VALUES (1, 'Row 1', false), (2, 'Row 2', true);
 
 CREATE RULE t1_ins_rule AS ON INSERT TO t1
 WHERE EXISTS (SELECT 1 FROM t1 WHERE id = new.id)
 DO INSTEAD UPDATE t1 SET data = new.data, deleted = false WHERE id = new.id;
 
 CREATE RULE t1_del_rule AS ON DELETE TO t1
 DO INSTEAD UPDATE t1 SET deleted = true WHERE id = old.id;
 
 CREATE VIEW v1 WITH (security_barrier=true) AS
 SELECT id, data FROM t1 WHERE NOT deleted;
 
 SELECT * FROM v1;
 
 EXPLAIN (VERBOSE, COSTS OFF)
 DELETE FROM v1 WHERE id = 1 AND snoop(data);
 DELETE FROM v1 WHERE id = 1 AND snoop(data);
 
 EXPLAIN (VERBOSE, COSTS OFF)
 INSERT INTO v1 VALUES (2, 'New row 2');
 INSERT INTO v1 VALUES (2, 'New row 2');
 
 SELECT * FROM t1;
 
 DROP TABLE t1 CASCADE;
Results:
 id | data 
 ----+-------
 1 | Row 1
 (1 row)
 
 QUERY PLAN 
 ------------------------------------------------------------------
 Update on public.t1 t1_1
 -> Nested Loop
 Output: t1_1.id, t1_1.data, true, t1_1.ctid, t1.wholerow
 -> Index Scan using t1_pkey on public.t1 t1_1
 Output: t1_1.id, t1_1.data, t1_1.ctid
 Index Cond: (t1_1.id = 1)
 -> Subquery Scan on t1
 Output: t1.id, t1.data, t1.wholerow
 Filter: snoop(t1.data)
 -> Index Scan using t1_pkey on public.t1 t1_2
 Output: t1_2.id, t1_2.data, t1_2.*
 Index Cond: (t1_2.id = 1)
 Filter: (NOT t1_2.deleted)
 (13 rows)
 
 NOTICE: Snooped value: Row 1
 DELETE 0
 
 QUERY PLAN 
 ----------------------------------------------------------
 Insert on public.t1
 InitPlan 1 (returns $0)
 -> Index Only Scan using t1_pkey on public.t1 t1_1
 Index Cond: (t1_1.id = 2)
 -> Result
 Output: 2, 'New row 2'::text, NULL::boolean
 One-Time Filter: ($0 IS NOT TRUE)
 
 Update on public.t1
 InitPlan 1 (returns $0)
 -> Index Only Scan using t1_pkey on public.t1 t1_1
 Index Cond: (t1_1.id = 2)
 -> Result
 Output: t1.id, 'New row 2'::text, false, t1.ctid
 One-Time Filter: $0
 -> Index Scan using t1_pkey on public.t1
 Output: t1.id, t1.ctid
 Index Cond: (t1.id = 2)
 (18 rows)
 
 INSERT 0 0
 
 id | data | deleted 
 ----+-----------+---------
 1 | Row 1 | t
 2 | New row 2 | f
 (2 rows)
=== Update on security barrier view with rules on the base relation (#2) ===
A much more artificial example — the rule on the base relation turns DELETEs INTO SELECTs:
 CREATE TABLE t1 (id int, data text);
 INSERT INTO t1 VALUES (1, 'Public row'), (-1, 'Private row');
 
 CREATE RULE t1_del_rule AS ON DELETE TO t1
 DO INSTEAD SELECT * FROM t1 WHERE id = old.id;
 
 CREATE VIEW v1 WITH (security_barrier=true) AS
 SELECT * FROM t1 WHERE id > 0;
 
 EXPLAIN (VERBOSE, COSTS OFF)
 DELETE FROM v1 WHERE snoop(data);
 DELETE FROM v1 WHERE snoop(data);
 
 SELECT * FROM t1;
 
 DROP TABLE t1 CASCADE;
Results:
 QUERY PLAN 
 ------------------------------------------------
 Hash Join
 Output: t1_1.id, t1_1.data
 Hash Cond: (t1_1.id = t1.id)
 -> Seq Scan on public.t1 t1_1
 Output: t1_1.id, t1_1.data
 -> Hash
 Output: t1.id
 -> Subquery Scan on t1
 Output: t1.id
 Filter: snoop(t1.data)
 -> Seq Scan on public.t1 t1_2
 Output: t1_2.id, t1_2.data
 Filter: (t1_2.id > 0)
 (13 rows)
 
 NOTICE: Snooped value: Public row
 id | data 
 ----+------------
 1 | Public row
 (1 row)
 
 DELETE 0
 
 id | data 
 ----+-------------
 1 | Public row
 -1 | Private row
 (2 rows)