使安全性障碍视图自动更新

本页介绍了一种使安全性障碍视图自动更新的方法。另请参阅自动更新安全性障碍视图，其中描述了一种不同的方法以及一些使此方法变得困难的基本问题。

• 原始建议，其中大部分工作在重写器中完成。
• 优化补丁，其中安全性障碍扩展代码移到计划程序以解决继承问题。
• 对规则进行修复后进行进一步更新

此补丁的修补程序条目上将出现更多更新信息。

工作原理

此补丁有两个主要部分：

1. RangeTblEntry 结构中的一个新的字段 securityQuals。
   这是一个列表，可让嵌套安全屏障视图中的资格与相互之间以及查询的联合树中用户提供的资格区分开来。如果目标视图具有安全屏障选项，则会在 rewriteTargetView() 中填充此字段，而不是将视图的资格与任何用户提供的资格合并。最终将这些资格用于构建安全屏障子查询以获取要更新的行，但最初将它们分开以便其余重写器代码和规划器的准备阶段无需处理子查询结果关系。
   
   
2. 规划器预处理阶段的新代码来处理任何具有 securityQuals 的 RTE。
   此类 RTE 会转变成（可能是嵌套的）子查询 RTE，其中安全栏属性设置为防止推送有泄漏的用户提供的资格。
   
   

第一部分非常简单。然而，规划器中的新代码必须在恰当的时间发生，以便与继承和目标列表扩展恰当配合使用。以下是通过规划器的路径的概要，显示了新代码插入的位置（对现有代码的更改以粗体显示）

• standard_planner()
  • subquery_planner()
    • 处理 CTE。
    • 上拉子链接 [ANY/EXISTS]。
    • 内嵌返回集合的函数。
    • 上拉子查询。
      注意：这排除了具有安全栏属性的子查询。
    • 将一个简单的 UNION ALL 查询扁平化成具有 append_rel_list 的查询。
    • 预处理行标记。这包括为所有未锁定且通常未锁定非目标关系添加行标记。
      此代码将具有 securityQuals 的 RTE 视为子查询，因为此类 RTE 将稍后成为子查询 RTE。
    • 展开继承的表，在 append_rel_list 中生成条目。
    • 在目标列表、WITH CHECK OPTION 子句、RETURNING 列表、WHERE 资格、HAVING 子句、窗口子句、LIMIT 和 OFFSET 子句、append_rel_list 以及范围表中的函数和 VALUES 中预处理表达式。此预处理包括对任何包含在任何此类表达式中的子链接子查询的递归规划（子查询规划器的递归调用）。因此，查询中这些部分中的所有 SubLink 节点将转变成 SubPlan 节点。
      注意：此预处理排除了范围表中的 RTE 子查询，因为它们由稍后的递归处理。出于同样的原因，此预处理还排除了 RTE securityQuals。
    • 也许将 HAVING 变成 WHERE 子句。
    • 尽可能减少外部连接。
    • 如果结果关系没有继承，直接跳转到 grouping_planner()。
    • 否则 inheritance_planner()
      • 对于每个继承子
        • 复制整个查询并使用 adjust_appendrel_attrs() 修复任何 Vars 以引用当前子关系。
          注意：adjust_appendrel_attrs() 会跳过范围表中的子查询，因为它们不能包含对结果关系的引用。如果允许对结果关系进行 LATERAL 引用，这将会中断。
          adjust_appendrel_attrs() 会下降到 RTE securityQuals，因为它们可能包含对结果关系的反引用。它们可能还包含子链接子查询，因此需要新代码使 adjust_appendrel_attrs() 能够处理此问题（以前这是不可能的，因为所有其他 SubLinks 已在表达式预处理期间转变为 SubPlans）。
        • 小心地处理 rangetable，以便查询的每个副本都能共享相同的 rangetable。
        • 使用 grouping_planner() 计划子查询
          • 使用递归计划集合操作。
          • 预处理目标列表。如果更新，这将为结果关系的每个属性添加目标列表条目。在这一点上，结果关系可能是原始结果关系的继承子，所以它可能有附加的属性。
          • 展开任何 RTE securityQuals。这将任何带有 securityQuals 的 RTE 转换为具有 security_barrier 特性的子查询 RTE。新子查询的目标列表使用外部查询引用的结果关系属性的（现在完全已知的）列表构建。如果相关 RTE 是结果关系，则会复制它并保留不展开，作为新的结果关系。展开的子查询 RTE 充当要更新的行来源。
          • 使用 query_planner() 查找最佳路径
            • ...
            • make_one_rel()
              • set_base_rel_size()
                • set_rel_size()
                  • 对于子查询 RTE，set_subquery_pathlist()
                    • 在安全执行的情况下向下从查询导出任何条件。
                      如果子查询具有 security_barrier 特性，则排除包含对易泄漏函数引用的条件。为什么不检查函数参数？？？
                    • 递归调用 subquery_planner() 来计划 RTE 的子查询。

