行级安全

当你需要细粒度的授权规则时，没有什么能比得上 Postgres 的 行级安全 (RLS)。

🌐 When you need granular authorization rules, nothing beats Postgres's Row Level Security (RLS).

Supabase 中的行级安全 #

🌐 Row Level Security in Supabase

1
GRANT SELECT ON <schema_name>.<table_name> TO anon;
2
GRANT SELECT, INSERT, UPDATE, DELETE ON <schema_name>.<table_name> TO authenticated;
3
GRANT SELECT, INSERT, UPDATE, DELETE ON <schema_name>.<table_name> TO service_role;
4
5
alter table <schema_name>.<table_name>
6
enable row level security;

RLS 非常强大且灵活，让你能够编写符合你独特业务需求的复杂 SQL 规则。RLS 可以与 Supabase Auth 结合使用，实现从浏览器到数据库的端到端用户安全。

🌐 RLS is incredibly powerful and flexible, allowing you to write complex SQL rules that fit your unique business needs. RLS can be combined with Supabase Auth for end-to-end user security from the browser to the database.

RLS 是 Postgres 的一个原生功能，即使通过第三方工具访问，也能为你的数据提供“纵深防御”来防止恶意行为者。

政策 #

🌐 Policies

策略 是 Postgres 的规则引擎。一旦你掌握了它们，策略其实很容易理解。每个策略都附加在一个表上，每次访问表的时候，策略都会被执行。

你可以把它们简单地理解为在每个查询中添加一个 WHERE 子句。例如像这样的策略 ...

🌐 You can just think of them as adding a WHERE clause to every query. For example a policy like this ...

1
create policy "Individuals can view their own todos."
2
on todos for select
3
using ( (select auth.uid()) = user_id );

.. 每当用户尝试从 todos 表中选择时，它会翻译成这样:

1
select *
2
from todos
3
where auth.uid() = todos.user_id;
4
-- Policy is implicitly added.

启用行级别安全 #

🌐 Enabling Row Level Security

你可以使用 enable row level security 子句为任何表启用 RLS：

🌐 You can enable RLS for any table using the enable row level security clause:

1
alter table "table_name" enable row level security;

一旦你启用了 RLS，使用可发布密钥时，通过 API 无法访问任何数据，除非你创建了策略。

🌐 Once you have enabled RLS, no data will be accessible via the API when using a publishable key, until you create policies.

自动为新表启用 RLS #

🌐 Auto-enable RLS for new tables

如果你想让 RLS 在新表上自动启用，你可以创建一个在表创建后触发的事件触发器。这使用 Postgres 的 事件触发器 来对每个新创建的表调用 ALTER TABLE ... ENABLE ROW LEVEL SECURITY。

🌐 If you want RLS enabled automatically for new tables, you can create an event trigger that runs after table creation. This uses a Postgres event trigger to call ALTER TABLE ... ENABLE ROW LEVEL SECURITY on each newly created table.

1
CREATE OR REPLACE FUNCTION rls_auto_enable()
2
RETURNS EVENT_TRIGGER
3
LANGUAGE plpgsql
4
SECURITY DEFINER
5
SET search_path = pg_catalog
6
AS $$
7
DECLARE
8
  cmd record;
9
BEGIN
10
  FOR cmd IN
11
    SELECT *
12
    FROM pg_event_trigger_ddl_commands()
13
    WHERE command_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
14
      AND object_type IN ('table','partitioned table')
15
  LOOP
16
     IF cmd.schema_name IS NOT NULL AND cmd.schema_name IN ('public') AND cmd.schema_name NOT IN ('pg_catalog','information_schema') AND cmd.schema_name NOT LIKE 'pg_toast%' AND cmd.schema_name NOT LIKE 'pg_temp%' THEN
17
      BEGIN
18
        EXECUTE format('alter table if exists %s enable row level security', cmd.object_identity);
19
        RAISE LOG 'rls_auto_enable: enabled RLS on %', cmd.object_identity;
20
      EXCEPTION
21
        WHEN OTHERS THEN
22
          RAISE LOG 'rls_auto_enable: failed to enable RLS on %', cmd.object_identity;
23
      END;
24
     ELSE
25
        RAISE LOG 'rls_auto_enable: skip % (either system schema or not in enforced list: %.)', cmd.object_identity, cmd.schema_name;
