Skip to content
Database

行级安全性能

Measure and tune Postgres Row Level Security policies.

衡量行级安全 (RLS) 的成本，并调整已经正确的策略。想要学习如何编写正确的策略，请参阅 行级安全。

🌐 Measure the cost of Row Level Security (RLS) and tune policies that are already correct. To learn how to write correct policies, see Row Level Security.

Postgres 会对每一行候选数据执行策略表达式，所以查询成本会随着扫描的行数增加而增加。这对那些需要扫描表中每一行的查询尤其重要，比如很多 select 操作，包括使用 limit、offset 和排序的操作。

🌐 Postgres evaluates a policy expression against each candidate row, so the cost scales with the rows a query scans. This matters most for queries that scan every row in a table, like many select operations, including those using limit, offset, and ordering.

有三条政策规则对表现的影响足够大，所以它们应该和政策本身放在一起，而不是放在这里。先应用它们：

🌐 Three policy rules affect performance enough that they belong with the policy itself rather than here. Apply them first:

诊断一下RLS是不是瓶颈 #

🌐 Diagnose whether RLS is the bottleneck

在重写策略之前先确认它们的成本。先在启用 RLS 的情况下运行查询，然后再在禁用 RLS 的情况下运行，进行对比。如果时间差不多，那问题就在查询本身。

🌐 Confirm that policies are the cost before you rewrite one. Run the query with RLS enabled, then again with it disabled, and compare. If the times are similar, the query itself is the problem.

要重现 API 请求，设置 JWT 声明并切换到请求运行时的角色：

🌐 To reproduce an API request, set the JWT claims and switch to the role the request runs as:

set session role authenticated;
set request.jwt.claims to '{"role":"authenticated", "sub":"5950b438-b07c-4012-8190-6ce79e4bd8e5"}';
explain analyze select count(*) from rlstest;
set session role postgres;

输出显示了作为过滤器的策略表达式，执行时间是用来比较的数字：

🌐 The output shows the policy expression as a filter, and the execution time is the number to compare:

Seq Scan on rlstest  (cost=0.00..4334.00 rows=1 width=35) (actual time=170.999..170.999 rows=0 loops=1)
  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)
  Rows Removed by Filter: 100000
Planning Time: 0.216 ms
Execution Time: 171.033 ms

Rows Removed by Filter 是需要关注的信号。一种在每次读取时都会移除大部分表的策略，其过滤列需要一个索引。

通过数据 API 测量 #

🌐 Measure through the Data API

PostgREST 可以向 Supabase 客户端返回查询计划。先启用它:

🌐 PostgREST can return the query plan to a Supabase client. Enable it first:

alter role authenticator set pgrst.db_plan_enabled to true;
notify pgrst, 'reload config';

然后在查询中添加 .explain() 修饰符：

🌐 Then add the .explain() modifier to a query:

const { data, error } = await supabase
  .from('projects')
  .select('*')
  .eq('id', 1)
  .explain({ analyze: true })
console.log(data)
Aggregate  (cost=8.18..8.20 rows=1 width=112) (actual time=0.017..0.018 rows=1 loops=1)
  ->  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)
        Index Cond: (id = 1)
        Filter: false
        Rows Removed by Filter: 1
Planning Time: 0.092 ms
Execution Time: 0.046 ms

在客户端查询中也加上过滤 #

🌐 Filter in the client query too

策略是隐含的 where 条款，所以通常会在没有任何过滤的情况下运行 select 语句。这是一个性能不好的做法。相比之下：

🌐 Policies are implicit where clauses, so it's common to run select statements without any filters. That's a bad pattern for performance. Instead of this:

const { data } = await supabase
  .from('table')
  .select()

总是加个滤镜：

🌐 Always add a filter:

const { data } = await supabase
  .from('table')
  .select()
  .eq('user_id', userId)

即使这会重复策略的内容，Postgres 仍然可以使用这个过滤器来构建更好的查询计划。

🌐 Even though this duplicates the contents of the policy, Postgres can use the filter to construct a better query plan.

在策略表达式中避免使用连接 #

🌐 Avoid joins in policy expressions

你经常可以重写一个策略以避免在源表和目标表之间进行连接。可以改为将目标表中的相关数据取到数组或集合中，然后在过滤器中使用 in 或 any 操作。

🌐 You can often rewrite a policy to avoid a join between the source and the target table. Fetch the relevant data from the target table into an array or set instead, then use an in or any operation in your filter.

这个策略将源 test_table 与目标 team_user 连接起来：

🌐 This policy joins the source test_table to the target team_user:

create policy "rls_test_select" on test_table
to authenticated
using (
  (select auth.uid()) in (
    select user_id
    from team_user
    where team_user.team_id = test_table.team_id -- joins to the source table
  )
);

重写它会将筛选条件选择到一个集合中：

🌐 Rewriting it selects the filter criteria into a set instead:

create policy "rls_test_select" on test_table
to authenticated
using (
  team_id in (
    select team_id
    from team_user
    where user_id = (select auth.uid()) -- no join
  )
);

你也可以使用一个 安全定义函数 来绕过连接表上的 RLS。

🌐 You can also use a security definer function to bypass RLS on the join table.

更多资源 #

🌐 More resources