PostgreSQL 行级安全性涉及其他表的外键

Question

我想知道在PostgreSQL中是否可以使用RLS（或任何其他机制）实现以下功能。如果用户的ID与另一个表中的某个列匹配，我希望用户能够获取表的某些行。

例如，我们有以下表：

"user"表： 列：id、姓名

| id | name  |
| --- | ---  |
| 1   | one  |
| 2   | two  |
| 3   | three|
| 4   | four |

"租户"表: 列：id、名称

| id | name |
| --- | --- |
| 1   | t1  |
| 2   | t2  |

"user_tenant"表： 列：用户ID，租户ID

| user_id | tenant_id|
| ---     | ---      |
| 1       | t1       |
| 2       | t2       |
| 3       | t1       |
| 4       | t2       |

我只想要与当前用户相同租户ID的用户。

| id | name  |
| --- | ---  |
| 1   | one  |
| 3   | three|

为了实现这个目标，我需要创建类似以下的策略：

CREATE POLICY tenant_policy ON "user" USING (tenant_id = current_setting('my_user.current_tenant')::uuid);

使用上述策略后，它并没有按预期工作，因为我得到了所有用户。

注意： 用户和租户表之间有多对多关系。

附言： 我知道我们可以通过连接或其他条件来实现这一点。但我想使用PostgreSQL和RLS（行级安全）实现上述输出。

提前感谢！！

- piyush

Answer 1

如果行级安全（row level security）没有起作用，可能是以下其中一种情况导致的：

• you didn't enable row level security:

  ALTER TABLE "user" ENABLE ROW LEVEL SECURITY;
  

• the user owns the table

  You can enable row level security for the owner with

  ALTER TABLE "user" FORCE ROW LEVEL SECURITY;
  

• you are a superuser, which is always exempt from RLS

• you are a user defines with BYPASSRLS

• the parameter row_security is set to off

除此之外，您可能还需要在策略中加入user_tenant。

CREATE POLICY tenant_policy ON "user"
   USING (
      EXISTS(SELECT 1 FROM user_tenant AS ut
             WHERE ut.user_id = "user".id
               AND ut.tenant_id = current_setting('my_user.current_tenant')::uuid
            )
      );

- Laurenz Albe

---

我不确定上述策略是否可行，因为“用户”表中不存在tenant_id列。我尝试了一下，但出现了错误。 - piyush

我明白了。我已经添加了一个建议的策略。 - Laurenz Albe

我已经尝试了你建议的那个方法，但是我仍然获取到了除一个用户租户外的所有租户的所有用户：CREATE POLICY tenant_policy ON "user" USING ( EXISTS(SELECT 1 FROM user_tenant AS ut WHERE ut.user_id = id AND ut.tenant_id = current_setting('my_user.current_tenant')::uuid ) );当我运行该策略时，在数据库中它显示为：(EXISTS ( SELECT 1 FROM user_tenant ut WHERE ((ut.user_id = ut.id) AND (ut.tenant_id = (current_setting('app_user.current_tenant'::text))::uuid)))) - piyush

我的错误。我现在已经用表名限定了 id。 - Laurenz Albe

哦..我不确定这是否是问题，因为现在当我创建你建议的策略时，它会给出0个结果，而当我尝试创建时，会出现错误“行违反了表“users”的行级安全策略”。你有什么想法吗？ - piyush