how to insert RLS to inser a group member？

[jonz-tech]

I have a table name group_members like this:

field meaning :
gid: the group id
role: 1: admin, 2: manager, 3: member

here is my question:
I want to insert a goup member only when the authented user is group manager or admin.

I try some policy like this:
`
create policy "CREATE POLICY "insert_based_on_user_role" ON "public"."tb_group_members"

AS PERMISSIVE FOR INSERT TO PUBLIC

WITH CHECK (

EXISTS (
    SELECT 1
    FROM public.tb_group_members
    WHERE role in (1,2)
    AND uid = auth.uid() 
    AND gid = NEW.gid
    AND validate = 1 
)

);
`

but some error occur: Failed to update database policy: undefined

so, how can I write the correct RLS policy?

[GaryAustin1]

You show doing create policy then have create policy again. You only do that once. Where are you entering this?

[jonz-tech]

still confuse about it.

if a auth user has 3 group, he is 2 group's admin, an 1 group's member. Only admin role can add group member.
some rows like this in group_member_table:
gid role uid
21 admin a_id
22 admin a_id
23 member a_id
...

so, what is ther correct RLS policy to check Using when insert group's member in the right role and gid ?

[GaryAustin1]

You would do something like you were showing, just don't use NEW.

EXISTS (
    SELECT 1
    FROM public.tb_group_members
    WHERE role in (1,2)
    AND uid = auth.uid() 
    AND gid = tb_group_members.gid
    AND validate = 1 
)

[jonz-tech]

sorry, i had try this. but a recursion Error response:

ERROR: 42P17: infinite recursion detected in policy for relation "tb_group_members" CONTEXT: SQL statement "INSERT INTO tb_group_members (gid, uid,role,created_uid, validate) VALUES (gid_input,uid_input,role_input,auth_uid,1)" PL/pgSQL function fn_member_add(integer,uuid,integer) line 38 at SQL statement

[GaryAustin1]

That would only work for insert. For select policy I assumed you were not limiting it. You can't do a select on the table itself in hte select policy without moving all the of the select code to a security definer function. It will keep calling itself over and over.

[GaryAustin1]

https://github.com/orgs/supabase/discussions/4509
If you search for recursion here in discussions you will find lots of hits.

[jonz-tech]

you are right! it work for insert, but when select policy is : auth.uid() = uid then infinite recursion detected!
I had search in supabase repo issue, but not search in discussions. I am sorry for not find that result!
thank's for your help!

[jonz-tech]

https://github.com/orgs/supabase/discussions/4509 If you search for recursion here in discussions you will find lots of hits.

sorry!
i try the definer function to advoid recursion, but it seems work in 2021 and not work now, recursion still occur!

here is my definer funtion:
`
CREATE OR REPLACE FUNCTION fn_member_gids_by_uid(user_id uuid)
RETURNS SETOF BIGINT
STABLE
SECURITY DEFINER
SET search_path = public
as $$
begin
SELECT gid from tb_group_members gm where gm.uid = user_id;
end
$$ LANGUAGE plpgsql;

`

and for select policy:
`

create policy "query by auth uid" on "public"."tb_group_members" as permissive for select to authenticated using (
(
gid IN (
SELECT fn_member_gids_by_uid(
( SELECT auth.uid() AS uid)) AS fn_member_gids_by_uid)
)
);

`

[GaryAustin1]

I really have no idea what you are doing there. But never pass in a UUID for the user to an RLS function. Just use auth.uid() in the function.