列级安全

Postgres 的 行级安全 (RLS) 让你可以精细控制谁可以访问数据行。不过，它不能控制他们可以访问行中的哪些列。有时候你可能想限制对数据库中特定列的访问。列级权限就可以做到这一点。

🌐 Postgres's Row Level Security (RLS) gives you granular control over who can access rows of data. However, it doesn't give you control over which columns they can access within rows. Sometimes you want to restrict access to specific columns in your database. Column Level Privileges allows you to do that.

行级策略 #

🌐 Policies at the row level

行级安全（RLS）中的策略用于限制对表中行的访问。可以把它们想象成在每个查询中都加了一个 WHERE 子句。

🌐 Policies in Row Level Security (RLS) are used to restrict access to rows in a table. Think of them like adding a WHERE clause to every query.

例如，假设你有一个 posts 表，里面有以下列：

🌐 For example, assume you have a posts table with the following columns:

• id
• user_id
• title
• content
• created_at
• updated_at

你可以使用 RLS 限制只有创建它的用户才能更新，具体策略如下：

🌐 You can restrict updates to the user who created it using RLS, with the following policy:

1
create policy "Allow update for owners" on posts for
2
update
3
  using ((select auth.uid()) = user_id);

不过，这会让帖子拥有者可以完全访问并更新这一行，包括所有列。

🌐 However, this gives the post owner full access to update the row, including all of the columns.

列级别的权限 #

🌐 Privileges at the column level

要限制对列的访问，你可以使用 权限。

🌐 To restrict access to columns, you can use Privileges.

在 Postgres 中有两种权限：

🌐 There are two types of privileges in Postgres:

1. 表级：授予表中所有列的权限。
2. 列级 授予表中某个特定列的权限。

你可以在同一张表上拥有两种类型的权限。如果你两种都有，并且你撤销了列级权限，表级权限仍然会有效。

🌐 You can have both types of privileges on the same table. If you have both, and you revoke the column-level privilege, the table-level privilege will still be in effect.

默认情况下，我们的表将拥有表级别的 UPDATE 权限，这意味着 authenticated 角色可以更新表中的所有列。

🌐 By default, our table will have a table-level UPDATE privilege, which means that the authenticated role can update all the columns in the table.

1
revoke
2
update
3
  on table public.posts
4
from
5
  authenticated;
6
7
grant
8
update
9
  (title, content) on table public.posts to authenticated;

在上面的例子中，我们正在从 authenticated 角色撤销表级别的 UPDATE 权限，并在 title 和 content 列上授予列级别的 UPDATE 权限。

🌐 In the above example, we are revoking the table-level UPDATE privilege from the authenticated role and granting a column-level UPDATE privilege on the title and content columns.

如果我们想限制对更新 title 列的访问：

🌐 If we want to restrict access to updating the title column:

1
revoke
2
update
3
  (title) on table public.posts
4
from
5
  authenticated;

这次，我们要从 authenticated 角色撤销 title 列的列级 UPDATE 权限。我们不需要撤销表级的 UPDATE 权限，因为它已经被撤销了。

🌐 This time, we are revoking the column-level UPDATE privilege of the title column from the authenticated role. We didn't need to revoke the table-level UPDATE privilege because it's already revoked.

在仪表板中管理列权限 #

🌐 Manage column privileges in the Dashboard

你可以在 Supabase Studio 查看和编辑权限。

🌐 You can view and edit the privileges in the Supabase Studio.

在迁移中管理列权限 #

🌐 Manage column privileges in migrations

虽然你可以直接在仪表板上管理权限，但随着你的项目增长，你可能想在迁移中管理它们。关于数据库迁移，请阅读本地开发指南。

🌐 While you can manage privileges directly from the Dashboard, as your project grows you may want to manage them in your migrations. Read about database migrations in the Local Development guide.

1
supabase migration new create_posts_table

1
create table
2
posts (
3
id bigint primary key generated always as identity,
4
user_id text,
5
title text,
6
content text,
7
created_at timestamptz default now(),
8
updated_at timestamptz default now()
9
);
10
11
-- Add row-level security
12
create policy "Allow update for owners" on posts for
13
update
14
using ((select auth.uid()) = user_id);
15
16
-- Add column-level security
17
revoke
18
update
19
(title) on table public.posts
20
from
21
authenticated;

使用列级权限时的注意事项 #

🌐 Considerations when using column-level privileges

• 如果你关闭某列的权限，你就完全无法使用那一列了。
• 所有操作（插入、更新、删除）以及使用 select * 都会失败。