RLS Simplified

基本总结 #

🌐 Basic summary

行级安全（RLS）策略：自动应用到数据库查询的 WHERE 或 CHECK 条件

🌐 Row-Level Security (RLS) Policy: A WHERE or CHECK condition applied automatically to database queries

主要特点：

🌐 Key features:

无需显式添加到每个查询中，这使得它非常适合管理未知实体（例如使用匿名或已认证角色的用户）的行访问。
可以为特定操作设置（例如，SELECT、INSERT）
可以针对特定的数据库角色（例如，“匿名”、“已认证”）

与常规情况相比：

🌐 Contrast with regular conditions:

常规条件：适用于所有角色，需要手动添加到每个查询中
RLS 策略：自动应用于指定的操作和角色

实操演示各种情况 #

🌐 Hands on walk-through for conditions

使用： #

🌐 USING:

USING 关键字会检查行的值，以确定是否应该让查询可见。

🌐 The USING keyword inspects the value of row to see if it should be made visible to the query.

当你使用 SELECT、UPDATE 或 DELETE 时，你必须使用 WHERE 语句来查找特定的行：

🌐 When you SELECT, UPDATE, or DELETE, you have to use a WHERE statement to search for specific rows:

1
-- select
2
select *
3
from some_table
4
where id = 5;
5
6
-- update
7
update some_table
8
set id = 6
9
where id = 5;
10
11
-- delete
12
delete from some_table
13
where id = 6;

即使你没有使用 WHERE 语句，它仍然会有一个隐含的:

🌐 Even when you don't use a WHERE statement, there's still an implicit one:

1
-- ...your query
2
WHERE true;

USING 条款会在 WHERE 语句后追加更多内容：

🌐 The USING clause appends more to the WHERE statement:

1
-- Your Using condition
2
USING (
3
  (select auth.uid()) = user_id
4
);
5
6
-- Your query without RLS:
7
SELECT * FROM some_table
8
WHERE id = 5 OR id = 6;
9
10
-- Your query after RLS
11
SELECT * FROM some_table
12
WHERE
13
  (id = 5 OR id = 6)
14
    AND
15
  (select auth.uid()) = user_id) -- <--- added by the USING clause;

带检查： #

🌐 WITH CHECK:

假设你有一个个人资料表。嗯，你不希望用户在插入数据时可以修改他们的 user_id，对吧？

🌐 Say you have a profile table. Well, you don't want user's to be able to modify their user_id when they make an insert, do you?

WITH CHECK 条件会检查正在被添加或修改的值。对于 INSERT，你可以单独使用它。无需使用 using 子句：

🌐 The WITH CHECK condition inspects values that are being added or modified. For INSERT you'd use it by itself. There's no need for a using clause:

1
-- Allow users to add to table, but make sure their user_id matches the one in their JWT:
2
3
create policy "Allow user to add posts"
4
on "public"."posts"
5
as PERMISSIVE
6
for INSERT
7
to authenticated
8
with check(
9
  (select auth.uid()) = user_id
10
);
11
12
-- Example: failing insert
13
INSERT INTO posts
14
VALUES (<false id>, <comment>);
15
16
-- Example: successful insert
17
INSERT INTO posts
18
VALUES (<real id>, <comment>);

INSERT 不依赖 WHERE 子句，但它们可以有约束。在这种情况下，RLS 相当于对某一列的 CHECK 约束，例如：

🌐 INSERTs do not rely on WHERE clauses, but they can have constraints. In this case, the RLS acts as a CHECK constraint against a column, e.g.:

1
ALTER TABLE table_name
2
ADD CONSTRAINT constraint_name CHECK (condition);

它和普通的 CHECK 约束的区别在于，它只针对某些角色或方法激活。

🌐 What distinguishes it from normal CHECK constraints is that it is only activate for certain roles or methods.

更新: #

🌐 UPDATEs:

更新要更改的行的两个过滤器，然后向表中添加新值，因此它需要同时使用 USING 和 WITH CHECK 条件：

🌐 UPDATE both filters for rows to change and then adds new values to the table, so it requires both USING and WITH CHECK conditions:

1
create policy "Allow user to edit their stuff"
2
on "public"."<SOME TABLE NAME>"
3
as RESTRICTIVE
4
for UPDATE
5
to authenticated
6
using (
7
  (select auth.uid()) = user_id
8
)
9
with check(
10
  (select auth.uid()) = user_id
11
);