Getting 406 JSON object requested, multiple (or no) rows when RLS is enabled on Supabase I have looked at various solutions on StackOverflow, github issues in supabase, supabase/postgrest-js, postgRESTPostgREST/postgrest, and searched the Discord as well. But none of the solutions have been working so far. The code works as expected, but as soon as I turn on RLS on Supabase. The request will return the below 406 error.

js
export const supabase = createClient(process.env.NEXT_PUBLIC_SUPABASE_URL, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY)

const { data, error } = await supabase.from('profiles').select('*').eq('id', userId).maybeSingle()
// const { data, error } = await supabase.from('profiles').select('*').eq('id', userId).limit(1).single() // works when RLS disabled
// const { data, error } = await supabase.from('profiles').select('*').eq('id', userId).single() // works when RLS disabled

Response before RLS

js
{
    "id": "123-123-1241-1231",
    "created_at": "2022-06-10T03:59:22.751125+00:00",
    "is_subscribed": false,
    "interval": null,
    "email": "scott.tj.yu@gmail.com"
}

Response after turning on RLS

js
{
    "message": "JSON object requested, multiple (or no) rows returned",
    "details": "Results contain 0 rows, application/vnd.pgrst.object+json requires 1 row"
}

I've tried schema reload, re-implement the policy, but none's been working so far. I have a "profile" table its "id" column referencing the "auth.users.id". The policy's target role is currently "anon" but I've tried "authenticated" as well.

(uid() = id)

I also tried to change the table name to "profiles" (plural) instead of "profile" but no luck.

Hello @beepboop! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates. Want to change the title? Use the ``/title`` command! We have solved your problem? Click the button below to archive it.

First thing is maybeSingle will still get a 406 if no row is returned, it just won't generate an error back from the supabase function call. So your RLS is blocking you. Is your select policy (uid() = id)? If so did you make up the 123-123..... (I assume so) and you have a real UUID. Then anon won't work unless you have true as the policy. My guess is you don't have a logged in user (or jwt) in that case when the call is made If you set to true and anon works that confirms no logged in user at point of call.

Thank you so much @garyaustin. That actually solved my problem. I changed the select policy to "true" first and it was working so I figured it was logged user based on what you said. I tried to include

the access_token

with

supabase.auth.setAuth(access_token)

after initializing

createClient(URL, ANON_KEY)

and it works. Thanks a ton! Would you want to paste the answer on StackOverflow since I asked there too? https://stackoverflow.com/questions/72654424/406-json-object-requested-multiple-or-no-rows-when-rls-is-enabled-on-supabase If not, I can just answer my own question. Thanks again!

Thread was archived by @beepboop. Anyone can send a message to unarchive it.