CODEWITHSUNDEEP
postgresqlrow-level-securitysupabasepostgrestsupabase-database
Scott Yu
Scott Yu

Reputation: 165

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.

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

{
    "id": "123-123-1241-1231",
    "created_at": "2022-06-10T03:59:22.751125+00:00",
    "is_subscribed": false,
    "interval": null,
    "email": "[email protected]"
}

Response after turning on RLS

{
    "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.

Upvotes: 2

Views: 4597

Answers (1)

Scott Yu
Scott Yu

Reputation: 165

Got help from Supabase's Discord and resolved the problem.

Thanks @garyaustin from Discord!

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 policy to true and anon works that confirms no logged in user at point of call.

I'm using NextJS and cookie were attached on client-side requests but didn't attach jwt on server-side requests.

Included token and RLS was working with authenticated role.

supabase.auth.setAuth(access_token)

Upvotes: 0

Related Questions