Skip to content
Database

行级安全

Secure your data using Postgres Row Level Security.

Postgres 行级安全 (RLS) 给你在数据库内部运行的精细授权规则。

🌐 Postgres Row Level Security (RLS) gives you granular authorization rules that run inside the database.

使用这个三部分的指南：

🌐 Use the guide in three parts:

  • 了解行级安全 解释了授权和策略如何结合起来控制访问。
  • 使用 RLS 保护表 是对暴露架构中的每个表都应遵循的流程：权限、策略，以及 supabase test db。
  • RLS 参考 文档记录了你在策略表达式中使用的辅助函数和模式。

在决定如何建模访问时先读第一部分。准备好保护表时直接跳到第二部分。

🌐 Read the first section when you're deciding how to model access. Go directly to the second section when you're ready to secure a table.

了解行级安全 #

🌐 Understand Row Level Security

政策的作用 #

🌐 What a policy does

策略 是 Postgres 的规则引擎。每个策略都附加在一个表上，每次访问表时都会执行该策略。

可以把策略想象成在每个查询中添加一个 WHERE 条款。像这样的策略：

🌐 Think of a policy as adding a WHERE clause to every query. A policy like this:

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

每当用户从待办事项表中选择时，该策略就会变成这样：

🌐 That policy translates to this whenever a user selects from the todos table:

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

你可以用 SQL 写 RLS 规则，这样规则就能表达你应用所需的各种访问逻辑。因为 RLS 是 Postgres 的原生功能，它还能在通过第三方工具访问数据时保护你的数据，这也就是它所谓的“纵深防御”。把 RLS 和 Supabase Auth 结合起来，就能实现从浏览器到数据库的端到端用户安全。

补助和政策 #

🌐 Grants and policies

在客户端操作表之前，Postgres 会先进行两次检查。权限决定一个角色是否可以在表上执行某个操作。策略决定该操作适用于哪些行。对你公开的每张表都设置这两者。

🌐 Postgres runs two checks before a client touches a table. Grants decide whether a role can run an operation on the table at all. Policies decide which rows that operation applies to. Set both for every table you expose.

在现有项目中，public 中的新表一开始就已经将所有权限授予给三个角色：

🌐 On existing projects, a new table in public starts with every privilege already granted to all three roles:

角色自动授予应该保留的内容
anonselect、insert、update、delete仅限未登录访客可以阅读的内容
authenticatedselect、insert、update、delete仅限你的应用向已登录用户开放的操作
service_roleselect、insert、update、delete完全访问。它绕过了 RLS，所以要保存在服务器端

添加策略并不会收回那些授权。如果一个表仅由策略保护，而你从未撤销授权，anon 仍然可以插入数据。

🌐 Adding policies doesn't take those grants back. A table protected only by policies still hands anon an insert path if you never revoke the grant.

并非每个项目都会自动授予这些权限。请参见 默认权限。只给每个角色它所需的操作权限。

🌐 Not every project grants these automatically. See Default privileges. Grant each role only the operations it needs.

在任何策略运行之前，缺失的权限会引发 42501 错误。当一个请求本应被你的策略允许但失败时，在修改策略之前先检查权限。要设置权限，请参见 启用 RLS 并设置权限。

🌐 A missing grant raises a 42501 error before any policy runs. When a request fails that your policy should allow, check the grants before you change the policy. To set them, see Enable RLS and set the grants.

已认证和未认证角色 #

🌐 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:

create policy "Profiles are viewable by everyone"
on profiles for select
to authenticated, anon
using ( true );
-- OR
create policy "Public profiles are viewable only by authenticated users"
on profiles for select
to authenticated
using ( true );

一条写着 to anon using ( true ) 的策略会授予每个未认证的访问者对该角色已通过授权可以访问的每一行的读取权限。仅将其用于公开的数据。

🌐 A policy that reads to anon using ( true ) grants every unauthenticated visitor read access to every row the role can already reach through grants. Use it only for data that is meant to be public.

视图和行级安全 #

