英文:
How to trigger insertion of row into child table from parent table with RLS enabled?
问题
在Supabase中，当我尝试向父表添加行以触发在子表中添加相应行时，我收到以下错误消息：新行违反了表"members"的行级安全策略
我有一个名为TEAM的父表和一个名为MEMBERS的子表。每个TEAM都由一个所有者拥有，每个所有者也是团队的成员。
TEAM
----
id (uuid)
owner (uuid) --> 与public.users.id的FK关系
MEMBER
----
team_id (uuid) --> 与TEAM.id的FK关系
member_id (uuid) --> 与public.users.id的FK关系
为了实现这一点，我有一个触发器，它在每次在TEAM表上插入一行之后执行，并在MEMBERS中插入team_id和所有者的user_id。
我在MEMBERS上有一个RLS策略，该策略规定只有所有者才能将成员添加到团队中。这是通过以下SECURITY DEFINER SQL函数来检查的：
名称：is_team_owner(_team_id uuid)
返回 BOOL
代码 -
SELECT EXISTS (
SELECT 1
FROM members m, team t
WHERE m.team_id = t.id
AND t.owner_id = auth.uid()
AND t.id = _team_id
)
我的RLS策略对MEMBERS的INSERT是WITH CHECK (is_team_owner(_team_id))。
我的问题是，当我使用cURL创建一个新团队时：
a）新团队不会添加到表中
b）我收到以下错误消息：新行违反了表"members"的行级安全策略
我怀疑(a)是因为(b)，这也是一个问题，因为我的触发器应该在INSERT之后运行，所以MEMBERS表中的策略违规不应该影响TEAM的插入。
我认为(b)发生是因为在触发的插入操作进入MEMBERS时，对TEAM的插入尚未完成。
如何修复(b)？
英文:
In Supabase, I get the following error when I try to add a row to a parent table which should also trigger the addition of a corresponding row in the child table: new row violates row-level security policy for table \"members\"
I have a parent table called TEAM and a child table called MEMBERS. Each TEAM is owned by an owner. Each owner is also a member of the team.
TEAM
----
id (uuid)
owner (uuid) --> FK relationship with public.users.id
MEMBER
----
team_id (uuid) --> FK relationship with TEAM.id
member_id (uuid) --> FK relationship with public.users.id
To achieve this, I have a trigger which executes AFTER INSERT of each row on the TEAM table and inserts team_id and the owner's user_id into MEMBERS.
I have an RLS policy on MEMBERS which says that only owners can add members to the team. This is checked using a SECURITY DEFINER SQL function given below:
Name: is_team_owner(_team_id uuid)
Returns BOOL
Code -
SELECT EXISTS (
SELECT 1
FROM members m, team t
WHERE m.team_id = t.id
AND t.owner_id = auth.uid()
AND t.id = _team_id
)
My RLS policy for INSERT is WITH CHECK (is_team_owner(_team_id)) on MEMBERS.
My issue is that when I create a new team using cURL
a) the new team does NOT get added to the table
b) I get the following error: new row violates row-level security policy for table \"members\"
I suspect (a) is because of (b) which is also an issue because my trigger is supposed to run AFTER INSERT so policy violations in the MEMBERS table should not affect inserts into TEAM.
I think (b) happens because when the triggered insertion into MEMBERS happens, the insert into TEAM has not been completed.
How do I fix (b)?
答案1
得分: 0
以下是翻译好的部分：
我刚刚测试过了，如果我像这样定义策略，它就可以正常工作：
WITH CHECK (EXISTS
(SELECT FROM team
WHERE team.id = member.team_id
AND team.owner = auth.uid()
)
);
英文:
I just tested it, and it works fine if I define the policy like this:
WITH CHECK (EXISTS
(SELECT FROM team
WHERE team.id = member.team_id
AND team.owner = auth.uid()
)
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论