我有一个患者和治疗师的应用程序。它们都在同一个users表中。患者应该能够看到他们的治疗师，治疗师应该能够看到患者。
我已经设置了一个具有成对用户ID的物化视图(user_access_pairs)，如果两个用户在视图中有一行，那么这意味着他们应该可以访问彼此。
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策略，该策略限制了谁可以使用jwt令牌读取哪个users。
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。它大大提高了性能。但我还在做序列扫描。
以下是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
)
);
使用RLS查询此表仍然可以对users表进行seq扫描，即使策略中的exists查询使用索引也是如此。
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扫描？
在没有RLS策略的情况下，您看不到与看似等效的查询相同的计划的原因是，在考虑RLS策略之前，子查询上拉发生了。这是一个计划者的怪癖。
总之，不幸的是，RLS策略与子查询相结合在性能方面互不友好。
对于您的信息，当比较以下两个查询时，可以看到类似的表现：
SELECT ... FROM my_table WHERE EXISTS(SELECT ...);
SELECT ... FROM my_table WHERE CASE WHEN true THEN EXISTS(SELECT ...) END;
这里，虽然两个查询是等效的，但第二个查询会导致子查询的(散列)子计划，因为不必要的CASE WHEN true的折叠是在子查询上拉后完成的。
免责声明：我在IRC#postgresql上从RhodiumToad获得了这些信息，但用我自己的话解释/简化了它。
我无法指出区别，但我认为你应该用更明智的策略来获得更好的计划：
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)
AND id2 = users.id)
);
我想指出的是，将行级安全性建立在用户可以随时更改的占位符变量上是有问题的安全性。
此评论的作者(经过反复试验)提出了将子查询强制转换为ARRAY的解决方案。根本不确定它是否适用于您的情况，但它只是表明，非常意外的技巧显然会吓到优化器去做它的工作。
所以你可以试试：
create policy select_users_policy
on public.users
for select using (
users.id = any (
array(
select id1
from user_access_pairs
where
id1 = current_setting('jwt.claims.user_id'::text, true)::integer
and id2 = users.id
)
)
);
很尴尬，但谁知道呢。。。
subZero Slack上的另一个用户共享了一个基于在函数中封装当前用户权限查找的解决方案。在你的情况下，类似于：
create policy select_users_policy
on public.users
for select using (
id IN (
select * from current_user_read_users()
)
);
您将创建一个current_user_read_users()函数，该函数从jwt中查找user_id，并根据user_access_pairs返回当前用户可能读取的用户集。
此函数与user_access_pairs视图具有相同的所有者，或者用SECURITY DEFINER声明该函数(以便绕过RLS)，这可能很重要，也可能不重要。重要的部分可能只是将子查询提取到一个函数中(以某种方式帮助优化器)，但报告的其他内容有助于解决其他性能问题。
最后，您可能想尝试将其放在api视图中，就像我报告的另一个解决方案一样。
一个警告：
权限表本身存在循环依赖性问题，因此我不得不执行一个特殊情况策略。不过，那台没有任何性能问题，所以很好。
(注意，在他们的情况下，权限保存在表中，可由管理员用户编辑，而不是像您的情况那样生成。)
一个解决方案(基于这篇文章，其中有其他几个好的建议和基准)是根本不使用RLS，而是将过滤构建到一个视图中：
create view api.allowed_users
with (security_barrier)
as
select id, first_name, last_name, favorite_color
from public.users
join user_access_pairs uap
on uap.id1 = current_setting('jwt.claims.user_id'::text, true)::integer
您已经在user_access_pairs视图中表达了您的访问策略，因此可以说RLS规则并没有真正添加任何内容。
(security_barrier是为了防止潜在的信息泄露，但会带来性能成本，因此请考虑在您的情况下是否有必要这样做。)
问题中没有说明，但我假设从public.users的读取是从另一个API-facing模式(让我们称之为api)触发的。
一个人在subZero Slack上分享：
我遇到了同样的问题，并根据我的
api视图定义了RLS，这解决了seq扫描问题。但是，在对这些视图进行更改时，保持这种状态有点痛苦，因为对于迁移，我必须首先放弃RLS策略，更改视图，然后重新创建策略。。。当RLS中涉及子查询时，我使用api视图。
因此，它们使用完全相同的规则，但引用的是api.foo和api.bar视图，而不是public.foo和public.bar表。
在您的情况下，您可以尝试：
create policy select_users_policy
on public.users
for select using (
exists (
select 1
from api.user_access_pairs
where
id1 = current_setting('jwt.claims.user_id'::text, true)::integer
and id2 = api.users.id
)
);
因此，假设api模式中有一个users视图镜像public.users，并将user_access_pairs移动到api(或创建一个引用它的视图)。
我不清楚这是否有效，因为查询最初是从api模式中的视图/函数触发的，因此在该模式中引用视图对查询优化器来说不那么令人困惑，或者这只是让优化器启动的一个技巧，而不管查询是如何产生的。(在我看来，后者似乎更有可能，但谁知道呢。)