🌐 Views and RLS

视图默认绕过 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. A view over a protected table hands out every row its policies were meant to withhold, so a view needs the same attention as a table. To create one safely, see Expose a view safely.

用 RLS 把表格锁住 #

🌐 Secure a table with RLS

对每个公开的模式里的表都按这些步骤操作：

🌐 Follow these steps for every table in an exposed schema:

  1. 启用 RLS 并设置权限以匹配应用。
  2. 为每个操作编写一条政策。
  3. 在 supabase/tests/ 下创建一个 .sql 文件，设置 select、insert、update 和 delete 对 anon 和 authenticated 的允许和拒绝。
  4. 运行 [Run supabase test db](#run-the-test-suite) 并修复它报告的问题。

在测试套件通过之前，你不知道这些策略是否达到了你的预期效果。

🌐 Until the suite passes, you don't know whether the policies do what you intended.

启用 RLS 并设置权限 #

🌐 Enable RLS and set the grants

在 SQL 编辑器 中运行这些语句以进行一次性更改，或者在 迁移 中运行以确保更改可在各个环境中重复执行。授权和 RLS 应该放在同一个迁移中。

🌐 Run these statements in the SQL Editor for a one-off change, or in a migration to keep the change reproducible across environments. Grants and RLS belong in the same migration.

启用 RLS，然后根据每个角色在你的应用中的功能设置权限：

🌐 Enable RLS, then set the grants to match what each role does in your app:

  1. 在表上启用 RLS。

    alter table public.reports enable row level security;

    一旦启用 RLS，在使用可发布密钥时，通过 API 无法访问任何数据，直到你创建策略为止。

  2. 撤销两个客户端角色的所有现有授权。

    revoke all on table public.reports from anon, authenticated;

  3. 只回授角色所需的权限。

    -- Signed-in users manage reports. Signed-out visitors get nothing.
    grant select, insert, update, delete on table public.reports to authenticated;

  4. 为这个表写测试文件，然后运行测试套件。

    supabase test new reports_rls.test
    supabase test db

    给你确保的每张桌子一个。下面的例子展示了文件里放的内容。

客户端只读取但从不写入的数据，例如后端作业填充的 feed，只能选择读取。策略和测试文件属于同一个变更的一部分：

🌐 Data that clients read but never write, such as a feed a backend job populates, gets select only. The policy and the test file are part of the same change:

alter table public.announcements enable row level security;
revoke all on table public.announcements from anon, authenticated;
grant select on table public.announcements to anon, authenticated;
create policy "Anyone can read announcements"
on public.announcements for select
to anon, authenticated
using ( true );
-- File: supabase/tests/announcements_rls.test.sql
-- Create: supabase test new announcements_rls.test
-- Run:    supabase test db
-- Repeat for every public-read table you secured.
begin;
select plan(10);
insert into announcements (id, body)
values ('33333333-3333-3333-3333-333333333333', 'published');
-- A read has to return the row. lives_ok passes on an empty result.
select ok(
  not has_table_privilege('anon', 'public.announcements', 'insert,update,delete'),
  'anon holds no write grant on the feed'
);
select ok(
  not has_table_privilege('authenticated', 'public.announcements', 'insert,update,delete'),
  'authenticated holds no write grant on the feed'
);
set local role anon;
select results_eq(
  $$select body from announcements where id = '33333333-3333-3333-3333-333333333333'$$,
  array['published'],
  'anon reads the feed'
);
select throws_ok(
  $$insert into announcements select * from announcements$$,
  '42501',
  null,
  'anon cannot insert into the feed'
);
select throws_ok(
  $$update announcements set id = id$$,
  '42501',
  null,
  'anon cannot update the feed'
);
select throws_ok(
  $$delete from announcements$$,
  '42501',
  null,
  'anon cannot delete from the feed'
);
set local role authenticated;
select results_eq(
  $$select body from announcements where id = '33333333-3333-3333-3333-333333333333'$$,
  array['published'],
  'authenticated reads the feed'
);
select throws_ok(
  $$insert into announcements select * from announcements$$,
  '42501',
  null,
  'authenticated cannot insert into the feed'
);
select throws_ok(
  $$update announcements set id = id$$,
  '42501',
  null,
  'authenticated cannot update the feed'
);
select throws_ok(
  $$delete from announcements$$,
  '42501',
  null,
  'authenticated cannot delete from the feed'
);
select * from finish();
rollback;

如果新表仍然会自动获得权限，请参见 撤销默认权限。要在每个新表上自动启用 RLS，请参见 事件触发器。

🌐 If new tables still receive automatic grants, see Revoke default privileges. To enable RLS automatically on every new table, see Event triggers.

为每个操作制定一条政策 #

🌐 Write a policy for each operation

为 select、insert、update 和 delete 编写单独的策略。Postgres 不接受在一个 for 子句中执行多个操作，而且一个 for all 策略会隐藏每条规则原本打算覆盖的操作。

🌐 Write a separate policy for select, insert, update, and delete. Postgres does not accept multiple operations in one for clause, and a for all policy hides which operation each rule was meant to cover.

这些例子使用了一个 profiles 表，每个用户只管理自己的那一行：

🌐 These examples use a profiles table where each user manages only their own row:

create table profiles (
  id uuid primary key,
  user_id uuid references auth.users,
  avatar_url text
);
alter table profiles enable row level security;
revoke all on table profiles from anon, authenticated;
grant select, insert, update, delete on table profiles to authenticated;

Supabase 提供了 辅助函数，如果你在使用 Supabase Auth，这些函数可以简化 RLS。auth.uid() 会返回发起请求的用户 ID。

🌐 Supabase provides helper functions that simplify RLS if you are using Supabase Auth. auth.uid() returns the ID of the user making the request.

选择政策 #

🌐 SELECT policies

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

🌐 You can specify select policies with the using clause.

create policy "Users can view their own profile."
on profiles for select
to authenticated
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 adheres to the policy constraints, so a user cannot create a row that belongs to someone else.

create policy "Users can create their own profile."
on profiles for insert
to authenticated
with check ( (select auth.uid()) = user_id );

更新政策 #

🌐 UPDATE policies

你可以通过组合 using 和 with check 表达式来指定更新策略。using 子句决定哪些现有行可以被更新。with check 子句决定结果行允许的样子，这可以阻止用户把 user_id 分配给别人。

🌐 You can specify update policies by combining the using and with check expressions. The using clause decides which existing rows can be updated. The with check clause decides what the resulting row is allowed to look like, which stops a user from reassigning user_id to someone else.

create policy "Users can update their own profile."
on profiles for update
to authenticated
using ( (select auth.uid()) = user_id )       -- checks the existing row
with check ( (select auth.uid()) = user_id ); -- checks the resulting row

如果没有定义 with check 表达式，using 表达式将决定哪些行是可见的以及允许哪些新行。

🌐 If no with check expression is defined, the using expression decides both which rows are visible and which new rows are allowed.

删除政策 #

🌐 DELETE policies

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

🌐 You can specify delete policies with the using clause.

create policy "Users can delete their own profile."
on profiles for delete
to authenticated
using ( (select auth.uid()) = user_id );

政策测试 #

🌐 Policy tests

当你把那四条政策应用到一个表格时，把这个文件加到那个表格里。路径是 supabase/tests/<table>_rls.test.sql。对于用户共享的表格，还要确认非拥有者的成员可以执行其政策允许的操作，而非成员则不行。

-- File: supabase/tests/profiles_rls.test.sql
-- Create: supabase test new profiles_rls.test
-- Run:    supabase test db
-- One file per table you enabled RLS on. Name that table in the assertions.
-- Do not create a table only the tests use.
begin;
select plan(14);
insert into auth.users (id, email)
values
  ('11111111-1111-1111-1111-111111111111', 'owner@example.com'),
  ('22222222-2222-2222-2222-222222222222', 'other@example.com');
-- anon holds no grant, so the request stops before any policy runs.
set local role anon;
select throws_ok(
  $$select * from profiles$$,
  '42501',
  null,
  'anon cannot read profiles'
);
select throws_ok(
  $$insert into profiles (id, user_id, avatar_url)
    values (
      gen_random_uuid(),
      '11111111-1111-1111-1111-111111111111',
      'anon.png'
    )$$,
  '42501',
  null,
  'anon cannot create a profile'
);
select throws_ok(
  $$update profiles set avatar_url = 'anon.png'$$,
  '42501',
  null,
  'anon cannot update profiles'
);
select throws_ok(
  $$delete from profiles$$,
  '42501',
  null,
  'anon cannot delete profiles'
);
-- The owner writes their own row. returning proves the row changed.
set local role authenticated;
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
select results_eq(
  $$insert into profiles (id, user_id, avatar_url)
    values (
      gen_random_uuid(),
      '11111111-1111-1111-1111-111111111111',
      'owner.png'
    )
    returning avatar_url$$,
  array['owner.png'],
  'the owner creates their own profile'
);
select results_eq(
  $$select avatar_url from profiles where user_id = '11111111-1111-1111-1111-111111111111'$$,
  array['owner.png'],
  'the owner reads their own profile'
);
select results_eq(
  $$update profiles set avatar_url = 'updated.png'
    where user_id = '11111111-1111-1111-1111-111111111111'
    returning avatar_url$$,
  array['updated.png'],
  'the owner updates their own profile'
);
-- A signed-in stranger holds the grant, so the policy is what stops them.
set local request.jwt.claim.sub = '22222222-2222-2222-2222-222222222222';
select throws_ok(
  $$insert into profiles (id, user_id, avatar_url)
    values (
      gen_random_uuid(),
      '11111111-1111-1111-1111-111111111111',
      'stolen.png'
    )$$,
  '42501',
  null,
  'another user cannot create a profile for the owner'
);
select is_empty(
  $$select * from profiles$$,
  'another user reads no profiles'
);
select is_empty(
  $$update profiles set avatar_url = 'stolen.png' returning avatar_url$$,
  'another user updates no profiles'
);
-- Matching no rows is not proof on its own. Pair every denied write with a
-- check that the row it targeted is intact. Scope it to that row: a suite
-- that asserts on everything a role can see breaks once the table holds more.
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
select results_eq(
  $$select avatar_url from profiles where user_id = '11111111-1111-1111-1111-111111111111'$$,
  array['updated.png'],
  'the denied update left the owner row intact'
);
set local request.jwt.claim.sub = '22222222-2222-2222-2222-222222222222';
select is_empty(
  $$delete from profiles returning avatar_url$$,
  'another user deletes no profiles'
);
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
select results_eq(
  $$select avatar_url from profiles where user_id = '11111111-1111-1111-1111-111111111111'$$,
  array['updated.png'],
  'the denied delete left the owner row intact'
);
-- Owner delete last so earlier cases still have a row to assert against.
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
select results_eq(
  $$delete from profiles where user_id = '11111111-1111-1111-1111-111111111111'
    returning avatar_url$$,
  array['updated.png'],
  'the owner deletes their own profile'
);
select * from finish();
rollback;

将声明与请求被拒绝的方式匹配。三者中只有两个会引发错误：

🌐 Match the assertion to how the request is denied. Only two of the three raise an error:

拒绝者Postgres断言与
缺少授权抛出 42501throws_ok
with check 违反抛出 42501throws_ok
using 子句将行过滤掉不抛出任何错误，匹配零行对带有 returning 的语句执行 is_empty，然后读取以证明目标行完好

永远不要用 lives_ok 来验证允许的写入。当写入匹配零行时，它会通过。添加 returning 并断言返回值。

🌐 Never prove an allowed write with lives_ok. It passes when the write matched zero rows. Add returning and assert the returned value.

和 set local role 与 set local request.jwt.claim.sub 交换角色和身份。

🌐 Switch role and identity with set local role and set local request.jwt.claim.sub.

在你的政策中指定角色 #

🌐 Specify roles in your policies

总是用 to 条款来命名策略适用的角色。不要这样做：

🌐 Always name the role a policy applies to, using the to clause. Instead of this:

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

用法：

🌐 Use:

create policy "rls_test_select" on rls_test
to authenticated
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.

运行测试套件 #

🌐 Run the test suite

上面的文件位于 supabase/tests/ 下，并通过 pgTAP 运行。用 supabase test new <table>_rls.test 创建它们，然后用 supabase test db 运行它们。

supabase-test-helpers 添加了 tests.create_supabase_user()、tests.authenticate_as() 和 tests.rls_enabled()。查看 高级 pgTAP 测试 和 测试你的数据库。

添加索引 #

🌐 Add indexes

在你的策略过滤的每一列上添加一个 索引。Postgres 会针对每一行候选数据评估策略，所以没有索引的过滤列会把一次读取变成顺序扫描。对于这样的策略：

🌐 Add an index on every column your policies filter on. Postgres evaluates the policy against each candidate row, so an unindexed filter column turns a read into a sequential scan. For a policy like this:

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

你可以像这样添加索引：

🌐 You can add an index like:

create index userid
on test_table
using btree (user_id);

一列只有在它位于 btree 索引的第一位时才算作被索引。Postgres 不能用多列索引去过滤不是首列的列，所以复合主键只对它的第一列建立索引，而不对其他列建立索引。一个以 (team_id, user_id) 为键的成员表在 user_id 上没有索引：

🌐 A column counts as indexed only when it comes first in a btree index. Postgres can't use a multi-column index to filter on a column that isn't the leading one, so a composite primary key indexes its first column and no others. A membership table keyed on (team_id, user_id) has no index on user_id:

create table team_members (
  team_id uuid references teams (id),
  user_id uuid references auth.users (id),
  primary key (team_id, user_id)
);
-- The primary key covers team_id. A policy filtering on user_id needs its own index.
create index team_members_user_id_idx
on team_members
using btree (user_id);

用 select#

🌐 Call functions with select

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

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

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

你可以这样做：

🌐 You can do:

create policy "rls_test_select" on test_table
to authenticated
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.

对过滤列建立索引并封装辅助调用可以让策略在表增长时依旧保持快速。关于实际影响以及在这两条规则之外的调优，请参见 行级安全性能。

🌐 Indexing the filter columns and wrapping helper calls keeps policies fast as a table grows. For the measured impact, and for tuning beyond these two rules, see Row Level Security performance.

安全地暴露视图 #

🌐 Expose a view safely

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

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

create view <VIEW_NAME>
with(security_invoker = true)
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.

RLS 参考 #

🌐 RLS reference

这些是在策略表达式中可用的函数和模式。

🌐 These are the functions and patterns available inside a policy expression.

辅助函数 #

🌐 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:

create policy "User is in team"
on my_table
to authenticated
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):

