sql - postgres 中的行级安全性 (RLS) 性能明显较慢。

Question

描述:这是性能问题的示例演示。

我们首先创建了两个表，启用了行级安全性并创建了策略。

表定义:

create table sample_schema.sample_table1(ID numeric(38) PRIMARY KEY NOT NULL,
                 tenant_id VARCHAR(255) NOT NULL,
                 Description VARCHAR(255)
        );

create table sample_schema.sample_table2(ID2 numeric(38) PRIMARY KEY NOT NULL,
                 tenant_id VARCHAR(255) NOT NULL,
                 table1_id numeric (38),
                 Description2 VARCHAR(255)
        );    

索引创建:

CREATE UNIQUE INDEX sample_table1_idx1 ON sample_schema.sample_table1(tenant_id,id);            

启用行级安全性:

ALTER TABLE sample_schema.sample_table1 ENABLE ROW LEVEL SECURITY;   

创建角色:

CREATE ROLE tenant_grp_role_p_id;    

创建策略:我想要一个策略来选择数据，其中 tenant_id 列值具有与已登录用户相同的角色。

CREATE POLICY Tenant_Roles ON  sample_schema.sample_table1 TO tenant_grp_role_p_id USING ((tenant_id) IN ( SELECT rolname FROM pg_roles WHERE    pg_has_role( current_user, oid, 'member')));

创建示例数据:

insert into sample_schema.sample_table1 values (1,'user1_tenant1',1,'Table1 Data');
insert into sample_schema.sample_table2 values (2,'user1_tenant1',1,'Table2 Data');

问题:下面的查询没有使用主键索引。

SELECT * FROM sample_schema.sample_table1 ST1,  sample_schema.sample_table2 T2 WHERE ST1.id = ST2.table1_id  AND ST1.id = 1;    

问题:如果我禁用了 RLS 则使用主键索引。为什么启用 RLS 时不使用主键索引扫描？

注意:
A.如果我禁用行级安全并运行上面的查询，它会使用索引。
B.下面是禁用低级别安全性时的解释计划输出。

Nested Loop  (cost=0.29..19.19 rows=1 width=1129)  ->  Index Scan using sample_table1_pkey on sample_table1 st1  (cost=0.29..8.30 rows=1 width=37)
    Index Cond: (id = '1'::numeric)  ->  Seq Scan on sample_table2 st2  (cost=0.00..10.88 rows=1 width=1092)        Filter: (table1_id = '1'::numeric);    

C.如果我启用低级别安全并运行它不使用索引的查询。
下面是启用低级别安全性时的解释计划输出。

 Nested Loop  (cost=1.03..946.65 rows=79 width=1129) ->  Seq Scan on sample_table2 st2  (cost=0.00..10.88 rows=1 width=1092)  Filter: (table1_id = '1'::numeric)  ->  Subquery Scan on st1  (cost=1.03..934.98 rows=79 width=37)
    Filter: (st1.id = '1'::numeric)        ->  Hash Join  (cost=1.03..738.11 rows=15750 width=37)              Hash Cond: ((st1_1.tenant_id)::name = pg_authid.rolname)              ->  Seq Scan on sample_table1 st1_1  (cost=0.00..578.00 rows=31500 width=37)              ->  Hash  (cost=1.01..1.01 rows=1 width=68)                    ->  Seq Scan on pg_authid  (cost=0.00..1.01 rows=1 width=68)                          Filter: pg_has_role("current_user"(), oid, 'member'::text);   

请帮我解决这个问题..

Answer 1

参见 this message thread在 pgsql-general 邮件列表上获取详细信息。

I recently applied RLS to several large (several million rows) tables in my 9.5 database and noticed that queries against a single large RLS protected table perform well however queries that join several large RLS protected tables perform very poorly. The explain plan shows the optimizer is scanning the entire table to enforce the RLS policy before executing the primary key join that would reduce the query results to a single row from each table. Clearly performance would be better if it performed the join before the policy check.

From what I can understand the RLS implementation strives to execute policy checks before user provided predicate checks so as to avoid leaking protected data.

以及响应:

Join cases with RLS aren't optimized very well at the moment. There's work afoot to improve this - see https://www.postgresql.org/message-id/flat/8185.1477432701%40sss.pgh.pa.us - but it won't be in production before v10.

和:

You can use a security barrier view which is owned by the same user that the tables underneath are owned by, that will bypass RLS on the tables themselves and therefore you'll need to implement the appropriate quals in the security barrier view.

所以你可以等待 PG10，或者尝试使用 security barrier view反而。该博文还解释了为什么 Postgres 不尝试结合(和优化)安全条件和用户指定的条件:自定义函数可用于泄漏值，否则这些值将对用户隐藏。

要创建这样的 View ，只需在定义中添加with (security_barrier):

rhaas=# create or replace view unclassified_emp with (security_barrier) as
        select * from emp where organization <> 'CIA';
CREATE VIEW

this detailed blog post 中有更多信息也是。