Supabase•8mo ago

Groups membership join X create

I’m building a database with Supabase and I need to manage group memberships. The database has two tables: 1. groups: Contains group info (id, name, join_code, etc.). 2. group_members: Tracks group members (group_id, user_id, role, join_code, etc.). Requirements: 1. When a group is created: • The creator should be automatically added to group_members with is_admin = TRUE. 2. When a user joins a group using a join_code: • The user only enters the join_code shared by another member; they don’t have access to the group’s name or id. • If the join_code is valid, the user is added to group_members. Problems: 1. Automatic insertion for creators: • A trigger should add the creator to group_members when a group is created. • Problem: RLS on group_members blocks this insertion because it requires a valid join_code. 2. Joining a group with a join_code: • A user must be added to group_members if the join_code is valid. • Problem: Checking the join_code in the same table (group_members) can cause a circular dependency. 3. RLS constraints: • RLS must enforce: • Only users with a valid join_code can join. • Creators can be added automatically during group creation. Question: What’s the best way to handle these cases while keeping RLS rules intact, ensuring secure validation, and without using Edge Functions?

14 Replies

DevsrealmGuy•8mo ago

Not clear as you are not showing the policies in question, however, here are some tips: For the first problem: Automatic insertion for creators Why not make the trigger function definer, I assume you are using invoker here? For the second problem: Joining a group with a join_code I think you should re-architect the table, perhaps, have another table solely for join_codes, instead of having it repeated in both groups and group_members

ang3LOP•8mo ago

Thanks for your input! I understand the idea of moving the join_code to its own table, but I find it a bit overkill since there’s only one join_code per group, so keeping it as a column in the groups table feels more logical to me. The goal is for users to simply enter the join_code, and I want Supabase to: 1. Verify that the code exists. 2. If it exists, automatically insert the user into the group_members table. The challenge is making this flow work with RLS in place while ensuring the join_code isn’t exposed to users or misused. Any thoughts? I will be more available in few hours to send my functions and database schema and to show why it doesn’t works like that

ang3LOP•8mo ago

So here we are : my two tables and I would like : 1 - When a group is created: • The creator should be automatically added to group_members with is_admin = TRUE 2 - When a user joins a group using a join_code : • The user only enters the join_code shared by another member; they don’t have access to the group’s name or id • If the join_code is valid, the user is added to group_members The idea was to check via an RLS rule if the join_code provided by the user already existed in the groups table. If yes, allow insertion into group_members. However, this blocked the insertion in case a new group was created, because there was no row in group_members related to the group yet to check the condition. I tried to duplicate the join_code in group_members, inserting a line for the creator when the group was created. The idea was that other users could then validate their join_code directly from this table. However, this approach complicates the database and introduces unnecessary redundancy. Additionally, this did not completely resolve the limitations related to RLS and automatic checks.

garyaustin•8mo ago

You might consider using a trigger for group creation and joining.

ang3LOP•8mo ago

I already tried using trigger, when a group is created I triggered the fact that the admin join the group in group_members, then I add an RLS and Im stuck for the joining

garyaustin•8mo ago

Not really looking at your details today... I looked at some group RLS stuff here: https://github.com/GaryAustin1/custom-properties/blob/main/groups.sql Note to do select RLS that uses a value in the same table you have to do a security definer function to avoid recursion. I would think joining a group (still involves an insert) could be done in a trigger to check things.

ang3LOP•8mo ago

So what I have done : Trigger to add the creator in group_members:

CREATE OR REPLACE FUNCTION add_creator_to_group_members()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO group_members (group_id, user_id, is_admin)
    VALUES (NEW.id, NEW.created_by, TRUE);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER after_group_creation
AFTER INSERT ON groups
FOR EACH ROW
EXECUTE FUNCTION add_creator_to_group_members();

CREATE OR REPLACE FUNCTION add_creator_to_group_members()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO group_members (group_id, user_id, is_admin)
    VALUES (NEW.id, NEW.created_by, TRUE);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER after_group_creation
AFTER INSERT ON groups
FOR EACH ROW
EXECUTE FUNCTION add_creator_to_group_members();

RLS on group_members to join via a join_code:

CREATE POLICY join_with_code
ON group_members
FOR INSERT
WITH CHECK (
    EXISTS (
        SELECT 1
        FROM groups
        WHERE groups.id = group_members.group_id
        AND groups.join_code = group_members.join_code
    )
);

CREATE POLICY join_with_code
ON group_members
FOR INSERT
WITH CHECK (
    EXISTS (
        SELECT 1
        FROM groups
        WHERE groups.id = group_members.group_id
        AND groups.join_code = group_members.join_code
    )
);

