I am getting an infinite recursion `infinite recursion detec Supabase #sql

I am getting an infinite recursion `infinite recur...

mikebarkmin

10/20/2021, 9:18 PM

I am getting an infinite recursion

infinite recursion detected in policy for relation "conversation_users"

on inserting to conversation_users. I thought creating a security definer function would solve this, but it did not. Does anybody has an idea?

Copy code

sql
CREATE OR REPLACE FUNCTION get_conversation_users_for_authenticated_user()
returns setof uuid
language sql
security definer
set search_path = public
stable
as $$
    SELECT DISTINCT user_id FROM conversation_users WHERE conversation_id IN (
        SELECT DISTINCT conversation_id FROM conversation_users WHERE user_id = auth.uid()
    )
$$;

CREATE POLICY "Can select conversation_users" ON public.conversation_users AS PERMISSIVE FOR
SELECT TO public USING (
    user_id IN (
        SELECT get_conversation_users_for_authenticated_user()
    )
);

CREATE POLICY "Can update to conversation_users" ON public.conversation_users AS PERMISSIVE FOR
UPDATE To public USING (
    conversation_id IN (SELECT id FROM conversations WHERE admin_id = auth.uid())
) WITH CHECK (
    conversation_id IN (SELECT id FROM conversations WHERE admin_id = auth.uid())
);

CREATE POLICY "Can insert to conversation_users" ON public.conversation_users AS PERMISSIVE FOR
INSERT To public WITH CHECK (
    conversation_id IN (SELECT id FROM conversations WHERE admin_id = auth.uid())
);

CREATE POLICY "Can delete from conversation_users" ON public.conversation_users AS PERMISSIVE FOR
DELETE TO public USING (
    (conversation_id IN (SELECT id FROM conversations WHERE admin_id = auth.uid()))
    OR (user_id = auth.uid())
);

jason-lynx

10/21/2021, 2:56 AM

the recursion would be occurring because of the

SELECT

policy, since it is applied on table

conversation_users

, but to check if the policy is violated, you run the

get_conversation_users_for_authenticated_user()

function, which queries the

conversation_users

table, causing another policy check, which queries the table again etc...

jason-lynx

10/21/2021, 3:01 AM

if i understand your goal correctly, it looks like a

user_id = auth.uid()

condition would be sufficient for your SELECT policy? this allows a user to see a conversation if their

user_id

appears in the same row as a given

conversation_id

jason-lynx

10/21/2021, 4:32 AM

just to consolidate threads, let's continue discussion on https://discord.com/channels/839993398554656828/900428702834393169 if any. i've linked the documentation there, which should solve your problem as well. in your case, the function body should just be

SELECT DISTINCT conversation_id FROM conversation_users WHERE user_id = auth.uid()

, and your SELECT policy should only have to be

USING (conversation_id IN (SELECT get_conversation_users_for_authenticated_user()

. remember to run these commands in the supabase SQL editor, which will ensure you have the necessary security definer rights

mikebarkmin

10/21/2021, 7:20 AM

Thanks. Do you have an idea, if this could also be pulled of without using the SQL editor. I am trying to maintain migrations files to keep my environments in sync. Currently I run these with the normal postgres user.

jason-lynx

10/21/2021, 8:09 AM

the normal

postgres

user should also work since it also bypasses RLS

Previous Next