我有一个包含Patients和Therapists的应用程序。他们都在同一个users
表中。Patients应该能够看到他们的Therapist,Therapists应该能够看到他们的Patient。
我已经设置了一个物化视图(user_access_pairs
),其中包含成对的用户ID,如果两个用户在视图中有一行,那么这意味着他们应该可以访问对方。
database> \d user_access_pairs
+----------+---------+-------------+
| Column | Type | Modifiers |
|----------+---------+-------------|
| id1 | integer | |
| id2 | integer | |
+----------+---------+-------------+
Indexes:
"index_user_access_pairs" UNIQUE, btree (id1, id2)
下面是users
表的定义,它有一堆与这个问题无关的列。
database> \d users
+-----------------------------+-----------------------------+-----------------------------------------------------+
| Column | Type | Modifiers |
|-----------------------------+-----------------------------+-----------------------------------------------------|
| id | integer | not null default nextval('users_id_seq'::regclass) |
| first_name | character varying(255) | |
| last_name | character varying(255) | |
+-----------------------------+-----------------------------+-----------------------------------------------------+
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
我已经创建了一个RLS策略,它限制了哪些users
可以由使用jwt令牌的人读取。
create policy select_users_policy
on public.users
for select using (
(current_setting('jwt.claims.user_id'::text, true)::integer, id) in (
select id1, id2 from user_access_pairs
)
);
这看起来很合理,但是我得到了糟糕的性能。查询规划器在user_access_pairs
上进行顺序扫描,尽管那里有一个索引。
database> set jwt.claims.user_id to '2222';
database> explain analyze verbose
select first_name, last_name
from users
+------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|------------------------------------------------------------------------------------------------------------------------------------|
| Seq Scan on public.users (cost=231.84..547.19 rows=2386 width=14) (actual time=5.481..6.418 rows=2 loops=1) |
| Output: users.first_name, users.last_name |
| Filter: (hashed SubPlan 1) |
| Rows Removed by Filter: 4769 |
| SubPlan 1 |
| -> Seq Scan on public.user_access_pairs (cost=0.00..197.67 rows=13667 width=8) (actual time=0.005..1.107 rows=13667 loops=1) |
| Output: user_access_pairs.id1, user_access_pairs.id2 |
| Planning Time: 0.072 ms |
| Execution Time: 6.521 ms |
+------------------------------------------------------------------------------------------------------------------------------------+
然而,如果我切换到一个绕过RLS的超级用户角色,并手动应用相同的过滤器,我会获得更好的性能。
database> set jwt.claims.user_id to '2222';
database> explain analyze verbose
select first_name, last_name
from users
where (current_setting('jwt.claims.user_id'::text, true)::integer, id) in (
select id1, id2 from user_access_pairs
)
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| QUERY PLAN
|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Nested Loop (cost=4.59..27.86 rows=2 width=14) (actual time=0.041..0.057 rows=2 loops=1)
| Output: users.first_name, users.last_name
| Inner Unique: true
| -> Bitmap Heap Scan on public.user_access_pairs (cost=4.31..11.26 rows=2 width=4) (actual time=0.029..0.036 rows=2 loops=1)
| Output: user_access_pairs.id1, user_access_pairs.id2
| Filter: ((current_setting('jwt.claims.user_id'::text, true))::integer = user_access_pairs.id1)
| Heap Blocks: exact=2
| -> Bitmap Index Scan on index_user_access_pairs (cost=0.00..4.31 rows=2 width=0) (actual time=0.018..0.018 rows=2 loops=1)
| Index Cond: (user_access_pairs.id1 = (current_setting('jwt.claims.user_id'::text, true))::integer)
| -> Index Scan using users_pkey on public.users (cost=0.28..8.30 rows=1 width=18) (actual time=0.008..0.008 rows=1 loops=2)
| Output: users.id, users.email, users.encrypted_password, users.first_name, users.last_name, users.roles_mask, users.reset_password_token, users.reset_password_sent_at, users.remember_created_at, users.sign_in_count, users.current_sign_in_at, users.last_sign_in_at,
| Index Cond: (users.id = user_access_pairs.id2)
| Planning Time: 0.526 ms
| Execution Time: 0.116 ms
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
为什么RLS在进行查询时不使用索引?
PS我使用的是PostgreSQL版本12.4
database> select version()
+-------------------------------------------------------------------------------------------------------------------------------+
| version |
|-------------------------------------------------------------------------------------------------------------------------------|
| PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit |
+-------------------------------------------------------------------------------------------------------------------------------+
编辑
谢谢Laurenz的回复。它提高了很多性能。但我仍然得到一些seq扫描。
以下是Laurenz建议的最新政策。
create policy select_users_policy
on public.users
for select using (
exists (
select 1
from user_access_pairs
where
id1 = current_setting('jwt.claims.user_id'::text, true)::integer
and id2 = users.id
)
);
即使策略中的exists
查询使用的是索引,使用RLS查询这个表仍然会对users
表进行seq扫描。
database> set jwt.claims.user_id to '2222';
database> explain analyze verbose
select first_name, last_name
from users
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------|
| Seq Scan on public.users (cost=0.00..40048.81 rows=2394 width=14) (actual time=0.637..1.216 rows=2 loops=1) |
| Output: users.first_name, users.last_name |
| Filter: (alternatives: SubPlan 1 or hashed SubPlan 2) |
| Rows Removed by Filter: 4785 |
| SubPlan 1 |
| -> Index Only Scan using index_user_access_pairs on public.user_access_pairs (cost=0.29..8.31 rows=1 width=0) (never executed) |
| Index Cond: ((user_access_pairs.id1 = (current_setting('jwt.claims.user_id'::text, true))::integer) AND (user_access_pairs.id2 = users.id)) |
| Heap Fetches: 0 |
| SubPlan 2 |
| -> Bitmap Heap Scan on public.user_access_pairs user_access_pairs_1 (cost=4.31..11.26 rows=2 width=4) (actual time=0.075..0.083 rows=2 loops=1) |
| Output: user_access_pairs_1.id2 |
| Recheck Cond: (user_access_pairs_1.id1 = (current_setting('jwt.claims.user_id'::text, true))::integer) |
| Heap Blocks: exact=2 |
| -> Bitmap Index Scan on index_user_access_pairs_on_id1 (cost=0.00..4.31 rows=2 width=0) (actual time=0.064..0.064 rows=2 loops=1) |
| Index Cond: (user_access_pairs_1.id1 = (current_setting('jwt.claims.user_id'::text, true))::integer) |
| Planning Time: 0.572 ms |
| Execution Time: 1.295 ms |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
下面是没有使用RLS的手动查询,这次没有seq扫描,性能明显更好(特别是在更大的数据集上运行时)
database> set jwt.claims.user_id to '2222';
database> explain analyze verbose
select first_name, last_name
from users
where exists (
select 1
from user_access_pairs
where
id1 = current_setting('jwt.claims.user_id'::text, true)::integer
and id2 = users.id
)
+---------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|---------------------------------------------------------------------------------------------------------------------------------------------|
| Nested Loop (cost=4.59..27.86 rows=2 width=14) (actual time=0.020..0.033 rows=2 loops=1) |
| Output: users.first_name, users.last_name |
| Inner Unique: true |
| -> Bitmap Heap Scan on public.user_access_pairs (cost=4.31..11.26 rows=2 width=4) (actual time=0.013..0.016 rows=2 loops=1) |
| Output: user_access_pairs.id1, user_access_pairs.id2 |
| Recheck Cond: (user_access_pairs.id1 = (current_setting('jwt.claims.user_id'::text, true))::integer) |
| Heap Blocks: exact=2 |
| -> Bitmap Index Scan on index_user_access_pairs_on_id1 (cost=0.00..4.31 rows=2 width=0) (actual time=0.010..0.010 rows=2 loops=1) |
| Index Cond: (user_access_pairs.id1 = (current_setting('jwt.claims.user_id'::text, true))::integer) |
| -> Index Scan using users_pkey on public.users (cost=0.28..8.30 rows=1 width=18) (actual time=0.006..0.006 rows=1 loops=2) |
| Output: users.id, users.email, users.encrypted_password, users.first_name, users.last_name, users.roles_mask |
| Index Cond: (users.id = user_access_pairs.id2) |
| Planning Time: 0.464 ms |
| Execution Time: 0.075 ms |
+---------------------------------------------------------------------------------------------------------------------------------------------+
我猜查询规划器会同样对待这两个查询。为什么它们不同,可以做些什么来避免seq扫描?
6条答案
按热度按时间0kjbasz61#
为什么你没有看到与没有RLS策略的看似等价的查询相同的计划,原因是子查询上拉发生在**RLS策略被考虑之前。这是一个计划器的怪癖。
总而言之,RLS策略与子查询的结合不幸地不是彼此的性能方面的朋友。
请注意,在比较以下两个查询时,可以看到类似的表现:
在这里,虽然两个查询是等价的,但是第二个查询为子查询产生一个(散列的)子计划,因为不必要的
CASE WHEN true
的折叠是在子查询上拉后完成的。免责声明:我在IRC #postgresql上从RhodiumToad得到了这个信息,但用我自己的话解释/简化了它。
c9x0cxw02#
我不能把我的手指的区别,但我认为你应该得到一个更好的计划与一个更聪明的政策:
我想提一下,将行级安全性建立在用户可以随时更改的占位符变量上是值得怀疑的安全性。
gv8xihay3#
this comment的作者(通过反复试验)提出了一个将子查询转换为
ARRAY
的解决方案。完全不确定它是否适用于您的情况,但只是表明一些意想不到的技巧显然可以吓到优化器来完成它的工作。所以你可以试试:
挺尴尬的,但谁知道呢。。
unftdfkk4#
subZero Slack上的另一位用户分享了一个解决方案,该解决方案基于在函数中 Package 当前用户权限的查找。在您的情况下,类似于:
你可以创建一个
current_user_read_users()
函数,它从jwt中查找user_id
,并根据user_access_pairs
返回当前用户可能读取的用户集。这个函数是否与
user_access_pairs
视图拥有相同的所有者,或者函数是否用SECURITY DEFINER
声明(这样它就绕过了RLS),这可能是重要的,也可能不是重要的。重要的部分可能只是将子查询拉入函数(以某种方式帮助优化器),* 但是 * 其他事情被报告以帮助解决其他性能问题。最后,您可能想尝试将其放入
api
视图中,如我报告的the other solution。一个警告:
权限表本身存在一个循环依赖问题,所以我必须做一个特殊情况策略。不过,这个策略没有任何性能问题,所以它很好。
(Note在他们的情况下,权限保存在一个 table 中,可由管理员用户编辑,而不是像您的情况那样生成。)
92dk7w1h5#
一个解决方案(基于this post,它有其他几个很好的建议和基准)是根本不使用RLS,而是将过滤构建到视图中:
您已经在
user_access_pairs
视图中表示了访问策略,因此可以说RLS规则实际上并没有添加任何内容。(
security_barrier
是为了防止潜在的信息泄漏,但会带来性能成本,因此请考虑在您的情况下是否有必要。)x9ybnkn66#
问题中没有说明,但我假设从
public.users
的读取是从另一个面向API的模式(我们称之为api
)触发的。一个人在subZero Slack上分享道:
我遇到了同样的问题,并基于我的
api
视图定义了RLS,解决了seq扫描问题。但是在对这些视图进行更改时,维护起来有点痛苦,因为对于迁移,我必须首先删除RLS策略,更改视图,然后重新创建策略。...当RLS中涉及子查询时,我使用api
视图。因此,它们使用完全相同的规则,但引用
api.foo
和api.bar
视图,而不是public.foo
和public.bar
表。在您的情况下,您可以尝试:
因此,这是假设您在
api
模式中有一个users
视图,它镜像了public.users
,并将user_access_pairs
移动到api
(或创建一个引用它的视图)。我不清楚这是否有效,因为查询首先是从
api
模式中的视图/函数触发的,所以引用该模式中的视图对查询优化器来说不那么令人困惑,或者这只是一个技巧,让优化器开始工作,而不管查询是如何产生的(在我看来,后者似乎更有可能,但谁知道呢)。