create policy "Restrict updates."
on profiles
as restrictive
for update
to authenticated using (
  (select auth.jwt()->>'aal') = 'aal2'
);

使用安全定义者函数 #

🌐 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:

create policy "rls_test_select" on test_table
to authenticated
using (
  exists (
    select 1 from roles_table
    where (select auth.uid()) = user_id and role = 'good_role'
  )
);

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

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

create function private.has_good_role()
returns boolean
language plpgsql
security definer -- will run as the creator
set search_path = '' -- every name inside must be schema-qualified
as $$
begin
  return exists (
    select 1 from public.roles_table
    where (select auth.uid()) = user_id and role = 'good_role'
  );
end;
$$;
-- Update our policy to use this function:
create policy "rls_test_select"
on test_table
to authenticated
using ( (select private.has_good_role()) );

在那个表格的 supabase/tests/ 文件中添加成员和非成员的案例。非所有者的成员必须允许；非成员则不允许。

🌐 Add member and non-member cases to that table's file under supabase/tests/. A member who is not the owner must be allowed; a non-member must not.

在每个 security definer 函数上设置 search_path = ''，并对其中的名字使用模式限定。如果没有固定的 search_path，调用者可以将未限定的名字指向他们自己的对象，并以函数所有者的权限运行它。

🌐 Set search_path = '' on every security definer function and schema-qualify the names inside it. Without a pinned search_path, a caller can point an unqualified name at their own object and run it with the function owner's privileges.

