RLS Performance and Best Practices

RLS 性能和最佳做法 #

🌐 RLS performance and best practices

虽然大部分时间花在考虑 RLS 是为了满足安全需求，但它对查询性能的影响可能非常大。尤其是在那些会查看表中每一行的查询上，比如很多选择操作和更新操作。需要注意的是，使用 limit 和 offset 的查询通常也需要查询所有行来确定顺序，而不仅仅是限制的数量，所以它们也会受到影响。

🌐 Although most of the time spent on thinking about RLS is to get it to handle security needs, the impact of it on performance of your queries can be massive. This is especially true on queries that look at every row in a table like for many select operations and updates. Note that queries that use limit and offset will usually have to query all rows to determine order, not only the limit amount so they are impacted too.

查看最后一节，了解在测试 RLS 改进时如何衡量查询性能的方法。

🌐 See the last section for ways to measure the performance of your queries as you test RLS improvements.

RLS 会导致性能问题吗（在单表查询上）？ #

🌐 Is RLS causing performance issues (on a single table query)?

对于非常慢的查询，或者如果使用文章末尾的工具，请在表上先启用 RLS 运行查询，然后再禁用它运行一次（这应该只在非生产环境中进行）。如果结果相似，那么很可能问题出在查询本身。不过，要记住，RLS 中的任何关联表也需要运行它们的 RLS，除非使用安全定义函数来绕过它们。如果是在安全环境中，你也可以使用密钥创建客户端来绕过 RLS 运行查询。

🌐 For very slow queries, or if using the tools at end of article, run a query with RLS enabled on the table and then with it disabled (this should only be done in a non production environment). If the results are similar then your query itself is likely the performance issue. Although, remember any join tables in RLS will also need to run their RLS unless a security definer function is used to bypass them. You can also create a client using a secret key to run the query bypassing RLS if in a secure environment.

如何提升 RLS 的性能 #

🌐 How to improve RLS performance.

以下这些提示都非常笼统，每条可能对具体情况有帮助，也可能没有帮助。有些更改，比如添加索引，如果对 RLS 性能没有影响，并且你没有用于过滤性能，那么应该撤回。

🌐 The following tips are very broad and each may or may not help the specific case involved. Some changes, like adding indexes, should be backed out if they do not make a difference in RLS performance and you are not using them for filtering performance.

1. 首先要尝试的是在 RLS 中使用但还不是主键或唯一的列上加一个索引。 #

🌐 1. The first thing to try is put an index on columns used in the RLS that are not primary keys or unique already.

对于像 auth.uid() = user_id 这样的 RLS： 添加一个类似 create index userid on test_table using btree (user_id) tablespace pg_default; 的索引。 在大表上性能提升超过 100 倍。

🌐 For RLS like: auth.uid() = user_id Add an index like: create index userid on test_table using btree (user_id) tablespace pg_default; Improvement seen over 100x on large tables.

2. 提高性能的另一种方法是将你的 RLS 查询和函数封装在 select 语句中。 #

🌐 2. Another method to improve performance is to wrap your RLS queries and functions in select statements.

这种方法对于像 auth.uid() 和 auth.jwt() 这样的 JWT 函数，以及任何其他包括 security definer 类型的函数都很有效。将函数封装在一些 SQL 里会让优化器执行一个 initPlan，这样它就可以“缓存”结果，而不是对每一行都调用函数。

警告：只有当查询或函数的结果不会根据行数据变化时，你才能这样做。

对于这种 RLS： is_admin() or auth.uid() = user_id 可以改用这个： (select is_admin()) OR (select auth.uid()) = user_id

🌐 This method works well for JWT functions like auth.uid() and auth.jwt() as well as any other functions including security definer type. Wrapping the function in some SQL causes an initPlan to be run by the optimizer which allows it to "cache" the results versus calling the function on each row. WARNING: You can only do this if the results of the query or function do not change based on the row data. For RLS like this: is_admin() or auth.uid() = user_id Use this instead: (select is_admin()) OR (select auth.uid()) = user_id

1
CREATE OR REPLACE FUNCTION is_admin()
2
  RETURNS boolean as
3
$$
4
begin
5
  return exists(select from rlstest_roles where auth.uid() = user_id and role = 'admin');
6
end;
7
$$ language plpgsql security definer;

3. 不要依赖 RLS 来进行筛选，只能用它来保障安全。 #

🌐 3. Do not rely on RLS for filtering but only for security.

不要这样做（JS 客户端示例）： .from('table').select() 使用 RLS 策略： auth.uid() = user_id 在 RLS 之外再添加一个过滤器： .from('table').select().eq('user_id',userId)

🌐 Instead of doing this (JS client example): .from('table').select() With an RLS policy of: auth.uid() = user_id Add a filter in addition to the RLS: .from('table').select().eq('user_id',userId)

