How to trigger insertion of row into child table from parent table with RLS enabled?

Question

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

Laurenz Albe · Accepted Answer · 2023-06-04 20:12:10Z

0

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

answered Jun 4, 2023 at 20:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sai Ramachandran Over a year ago

As given, your SQL gives an error saying "missing FROM clause entry for "members". I'm still accepting your answer because rewriting the SQL like this seems to work. SELECT EXISTS ( SELECT 1 FROM team t WHERE t.owner = auth.uid() AND t.id = _team_id)

Laurenz Albe Over a year ago

My code sample expects that the table is called member.

Collectives™ on Stack Overflow

How to trigger insertion of row into child table from parent table with RLS enabled?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related