But I cannot retrieve the group name, I need the end user to send only the join_code and get his id from jwt if anyone can take some time on that, I would be really grateful, 4 days I spend on this now

garyaustin•8mo ago

What is your select RLS on groups? You have to meet that. Or you have to move the select code on groups to a security definer function to bypass RLS. And worst case why not just have a function to join a group and check in that if the code matches?

ang3LOP•8mo ago

yeah but then how can i call that function with flutterflow ? by the api ? I already tried that without succeeded

garyaustin•8mo ago

RPC call? But I don't know anything about FlutterFlow. There is a REST API endpoint for Postgres function calls and a Flutter client RPC call that uses the same endpoint.

ang3LOP•8mo ago

I think my problem is quite basic, i don’t know why i’m trying so hard just to propose to some users to join a group ahah but as you see I tried everything I could Hello, if anyone got an answer for my point I'ld take it 🙂 thanks !

DevsrealmGuy•8mo ago

You are over thinking this and no you haven't tried everything yet 😉, let me get on my PC and see if I can help out First, going by your requirement, and you not wanting a junction table, you can optimize your table like so:

groups: (id, name, join_code, etc.)
group_members: (group_id, user_id, role, etc.)

groups: (id, name, join_code, etc.)
group_members: (group_id, user_id, role, etc.)

Remove the join_codes in the group_members table, it is not needed, there is already a relation to the groups table by the group_id Now when creating the trigger that adds the a group_members record when a group is created, ensure you are using a SECURITY DEFINER and not INVOKER The INVOKER will respect RLS which is why you get the RLS issue, DEFINER though takes the capability of what role created the trigger function e.g postgres role, so, it can bypass the RLS. For the following issue: But I cannot retrieve the group name, I need the end user to send only the join_code and get his id from jwt Use a postgres function to retrieve that with a security definer (though I'll assume you will limit the use of this function to RLS and the function should be created in a private schema): I think the conclusion is the same as to what gary came up with, if you can be more specific, we can help!

ang3LOP•8mo ago

Thank you so much, okay so : - I deleted the join_code from my group_members - I created a trigger fnction to add the admin :

CREATE OR REPLACE FUNCTION add_admin_to_group()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO group_members (group_id, user_id, is_admin, role, joined_at)
    VALUES (NEW.id, NEW.created_by, TRUE, 'admin', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trigger_add_admin
AFTER INSERT ON groups
FOR EACH ROW EXECUTE FUNCTION add_admin_to_group();

CREATE OR REPLACE FUNCTION add_admin_to_group()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO group_members (group_id, user_id, is_admin, role, joined_at)
    VALUES (NEW.id, NEW.created_by, TRUE, 'admin', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trigger_add_admin
AFTER INSERT ON groups
FOR EACH ROW EXECUTE FUNCTION add_admin_to_group();

then I create my function :

CREATE OR REPLACE FUNCTION get_group_by_join_code(p_join_code TEXT)
RETURNS UUID AS $$
DECLARE
    v_group_id UUID;
BEGIN
    SELECT id INTO v_group_id FROM groups WHERE join_code = p_join_code;

    IF v_group_id IS NULL THEN
        RAISE EXCEPTION 'Invalid join code';
    END IF;

    RETURN v_group_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION get_group_by_join_code(p_join_code TEXT)
RETURNS UUID AS $$
DECLARE
    v_group_id UUID;
BEGIN
    SELECT id INTO v_group_id FROM groups WHERE join_code = p_join_code;

    IF v_group_id IS NULL THEN
        RAISE EXCEPTION 'Invalid join code';
    END IF;

    RETURN v_group_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

then I edit the schema :

ALTER FUNCTION get_group_by_join_code SET search_path = private_schema;

ALTER FUNCTION get_group_by_join_code SET search_path = private_schema;

And it should works ? Can you explain How I can insert a data un group_members by using this function ? From the api..

DevsrealmGuy•8mo ago

Yes that looks good, since there is a trigger already when a data is inserted into the groups table, it automatically adds it (this is what your add_admin_to_group) does. For the case where the user is added to the group_members when they supply a valid join_code, you create another function (this one should be in the public schema though), inside the function you check if the join_code is valid if so, retrieve the group by the join_code and use that record alongside when adding the group_memebers record. All that is left is calling it like so:

await supabase.rpc('join_group', { code: 'xxx' })

await supabase.rpc('join_group', { code: 'xxx' })

Hope that helps!

Gaming

Programming

Groups membership join X create

Did you find this page helpful?