4. 尽可能使用安全定义者函数对其他表进行查询，以绕过它们的行级安全。 #

🌐 4. Use security definer functions to do queries on other tables to bypass their RLS when possible.

与其让 roles_table 有一个 RLS 的 select 策略 auth.uid() = user_id 不如这样做： exists (select 1 from roles_table where auth.uid() = user_id and role = 'good_role') 创建一个安全定义函数 has_role()，然后执行： (select has_role()) 并使用 exists (select 1 from roles_table where auth.uid() = user_id and role = 'good_role') 的代码 注意，如果每次都是固定值，你应该在 select 中封装你的安全定义函数。 记住，你在 RLS 中使用的函数可以从 API 调用。 如果函数的结果可能导致安全泄露，就把它们放到备用 schema 中保护起来。 警告：如果你的安全定义函数使用行信息作为输入参数，一定要测试性能，因为你不能像在第 2 步那样封装函数。

🌐 Instead of having this RLS where the roles_table has an RLS select policy of auth.uid() = user_id: exists (select 1 from roles_table where auth.uid() = user_id and role = 'good_role') Create a security definer function has_role() and do: (select has_role()) with code of exists (select 1 from roles_table where auth.uid() = user_id and role = 'good_role') Note that you should wrap your security definer function in select if it is a fixed value per 2. Remember functions you use in RLS can be called from the API. Secure your functions in an alternate schema if their results would be a security leak. Warning: If your security definer function uses row information as an input parameter be sure to test performance as you can't wrap the function as in 2.

1
CREATE OR REPLACE FUNCTION has_role()
2
    RETURNS boolean as
3
$$
4
begin
5
    return exists (select 1 from roles_table where auth.uid() = user_id and role = 'good_role');
6
end;
7
$$ language plpgsql security definer;

5. 总是优化连接查询，将行列与固定的连接数据进行比较。 #

🌐 5. Always optimize join queries to compare row columns to fixed join data.

与其在连接表的某行列上使用 WHERE 查询，不如把你的查询组织成获取满足条件的所有列值到一个数组或集合中。然后使用 IN 或 ANY 操作来过滤该行列。这个 RLS（行级安全）只允许选择用户有权限访问的 team_id 行： auth.uid() in (select user_id from team_user where team_user.team_id = table.team_id) 会比： team_id in (select team_id from team_user where user_id = auth.uid()) 慢得多。你也可以考虑把连接查询移到一个安全定义者函数中，以避免对连接表使用 RLS： team_id in (select user_teams()) 请注意，如果 in 列表超过 1 万个项，可能需要额外分析。参见后续测试：https://github.com/GaryAustin1/RLS-Performance/tree/main/tests/Supabase-Docs-Test。

🌐 Instead of querying on a row column in a join table WHERE, organize your query to get all the column values that meet your query into an array or set. Then use an IN or ANY operation to filter against the row column. This RLS to allow select only for rows where the team_id is one the user has access to: auth.uid() in (select user_id from team_user where team_user.team_id = table.team_id) will be much slower than: team_id in (select team_id from team_user where user_id = auth.uid()) Also consider moving the join query to a security definer function to avoid RLS on join table: team_id in (select user_teams()) Note that if the in list gets to be over 10K items, then extra analysis is likely needed. See this follow up testing: https://github.com/GaryAustin1/RLS-Performance/tree/main/tests/Supabase-Docs-Test .

6. 在仪表板中使用 TO 选项的角色或角色下拉菜单。 #

🌐 6. Use role in TO option or roles dropdown in the dashboard.

永远不要只用涉及 auth.uid() 或 auth.jwt() 的 RLS 来排除 'anon' 角色。
总是把 'authenticated' 加到批准的角色里，而不是留空或使用 public。
虽然这不会提升已登录用户的查询性能，但确实能在不增加数据库负担的情况下把 'anon' 用户排除掉。

🌐 Never use RLS involving auth.uid() or auth.jwt() as your only way to rule out 'anon' role. Always add 'authenticated' to the approved roles instead of nothing or public. Although this does not improve the query performance for the signed in user it does eliminate 'anon' users without taxing the database to process the rest of the RLS.

样品结果 #

🌐 Sample results

以下测试使用的代码可以在这里找到：LINK：这些测试是在一个10万行的表上进行选择操作。有些测试还额外连接了一个表。

🌐 The code used for the below tests can be found here: LINK: The tests are doing selects on a 100K row table. Some have an additional join table.

显示上面例子的RLS以及前后情况。

🌐 Show RLS and before after for above examples.

衡量性能的工具 #

🌐 Tools to measure performance

Postgres 有一些工具可以用来分析查询性能。https://www.postgresql.org/docs/current/sql-explain.html
在这里详细使用 explain 来分析查询超出了本次讨论的范围。
我们这里主要用它来获取一个性能指标，以比较耗时。
如果要进行 RLS 测试，你需要设置用户的 JWT 声明，并将运行用户改为 anon 或 authenticated。

