postgresql行级安全限制策略编写单选查询

Question

下面是3个表脚本。

CREATE TABLE rls_permission(upn text,is_all boolean ,reference int[]);
CREATE TABLE objects(key serial primary key,object_type_key int,status text );
CREATE TABLE object_attributes(key serial primary key,objects_key int,status text ,values text,reference int[], type_key int);
---Indexes
CREATE INDEX objects_object_type_key_status ON objects USING btree    (object_type_key, status )    
CREATE UNIQUE INDEX object_attributes_objects_key_type_key_uniq ON object_attributes (objects_key, type_key);
CREATE INDEX object_attributes_reference on object_attributes USING gin(value_reference gin__int_ops)

Web应用程序将首先检索某些对象，然后检索其各自的对象属性值。一个对象在object_attributes表中可能有许多属性值。

在表rls_permission中配置的用户权限设置，如果列is_all的值为true，则用户可以查看所有行/对象，否则可以查看列reference中提到的引用。(引用由其他接口填充到rls_permission中的值，该接口具有完全访问权限并从object_attributes获取值(

我已经在objects表上创建了行级别以下的策略。

CREATE POLICY no_rls_objects ON objects AS PERMISSIVE FOR ALL TO PUBLIC USING (TRUE);
CREATE POLICY rls_on_objects ON objects AS RESTRICTIVE TO web_app_user
USING( (current_setting('db.rls_user')='web_system')
OR (SELECT per.is_all FROM rls_permission per
WHERE (lower(per.upn) = 
lower(current_setting('db.rls_user'::text)))
) 
OR (EXISTS ( SELECT 1 FROM object_attributes att
JOIN rls_permission per ON ((per.reference && att.reference)))
WHERE ((lower(per.upn) = lower(current_setting('db.rls_user'::text))) 
AND (att.objects_key = objects.key) 
)
)
OR (object_type_key NOT IN (1,24))
) 

rls_on_objects限制策略有两个用OR分隔的SELECT要求。

我不能创建两个RESTRICTIVE策略，每个策略中有一个查询，因为有两个Restractive策略会使用AND组合。但我需要使用OR组合两个查询。

有没有办法重写查询并生成单个查询？

因为两个查询都有(lower(per.upn) = lower(current_setting('db.rls_user'::text)))，所以它在两个查询中都有计算，当is_all为false或null时，它会检查/执行第二个查询。将查询转换为单个查询将提高RLS的性能，因为它不需要计算两次。

更新1:下面是针对我的策略执行的查询计划和查询select * from objects where status='active' and object_type_key=1

Aggregate  (cost=122653500.78..122653500.79 rows=1 width=8) (actual time=3087.257..3087.357 rows=1 loops=1)
InitPlan 1 (returns $0)
->  Seq Scan on rls_permission  (cost=0.00..27.40 rows=4 width=1) (actual time=0.014..0.015 rows=1 loops=1)
Filter: (lower(upn) = lower(current_setting('db.rls_user'::text)))
Rows Removed by Filter: 3
->  Index Scan using objects_object_type_key_status on objects  (cost=0.43..122650563.66 rows=1163888 width=0) (actual time=1454.965..3086.453 rows=7697 loops=1)
Index Cond: (object_type_key = 1)
Filter: (((current_setting('db.rls_user'::text) = 'web_system'::text) OR $0 OR (alternatives: SubPlan 2 or hashed SubPlan 3) OR (object_type_key <> ALL ('{1,24}'::integer[]))) AND (status = 'active'::enm_status))
Rows Removed by Filter: 3024827
SubPlan 2
->  Nested Loop  (cost=0.44..40.35 rows=1 width=0) (never executed)
Join Filter: (per.reference && att.reference)
->  Index Scan using object_attributes_object_key_type_key_uniq on object_attributes att  (cost=0.44..12.90 rows=1 width=25) (never executed)
Index Cond: ((object_key = objects.key) AND (type_key = ANY ('{6,192}'::integer[])))
->  Seq Scan on rls_permission per  (cost=0.00..27.40 rows=4 width=32) (never executed)
Filter: (lower(upn) = lower(current_setting('db.rls_user'::text)))
SubPlan 3
->  Nested Loop  (cost=1000.00..741248.75 rows=45627 width=4) (actual time=0.333..1449.186 rows=9079 loops=1)
Join Filter: (per_1.reference && att_1.reference)
Rows Removed by Join Filter: 1170632
->  Gather  (cost=1000.00..672780.72 rows=1140677 width=29) (actual time=0.256..1072.187 rows=1179711 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Parallel Seq Scan on object_attributes att_1  (cost=0.00..557713.02 rows=475282 width=29) (actual time=0.032..1214.816 rows=393237 loops=3)
Filter: (type_key = ANY ('{6,192}'::integer[]))
Rows Removed by Filter: 7566828
->  Materialize  (cost=0.00..27.42 rows=4 width=32) (actual time=0.000..0.000 rows=1 loops=1179711)
->  Seq Scan on rls_permission per_1  (cost=0.00..27.40 rows=4 width=32) (actual time=0.009..0.010 rows=1 loops=1)
Filter: (lower(upn) = lower(current_setting('db.rls_user'::text)))
Rows Removed by Filter: 3
Planning Time: 0.423 ms
Execution Time: 3087.431 ms

Updated2---爱德华H.的查询计划建议查询

query1
Aggregate  (cost=239792868.03..239792868.04 rows=1 width=8) (actual time=33737.946..33737.947 rows=1 loops=1)
->  Index Scan using object_type_key_status on objects  (cost=0.43..239790834.63 rows=813358 width=0) (actual time=4.949..33735.611 rows=7697 loops=1)
Index Cond: (object_type_key = 1)
Filter: (((current_setting('db.rls_user'::text) = 'web_system'::text) OR (SubPlan 2) OR (object_type_key <> ALL ('{1,24}'::integer[]))) AND (status = 'active'::enm_status))
Rows Removed by Filter: 3024827
SubPlan 2
->  Seq Scan on rls_permission per  (cost=0.00..79.03 rows=4 width=1) (actual time=0.009..0.009 rows=1 loops=3032524)
Filter: (lower(upn) = lower(current_setting('db.rls_user'::text)))
Rows Removed by Filter: 3
SubPlan 1
->  Index Scan using object_attributes_objects_key_type_key_uniq on object_attributes att  (cost=0.44..12.91 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=3032524)
Index Cond: ((objects_key = objects.key) AND (type_key = ANY ('{6,192}'::integer[])))
Filter: (reference && per.reference)
Rows Removed by Filter: 0
Planning Time: 0.781 ms
Execution Time: 33738.102 ms
query2
Aggregate  (cost=122743462.16..122743462.17 rows=1 width=8) (actual time=24355.184..24355.186 rows=1 loops=1)
->  Index Scan using objects_object_type_key_status on objects  (cost=0.43..122741428.77 rows=813358 width=0) (actual time=1.222..24353.622 rows=7697 loops=1)
Index Cond: (object_type_key = 1)
Filter: (((current_setting('db.rls_user'::text) = 'web_system'::text) OR (SubPlan 1) OR (object_type_key <> ALL ('{1,24}'::integer[]))) AND (status = 'active'::enm_status))
Rows Removed by Filter: 3024827
SubPlan 1
->  GroupAggregate  (cost=40.36..40.38 rows=1 width=2) (actual time=0.007..0.007 rows=0 loops=3032524)
Group Key: per.is_all
->  Sort  (cost=40.36..40.37 rows=1 width=26) (actual time=0.007..0.007 rows=0 loops=3032524)
Sort Key: per.is_all
Sort Method: quicksort  Memory: 25kB
->  Nested Loop  (cost=0.44..40.35 rows=1 width=26) (actual time=0.006..0.006 rows=0 loops=3032524)
Join Filter: (att.reference && per.reference)
Rows Removed by Join Filter: 0
->  Index Scan using object_attributes_objects_key_type_key_uniq on object_attributes att  (cost=0.44..12.90 rows=1 width=25) (actual time=0.004..0.004 rows=0 loops=3032524)
Index Cond: ((objects_key = objects.key) AND (type_key = ANY ('{6,192}'::integer[])))
->  Seq Scan on rls_permission per  (cost=0.00..27.40 rows=4 width=33) (actual time=0.004..0.004 rows=1 loops=1098057)
Filter: (lower(upn) = lower(current_setting('db.rls_user'::text)))
Rows Removed by Filter: 3
Planning Time: 0.340 ms
Execution Time: 24355.242 ms

感谢

Answer 1

试试这样的东西：

SELECT per.is_all
OR EXISTS ( SELECT 1
FROM object_attributes att
WHERE att.reference && per.reference
AND att.objects_key = objects.key
)
FROM rls_permission per
WHERE lower(per.upn) = lower(current_setting('db.rls_user'::text))

或者这可能更快：

SELECT per.is_all
OR bool_or(att.reference IS NOT NULL)
FROM rls_permission per
LEFT JOIN object_attributes att
ON att.reference && per.reference
WHERE lower(per.upn) = lower(current_setting('db.rls_user'::text))
AND att.objects_key = objects.key
GROUP BY per.is_all

Answer 2

我认为您关心的是错误的问题。

重复的WHERE子句不会成为主要的性能问题。OR和必须为每个表行执行的带有联接的子查询将是一个更大的问题。并不是说我知道如何改进，除了使用一个更简单的权限系统。