LATERAL 的问题

在 9.3.2 和 158b7fa6a34006bdc70b515e14e120d3e896589b 提交之前的 9.4devel 中，可以用 FROM 子句（其中包含对结果关系的 LATERAL 引用）编写 UPDATE 内容。当前代码中存在的问题如上所示 – 如果结果关系是一个继承集的父集，那么它就会崩溃，因为 adjust_appendrel_attrs() 不会下降到 rangetable 中的子查询，因此那些 LATERAL 引用不会在继承计划器中被重写来引用正确的 append_rel 子条目。以下示例对此进行了说明

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;

如果允许，则会产生

ERROR:  no relation entry for relid 1

如果 adjust_appendrel_attrs() 要下降到 rangetable 中的子查询以修复此类引用，那么将不得不修改它以处理可能在那里遇到的任何 SubLinks（就像此修补程序一样），因为 rangetable 中的子查询未在初始预处理中计划。

测试用例

这些测试使用了以下函数，如果视图没有 security_barrier 特性，该函数可以窥探视图条件后面隐藏的值

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;

以及以下 LEAKPROOF 函数，该函数不会透露有关传递给它的任何数据，因此应该可以安全地向下推送到安全障碍视图

CREATE OR REPLACE FUNCTION leakproof(anyelement)
RETURNS boolean AS
$$
BEGIN
  RETURN true;
END;
$$
LANGUAGE plpgsql STRICT IMMUTABLE LEAKPROOF;

确认非安全障碍视图泄漏

如果缺少 security_barrier 属性，那么 snoop() 函数的低成本会导致它首先执行并泄露视图应该隐藏的信息

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;

结果

                                   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)

因此，具有视图访问权限的人员能够看到 Dick 个人，即使视图中未公开此人。

确认安全屏障视图无泄露

在视图上设置 security_barrier 选项应导致使用子查询扫描的计划，并且包含泄露函数的标记符不应下推，但防泄露函数可以下推

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;

结果

                                              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)

因此，没有信息泄露，因为 snoop 函数未下推至子查询中，但防泄露函数下推了。

嵌套的安全屏障视图

没有任何内容阻止用户定义他们自己的安全屏障视图并在其中放置恶意函数，因此必须保持每个视图的标记符分开，仅在安全的情况下合并标记符

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;

结果

                                               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)

因此，不安全的顶级标记符与外部视图的标记符分开，而外部视图的不安全的标记符与内部视图的标记符分开，但所有安全的标记符一直下推到最底层才能产生最高效的计划。

使用 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;

结果

-- 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).

使用 CHECK OPTION 的安全屏障视图和基表的 BEFORE 触发器

CHECK OPTION 应适用于 NEW 行（在 before 触发器触发后）

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();

结果

-- 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)).

带有 ORDER BY 和不可更新列的安全屏障视图

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;

结果

 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

带有子查询的安全屏障视图

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;

结果

 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

基于继承集的安全屏障视图

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;

结果

  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)

在联接至子查询的安全屏障视图上更新

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;

结果

                         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)

在使用 LATERAL 联接至子查询的安全屏障视图上更新

测试已移除 --- 不再允许 LATERAL 引用结果关系。

原始测试

在使用 LATERAL 联接至 JOIN 子查询的安全屏障视图上更新

测试已移除 --- 不再允许 LATERAL 引用结果关系。

原始测试

基于其他安全屏障视图的子查询安全屏障视图

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;

结果

                                               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

具有基关系规则的安全屏障视图上的更新

此处基表上的规则将选定的 INSERT 和所有 DELETE 转换为 UPDATE。它们还更改了 resultRelation 索引，以便 securityQuals 不再附加到 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;

结果

 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)

在具有基关系规则的安全屏障视图上更新 (#2)

更加不真实的示例 — 基关系上的规则将 DELETE 转换为 SELECT

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;

结果

                   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)