26
     END IF;
27
  END LOOP;
28
END;
29
$$;
30
31
DROP EVENT TRIGGER IF EXISTS ensure_rls;
32
CREATE EVENT TRIGGER ensure_rls
33
ON ddl_command_end
34
WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
35
EXECUTE FUNCTION rls_auto_enable();

注意，这只适用于触发器安装后创建的表。已有的表仍然需要手动启用 RLS。

🌐 Note that this applies to tables created after the trigger is installed. Existing tables still need RLS enabled manually.

1
USING (auth.uid() = user_id)

1
null = user_id

1
USING (auth.uid() IS NOT NULL AND auth.uid() = user_id)

已认证和未认证角色 #

🌐 Authenticated and unauthenticated roles

Supabase 会将每个请求映射到以下角色之一：

🌐 Supabase maps every request to one of the roles:

• anon：未经验证的请求（用户未登录）
• authenticated：已验证的请求（用户已登录）

这些是 Postgres 角色。你可以在策略中使用 TO 子句来使用这些角色：

🌐 These are Postgres Roles. You can use these roles within your Policies using the TO clause:

1
create policy "Profiles are viewable by everyone"
2
on profiles for select
3
to authenticated, anon
4
using ( true );
5
6
-- OR
7
8
create policy "Public profiles are viewable only by authenticated users"
9
on profiles for select
10
to authenticated
11
using ( true );

制定政策 #

🌐 Creating policies

策略是你附加到 Postgres 表上的 SQL 逻辑。你可以给每个表附加任意多的策略。

🌐 Policies are SQL logic that you attach to a Postgres table. You can attach as many policies as you want to each table.

如果你使用 Supabase Auth，Supabase 提供了一些可以简化 RLS 的 辅助工具。我们将使用这些辅助工具来演示一些基本策略：

🌐 Supabase provides some helpers that simplify RLS if you're using Supabase Auth. We'll use these helpers to illustrate some basic policies:

选择政策 #

🌐 SELECT policies

你可以用 using 条款指定选择的政策。

🌐 You can specify select policies with the using clause.

假设你有一个叫 profiles 的表在 public schema 中，并且你想让所有人都能读取它。

🌐 Say you have a table called profiles in the public schema and you want to enable read access to everyone.

1
-- 1. Create table
2
create table profiles (
3
  id uuid primary key,
4
  user_id uuid references auth.users,
5
  avatar_url text
6
);
7
8
-- 2. Enable RLS
9
alter table profiles enable row level security;
10
11
-- 3. Create Policy
12
create policy "Public profiles are visible to everyone."
13
on profiles for select
14
to anon         -- the Postgres Role (recommended)
15
using ( true ); -- the actual Policy

或者，如果你只想让用户能够看到自己的个人资料：

🌐 Alternatively, if you only wanted users to be able to see their own profiles:

1
create policy "User can see their own profile only."
2
on profiles
3
for select using ( (select auth.uid()) = user_id );

插入政策 #

🌐 INSERT policies

你可以使用 with check 子句指定插入策略。with check 表达式确保任何新行数据都符合策略约束。

🌐 You can specify insert policies with the with check clause. The with check expression ensures that any new row data adheres to the policy constraints.

假设你有一个名为 profiles 的表在公共模式中，而且你只希望用户自己创建个人档案。在这种情况下，我们想要检查他们的用户ID是否与他们试图插入的值匹配：

🌐 Say you have a table called profiles in the public schema and you only want users to create a profile for themselves. In that case, we want to check their User ID matches the value that they are trying to insert:

1
-- 1. Create table
2
create table profiles (
3
  id uuid primary key,
4
  user_id uuid references auth.users,
5
  avatar_url text
6
);
7
8
-- 2. Enable RLS
9
alter table profiles enable row level security;
10
11
-- 3. Create Policy
12
create policy "Users can create a profile."
13
on profiles for insert
14
to authenticated                          -- the Postgres Role (recommended)
15
with check ( (select auth.uid()) = user_id );      -- the actual Policy

更新政策 #

🌐 UPDATE policies

你可以通过结合使用 using 和 with check 表达式来指定更新策略。

🌐 You can specify update policies by combining both the using and with check expressions.

