英文:
RLS Policy for a table based on another table
问题
根据我学到的知识，设置 RLS 应该很容易。但不知何故，我无法使其工作。请为我解惑。谢谢。
对于表 student，我的策略如下：
CREATE POLICY "crud" ON "public"."student"
AS PERMISSIVE FOR ALL
TO public
USING ((id IN (SELECT sb.student_id FROM student_branch sb WHERE (sb.branch_id = (SELECT a.branch_id FROM admin a WHERE (a.id = '62bfcd1c-016c-416a-a155-388e766b411f'::uuid))))))
WITH CHECK ((id IN (SELECT sb.student_id FROM student_branch sb WHERE (sb.branch_id = (SELECT a.branch_id FROM admin a WHERE (a.id = '62bfcd1c-016c-416a-a155-388e766b411f'::uuid))))))
基本上，一个具有 ID 为 62bfcd1c-016c-416a-a155-388e766b411f 的管理员，属于（假设）branch_id = 1，应该只能看到表 student_branch 中 branch_id = 1 的学生。
但我得到了空结果。我尝试运行下面的 SQL 语句，它可以正常工作，但策略没有执行。
SELECT * FROM student WHERE (id IN (SELECT sb.student_id
FROM student_branch sb
WHERE (sb.branch_id = (SELECT admin.branch_id
FROM admin
WHERE (admin.id = '62bfcd1c-016c-416a-a155-388e766b411f'::uuid)))))
学生数据：
id, name
========
1, Student 1
2, Student 2
管理员数据：
id, name, branch_id
===================
62bfcd1c-016c-416a-a155-388e766b411f, Admin 1, 1
学生分支数据：
id, student_id, branch_id
=========================
1, 1, 1
使用 Supabase API 访问：
<url>/student?select=*
英文:
And based on what I learned, setting an RLS should be easy. But somehow I couldn't make this work. Please shed some light. Thanks.
For table student, my policy is as this:
CREATE POLICY "crud" ON "public"."student"
AS PERMISSIVE FOR ALL
TO public
USING ((id IN ( SELECT sb.student_id FROM student_branch sb WHERE (sb.branch_id = ( SELECT a.branch_id FROM admin a WHERE (a.id = '62bfcd1c-016c-416a-a155-388e766b411f'::uuid))))))
WITH CHECK ((id IN ( SELECT sb.student_id FROM student_branch sb WHERE (sb.branch_id = ( SELECT a.branch_id FROM admin a WHERE (a.id = '62bfcd1c-016c-416a-a155-388e766b411f'::uuid))))))
which basically an admin with id 62bfcd1c-016c-416a-a155-388e766b411f which belong to, let say branch_id = 1 should see only student for branch_id = 1 in table student_branch,
But I get empty result. I tried running sql statement below, it works ok but the policy did not get enforced.
select * from student where (id IN ( SELECT sb.student_id
FROM student_branch sb
WHERE (sb.branch_id = ( SELECT admin.branch_id
FROM admin
WHERE (admin.id = '62bfcd1c-016c-416a-a155-388e766b411f'::uuid)))))
the student data
id, name
========
1, Student 1
2, Student 2
the admin data
id, name, branch_id
============
62bfcd1c-016c-416a-a155-388e766b411f, Admin 1, 1
the student_branch data
id, student_id, branch_id
======================
1, 1, 1
access using Supabase API:
<url>/student?select=*
答案1
得分: 0
好的。看起来我需要正确配置我所引用的另一张表 student_branch 和 admin 的策略。
现在我对这两张表使用这个策略：
创建策略 "policy_name"
在 public.student_branch 上
对于 SELECT 使用 (
真
);
我担心这个策略太开放了，我计划在另一个新问题中提出。
英文:
OK. It seems I've to correctly configure policy for the other table that I'm referring to: student_branch and admin.
Right now I'm using this policy for those two tables:
CREATE POLICY "policy_name"
ON public.student_branch
FOR SELECT USING (
true
);
which I fear is too open which I plan to ask in another new question.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论