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)

Making security barrier views automatically updatable

Contents

How it works

The problem with LATERAL

Test cases

Confirm non-security barrier views leak

Confirm security barrier views don't leak

Nested security barrier views

Security barrier view WITH CHECK OPTION

Security barrier view WITH CHECK OPTION and BEFORE trigger on base table

Security barrier view with ORDER BY and non-updatable columns

Security barrier view with subqueries

Security barrier view based on an inheritance set

Update on security barrier view joined to subquery

Update on security barrier view with LATERAL join to subquery

Update on security barrier view with LATERAL join to JOIN subquery

Security barrier views with subqueries based on other security barrier views

Update on security barrier view with rules on the base relation

Update on security barrier view with rules on the base relation (#2)

Navigation menu

Page actions

Page actions

Personal tools

Navigation

Search

Tools