避免递归策略 #

🌐 Avoid recursive policies

两个表的策略互相引用时永远无法解决。Postgres 会抛出 42P17、infinite recursion detected in policy for relation，而且查询对策略适用的每个角色都会失败。

🌐 Two tables whose policies read each other never resolve. Postgres raises 42P17, infinite recursion detected in policy for relation, and the query fails for every role the policies apply to.

共享功能会产生这种形状。关于 lists 的策略会检查 list_members 来找出列表共享给了谁，而关于 list_members 的策略会检查 lists 来找出它的拥有者：

🌐 Sharing features produce this shape. A policy on lists checks list_members to find who the list is shared with, and a policy on list_members checks lists to find who owns it:

-- Reject: each policy reads the table the other one protects.
create policy "members read lists" on lists for select
to authenticated
using (
  exists (
    select 1 from list_members m
    where m.list_id = lists.id and m.user_id = (select auth.uid())
  )
);
create policy "members read membership" on list_members for select
to authenticated
using (
  exists (
    select 1 from lists l
    where l.id = list_members.list_id and l.owner_id = (select auth.uid())
  )
);

通过一个 安全定义者函数 打破循环。它以自己的所有者身份读取成员表，所以第二条策略永远不会运行，循环就被打破了：

🌐 Break the cycle with a security definer function. It reads the membership table as its owner, so the second policy never runs and the cycle is broken:

