postgresql 为什么行级安全性(RLS)不使用索引？

cngwdvgl  于 2023-04-20  发布在  PostgreSQL
关注(0)|答案(6)|浏览(288)

我有一个包含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扫描？

postgresql

6条答案

按热度按时间
0kjbasz6

0kjbasz61#

为什么你没有看到与没有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得到了这个信息，但用我自己的话解释/简化了它。

赞(0）分享  回复(0）举报  2023-04-20
c9x0cxw0

c9x0cxw02#

我不能把我的手指的区别，但我认为你应该得到一个更好的计划与一个更聪明的政策：

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

我想提一下，将行级安全性建立在用户可以随时更改的占位符变量上是值得怀疑的安全性。

赞(0）分享  回复(0）举报  2023-04-20
gv8xihay

gv8xihay3#

this comment的作者（通过反复试验）提出了一个将子查询转换为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
        )
    )
);

挺尴尬的，但谁知道呢。。

赞(0）分享  回复(0）举报  2023-04-20
unftdfkk

unftdfkk4#

subZero Slack上的另一位用户分享了一个解决方案，该解决方案基于在函数中 Package 当前用户权限的查找。在您的情况下，类似于：

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视图中，如我报告的the other solution。
一个警告：
权限表本身存在一个循环依赖问题，所以我必须做一个特殊情况策略。不过，这个策略没有任何性能问题，所以它很好。
(Note在他们的情况下，权限保存在一个 table 中，可由管理员用户编辑，而不是像您的情况那样生成。）

赞(0）分享  回复(0）举报  2023-04-20
92dk7w1h

92dk7w1h5#

一个解决方案（基于this post，它有其他几个很好的建议和基准）是根本不使用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是为了防止潜在的信息泄漏，但会带来性能成本，因此请考虑在您的情况下是否有必要。）

赞(0）分享  回复(0）举报  2023-04-20
x9ybnkn6

x9ybnkn66#

问题中没有说明，但我假设从public.users的读取是从另一个面向API的模式（我们称之为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模式中的视图/函数触发的，所以引用该模式中的视图对查询优化器来说不那么令人困惑，或者这只是一个技巧，让优化器开始工作，而不管查询是如何产生的（在我看来，后者似乎更有可能，但谁知道呢）。

赞(0）分享  回复(0）举报  2023-04-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com