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)?
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()
)
);