create schema if not exists private;
create function private.user_list_ids()
returns setof uuid
language sql
security definer
set search_path = ''
stable
as $$
  select list_id from public.list_members
  where user_id = (select auth.uid())
$$;
revoke execute on function private.user_list_ids() from public;
grant usage on schema private to authenticated;
grant execute on function private.user_list_ids() to authenticated;
create policy "members read lists" on lists for select
to authenticated
using ( id in (select private.user_list_ids()) );
create policy "members read membership" on list_members for select
to authenticated
using ( list_id in (select private.user_list_ids()) );

这个函数会根据 (select auth.uid()) 进行过滤，所以它只返回调用者的列表。没有拥有列表的成员仍然可以读取列表，而非成员则什么都看不到。

🌐 The function filters on (select auth.uid()), so it returns only the caller's lists. A member who doesn't own the list still reads it, and a non-member reads nothing.

这是可行的，因为函数是以它的拥有者身份运行的，而 security definer 函数只有在其拥有者可以时才会跳过 RLS。在 Supabase 上，拥有者是 postgres，它有 bypassrls。由没有 bypassrls 的角色拥有的函数，或者读取被设置为 force row level security 的表，会再次评估成员策略并保持递归。

🌐 This works because the function runs as its owner, and a security definer function only skips RLS when its owner can. On Supabase the owner is postgres, which has bypassrls. A function owned by a role without bypassrls, or reading a table set to force row level security, evaluates the membership policy again and stays recursive.

绕过行级别安全 #

🌐 Bypassing Row Level Security

对需要绕过 RLS 的管理任务使用 秘密密钥。秘密密钥通过 service_role Postgres 角色授权访问，该角色具有 bypassrls 属性。切勿在浏览器中使用秘密密钥或将其暴露给客户。

🌐 Use a secret key for administrative tasks that need to bypass RLS. A secret key authorizes access through the service_role Postgres role, which has the bypassrls attribute. Never use a secret key in the browser or expose it to customers.

基于 JWT 的 service_role 密钥是一个旧版本的替代方案。尽量使用秘密密钥。

🌐 The JWT-based service_role key is a legacy alternative. Prefer a secret key where possible.

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

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

alter role "role_name" with bypassrls;

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

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

相关内容 #

🌐 Related content