using 条款表示必须为真的条件才能允许更新，而 with check 条款确保所做的更新符合策略约束。

🌐 The using clause represents the condition that must be true for the update to be allowed, and with check clause ensures that the updates made adhere to the policy constraints.

假设你有一个叫 profiles 的表在公共 schema 中，而且你只想让用户更新自己的资料。

🌐 Say you have a table called profiles in the public schema and you only want users to update their own profile.

你可以创建一个策略，其中 using 条款用来检查用户是否拥有正在更新的个人资料。而 with check 条款确保在生成的行中，用户不会将 user_id 改成不等于他们用户 ID 的值，从而保持修改后的个人资料仍然符合所有权条件。

🌐 You can create a policy where the using clause checks if the user owns the profile being updated. And the with check clause ensures that, in the resultant row, users do not change the user_id to a value that is not equal to their User ID, maintaining that the modified profile still meets the ownership condition.

1
-- 1. Create table
2
create table profiles (
3
  id uuid primary key,
4
  user_id uuid references auth.users,
5
  avatar_url text
6
);
7
8
-- 2. Enable RLS
9
alter table profiles enable row level security;
10
11
-- 3. Create Policy
12
create policy "Users can update their own profile."
13
on profiles for update
14
to authenticated                    -- the Postgres Role (recommended)
15
using ( (select auth.uid()) = user_id )       -- checks if the existing row complies with the policy expression
16
with check ( (select auth.uid()) = user_id ); -- checks if the new row complies with the policy expression

如果没有定义 with check 表达式，那么 using 表达式将用于确定哪些行是可见的（普通 USING 情况）以及允许添加哪些新行（WITH CHECK 情况）。

🌐 If no with check expression is defined, then the using expression will be used both to determine which rows are visible (normal USING case) and which new rows will be allowed to be added (WITH CHECK case).

删除政策 #

🌐 DELETE policies

你可以用 using 条款来指定删除策略。

🌐 You can specify delete policies with the using clause.

假设你有一个叫 profiles 的表在公共模式中，并且你只希望用户能够删除自己的个人资料：

🌐 Say you have a table called profiles in the public schema and you only want users to be able to delete their own profile:

1
-- 1. Create table
2
create table profiles (
3
  id uuid primary key,
4
  user_id uuid references auth.users,
5
  avatar_url text
6
);
7
8
-- 2. Enable RLS
9
alter table profiles enable row level security;
10
11
-- 3. Create Policy
12
create policy "Users can delete a profile."
13
on profiles for delete
14
to authenticated                     -- the Postgres Role (recommended)
15
using ( (select auth.uid()) = user_id );      -- the actual Policy

浏览量 #

🌐 Views

视图默认会绕过 RLS，因为它们通常是用 postgres 用户创建的。这是 Postgres 的一个特性，它会自动用 security definer 创建视图。

🌐 Views bypass RLS by default because they are usually created with the postgres user. This is a feature of Postgres, which automatically creates views with security definer.

在 Postgres 15 及以上版本中，你可以通过设置 security_invoker = true，让在 anon 和 authenticated 角色调用时，视图遵守底层表的 RLS 策略。

🌐 In Postgres 15 and above, you can make a view obey the RLS policies of the underlying tables when invoked by anon and authenticated roles by setting security_invoker = true.

1
create view <VIEW_NAME>
2
with(security_invoker = true)
3
as select <QUERY>

在较老版本的 Postgres 中，可以通过撤销 anon 和 authenticated 角色的访问权限，或者将它们放在一个不公开的 schema 中来保护你的视图。

🌐 In older versions of Postgres, protect your views by revoking access from the anon and authenticated roles, or by putting them in an unexposed schema.

辅助函数 #

🌐 Helper functions

Supabase 提供了一些辅助函数，让写策略更容易。

🌐 Supabase provides some helper functions that make it easier to write Policies.

auth.uid()#

返回发起请求的用户ID。

🌐 Returns the ID of the user making the request.

auth.jwt()#

返回发出请求的用户的 JWT。你存储在用户的 raw_app_meta_data 列或 raw_user_meta_data 列的任何内容都可以通过这个函数访问。了解这两者之间的区别很重要：

🌐 Returns the JWT of the user making the request. Anything that you store in the user's raw_app_meta_data column or the raw_user_meta_data column will be accessible using this function. It's important to know the distinction between these two:

• raw_user_meta_data - 可以由已认证的用户使用 supabase.auth.update() 功能更新。这里不是存储授权数据的好地方。
• raw_app_meta_data - 无法由用户更新，所以这是存储授权数据的好地方。

auth.jwt() 函数非常多用途。例如，如果你将一些团队数据存储在 app_metadata 里，你可以用它来判断某个用户是否属于某个团队。例如，如果这是一个 ID 数组：

🌐 The auth.jwt() function is extremely versatile. For example, if you store some team data inside app_metadata, you can use it to determine whether a particular user belongs to a team. For example, if this was an array of IDs:

1
create policy "User is in team"
2
on my_table
3
to authenticated
4
using ( team_id in (select auth.jwt() -> 'app_metadata' -> 'teams'));

多重身份验证 #

🌐 MFA

auth.jwt() 函数可以用来检查 多因素认证。例如，你可以限制用户在没有至少两级认证（保证级别 2）的情况下更新他们的个人资料：

🌐 The auth.jwt() function can be used to check for Multi-Factor Authentication. For example, you could restrict a user from updating their profile unless they have at least 2 levels of authentication (Assurance Level 2):

1
create policy "Restrict updates."
2
on profiles
3
as restrictive
4
for update
5
to authenticated using (
6
  (select auth.jwt()->>'aal') = 'aal2'
7
);

绕过行级别安全 #

🌐 Bypassing Row Level Security

Supabase 提供了特殊的“服务”密钥，可以用来绕过 RLS。它们绝不应该在浏览器中使用或暴露给客户，但对管理任务很有用。

🌐 Supabase provides special "Service" keys, which can be used to bypass RLS. These should never be used in the browser or exposed to customers, but they are useful for administrative tasks.

你也可以创建新的 Postgres 角色，通过使用“bypass RLS”权限来绕过行级安全性：

🌐 You can also create new Postgres Roles which can bypass Row Level Security using the "bypass RLS" privilege:

1
alter role "role_name" with bypassrls;

这对于系统级访问可能很有用。你绝对不应该与任何拥有此权限的 Postgres 角色分享登录凭据。

🌐 This can be useful for system-level access. You should never share login credentials for any Postgres Role with this privilege.

RLS 性能建议 #

🌐 RLS performance recommendations

每个授权系统都会对性能产生影响。虽然行级安全很强大，但性能影响是需要注意的。对于扫描表中每一行的查询尤其如此——比如很多 select 操作，包括使用 limit、offset 和排序的操作。

🌐 Every authorization system has an impact on performance. While row level security is powerful, the performance impact is important to keep in mind. This is especially true for queries that scan every row in a table - like many select operations, including those using limit, offset, and ordering.

根据一系列测试，我们对RLS有一些建议：

🌐 Based on a series of tests, we have a few recommendations for RLS:

添加索引 #

🌐 Add indexes

确保你已经在 Policies 中使用但还没有索引（或主键）的列上添加了索引。对于像这样的 Policy：

🌐 Make sure you've added indexes on any columns used within the Policies which are not already indexed (or primary keys). For a Policy like this:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using ( (select auth.uid()) = user_id );

你可以像这样添加索引：

🌐 You can add an index like:

1
create index userid
2
on test_table
3
using btree (user_id);

基准 #

🌐 Benchmarks

用 select#

🌐 Call functions with select

你可以使用 select 语句来改进使用函数的策略。例如，不是这样：

🌐 You can use select statement to improve policies that use functions. For example, instead of this:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using ( auth.uid() = user_id );

你可以这样做：

🌐 You can do:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using ( (select auth.uid()) = user_id );

这种方法对于像 auth.uid() 和 auth.jwt() 这样的 JWT 函数以及 security definer 函数都很有效。将函数封装起来会让 Postgres 优化器运行一个 initPlan，这样它就能在每个语句上“缓存”结果，而不是对每一行都调用函数。

🌐 This method works well for JWT functions like auth.uid() and auth.jwt() as well as security definer Functions. Wrapping the function causes an initPlan to be run by the Postgres optimizer, which allows it to "cache" the results per-statement, rather than calling the function on each row.

基准 #

🌐 Benchmarks

给每个查询添加过滤器 #

🌐 Add filters to every query

策略是“隐式的 where 子句”，所以通常会在没有任何过滤条件的情况下运行 select 语句。这是一种性能很差的做法。不要这么做（JS 客户端示例）:

🌐 Policies are "implicit where clauses," so it's common to run select statements without any filters. This is a bad pattern for performance. Instead of doing this (JS client example):

1
const { data } = supabase
2
  .from('table')
3
  .select()

你应该总是加上一个滤镜：

🌐 You should always add a filter:

1
const { data } = supabase
2
  .from('table')
3
  .select()
4
  .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.

基准 #

🌐 Benchmarks

使用安全定义者函数 #

🌐 Use security definer functions

“安全定义者”函数会使用 创建 该函数的角色来运行。这意味着，如果你用超级用户（比如 postgres）创建了一个角色，那么该函数将拥有 bypassrls 的权限。例如，如果你有这样的策略：

🌐 A "security definer" function runs using the same role that created the function. This means that if you create a role with a superuser (like postgres), then that function will have bypassrls privileges. For example, if you had a policy like this:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using (
4
  exists (
5
    select 1 from roles_table
6
    where (select auth.uid()) = user_id and role = 'good_role'
7
  )
8
);

我们可以改为创建一个 security definer 函数，它可以扫描 roles_table，而不会有任何 RLS 惩罚：

🌐 We can instead create a security definer function which can scan roles_table without any RLS penalties:

1
create function private.has_good_role()
2
returns boolean
3
language plpgsql
4
security definer -- will run as the creator
5
as $$
6
begin
7
  return exists (
8
    select 1 from roles_table
9
    where (select auth.uid()) = user_id and role = 'good_role'
10
  );
11
end;
12
$$;
13
14
-- Update our policy to use this function:
15
create policy "rls_test_select"
16
on test_table
17
to authenticated
18
using ( (select private.has_good_role()) );

尽量少用连接 #

🌐 Minimize joins

你通常可以重写你的策略来避免源表和目标表之间的连接。相反，试着将你的策略组织成从目标表中获取所有相关数据到一个数组或集合中，然后你就可以在过滤器中使用 IN 或 ANY 操作。

🌐 You can often rewrite your Policies to avoid joins between the source and the target table. Instead, try to organize your policy to fetch all the relevant data from the target table into an array or set, then you can use an IN or ANY operation in your filter.

例如，这是一个将源 test_table 与目标 team_user 连接的慢策略示例：

🌐 For example, this is an example of a slow policy which joins the source test_table to the target team_user:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using (
4
  (select auth.uid()) in (
5
    select user_id
6
    from team_user
7
    where team_user.team_id = team_id -- joins to the source "test_table.team_id"
8
  )
9
);

我们可以改写这个来避免这个连接，而是将筛选条件选入一个集合：

🌐 We can rewrite this to avoid this join, and instead select the filter criteria into a set:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using (
4
  team_id in (
5
    select team_id
6
    from team_user
7
    where user_id = (select auth.uid()) -- no join
8
  )
9
);

在这种情况下，你也可以考虑使用 security definer 函数来绕过连接表上的 RLS：

🌐 In this case you can also consider using a security definer function to bypass RLS on the join table:

基准 #

🌐 Benchmarks

在你的政策中指定角色 #

🌐 Specify roles in your policies

在你的策略中始终使用由 TO 操作符指定的角色。例如，而不是这个查询：

🌐 Always use the Role of inside your policies, specified by the TO operator. For example, instead of this query:

1
create policy "rls_test_select" on rls_test
2
using ( auth.uid() = user_id );

用法：

🌐 Use:

1
create policy "rls_test_select" on rls_test
2
to authenticated
3
using ( (select auth.uid()) = user_id );

这会阻止策略 ( (select auth.uid()) = user_id ) 对任何 anon 用户运行，因为执行会在 to authenticated 步骤停止。

🌐 This prevents the policy ( (select auth.uid()) = user_id ) from running for any anon users, since the execution stops at the to authenticated step.

基准 #

🌐 Benchmarks

更多资源 #

🌐 More resources

• 测试你的数据库
• RLS 指南和最佳实践
• 社区仓库，用于测试 RLS，使用 pgTAP 和 dbdev