🌐 Postgres has tools to analyze the performance of queries. https://www.postgresql.org/docs/current/sql-explain.html The use of explain in detail for query analysis is beyond the scope of this discussion. Here we will use it mainly to get a performance metric to compare times. In order to do RLS testing you need to setup the user JWT claims and change the running user to anon or authenticated.

1
set session role authenticated;
2
set request.jwt.claims to '{"role":"authenticated", "sub":"5950b438-b07c-4012-8190-6ce79e4bd8e5"}';
3
4
explain analyze SELECT count(*) FROM rlstest;
5
set session role postgres;

这将返回类似的结果：

🌐 This will return results like:

1
Seq Scan on rlstest  (cost=0.00..4334.00 rows=1 width=35) (actual time=170.999..170.999 rows=0 loops=1)
2
"  Filter: ((COALESCE(NULLIF(current_setting('request.jwt.claim.sub'::text, true), ''::text), ((NULLIF(current_setting('request.jwt.claims'::text, true), ''::text))::jsonb ->> 'sub'::text)))::uuid = user_id)"
3
  Rows Removed by Filter: 100000
4
Planning Time: 0.216 ms
5
Execution Time: 171.033 ms

在这种情况下，执行时间是我们需要比较的关键数字。

🌐 In this case the execution time is the critical number we need to compare.

PostgREST 允许使用 explain 来获取你在 Supabase 客户端上查询的性能信息。

🌐 PostgREST allows use of explain to get performance information on your queries with Supabase clients.

在使用此功能之前，你需要在仪表板 SQL 编辑器中运行以下命令（不应在生产环境中使用）:

🌐 Before using this feature you need to run the following command in the Dashboard SQL editor (should not be used in production):

1
alter role authenticator set pgrst.db_plan_enabled to true;
2
NOTIFY pgrst, 'reload config';

然后你可以使用 .explain() 修饰符来获取性能指标。

🌐 Then you can use the .explain() modifier to get performance metrics.

1
const { data, error } = await supabase
2
  .from('projects')
3
  .select('*')
4
  .eq('id', 1)
5
  .explain({ analyze: true })
6
7
console.log(data)

这会返回一个类似这样的结果：

🌐 This will return a result like:

1
Aggregate  (cost=8.18..8.20 rows=1 width=112) (actual time=0.017..0.018 rows=1 loops=1)
2
  ->  Index Scan using projects_pkey on projects  (cost=0.15..8.17 rows=1 width=40) (actual time=0.012..0.012 rows=0 loops=1)
3
        Index Cond: (id = 1)
4
        Filter: false
5
        Rows Removed by Filter: 1
6
Planning Time: 0.092 ms
7
Execution Time: 0.046 ms

*这两个 GitHub 讨论涵盖了导致这次分析的历史...

在基本形式下，RLS似乎不支持稳定函数 current_setting在RLS上使用时可能导致性能下降

感谢 Steve Chavez 和 Wolfgang Walther 在那些讨论线程中的分享。

🌐 Thanks Steve Chavez and Wolfgang Walther in those threads.

添加了一个安全定义函数的示例，该函数对团队表进行选择，并与主表中的一列进行比较 #

🌐 Added example of security definer function having select of a team table, comparing against a column in main table

这个例子在测试数据中叫做 test2f。
在这种情况下，我们从一个有 1M 行的表开始，这个表有一个 team_id 列。
我们还有一个 1000 行的 team 表，里面有 user_id 和他们所属的团队。
对一个 select 做基本的 RLS 会是 team_id = ANY(user_teams())。
这种情况会超时，超过 3 分钟，因为必须搜索 1M 行，而且这个函数要对 1000 行每次运行。
改成用函数封装（方法 2）team_id = ANY(ARRAY(select user_teams())) 会有很大提升，但仍然可能需要几秒钟。
给 team_id 加索引才是最大提升，但只有在第二种情况才有效。否则，即便有索引，仍然会超时。

🌐 The example is in the test data as test2f. In this case we start with a 1M row table with a team_id column. We have a 1000 row team table that has user_ids and team(s) they belong too. Basic RLS for a select would be team_id = ANY(user_teams()) This case times out with over 3 minutes as 1M rows must be searched and the function is run each time on 1000 rows. Changing to wrap the function (method 2) team_id = ANY(ARRAY(select user_teams())) is a big improvement but can still take seconds. Adding an index to team_id is the big win, but only with the second case. Without, the index case still times out.

1
CREATE OR REPLACE FUNCTION user_teams()
2
    RETURNS int[] as
3
$$
4
begin
5
    return array( select team_id from team_user where auth.uid() = user_id);
6
end;
7
$$ language plpgsql security definer;

一些结果：

🌐 Some results: