CODEWITHSUNDEEP
next.jsauth0supabase
gorlaz
gorlaz

Reputation: 478

How to determine reason for RLS policy not working

I've got a NextJS app using Auth0 for auth, Supabase for db and I'm having issues understanding/passing through the correct 'thing' to make RLS work for an upsert. I'm using an API route. If I turn RLS off for the userPreferences table, the upsert works, so I think the upsert code is correct.

Error I'm receiving in browser is; {"error":"new row violates row-level security policy for table \"userPreferences\""}

and in console;

data null {
  code: '42501',
  details: null,
  hint: null,
  message: 'new row violates row-level security policy for table "userPreferences"'
}

I have a userPreferences table;

API route - pages/api/postUserPreferences

import { withApiAuthRequired, getSession } from '@auth0/nextjs-auth0'
import { getSupabase } from '../../utils/supabase';

export default withApiAuthRequired(async function PostUserPreferences(req, res) {
  const body = req.body
  const { user } = getSession(req, res)
  const supabase = getSupabase(user.accessToken)

  if (req.method !== 'POST') {
    res.status(405).send({ message: 'Only POST requests allowed' })
    return
  }

  let { data, error }  = await supabase
    .from('userPreferences')
    .upsert(
      {defaultVendor_id: body.defaultVendor,
      defaultCountry_id: body.defaultCountry,
      defaultBookFormat_id: body.defaultFormat,
      user_id: user.sub},
      { onConflict:'user_id'},
      { returning: 'minimal'}
    )
console.log('data',data, error)
    if (error) return res.status(401).json({ error: error.message })
    return res.status(200).json(data)
})

I want the user_id field to be the field the upsert checks for uniqueness and this appears to work properly when I turn off RLS.

As far as I can figure, the User credentials from the session access token are what are used to initiate the Supabase connection which I think means the user object is along for the ride when the query is made and so auth.user_id can be resolved?

I have a different insert with RLS applied which is working using the same method so I'm confused as to why the same approach of user_id() = user_id isn't working here. From what I can read on Supabase's site; USING - equivalent to a where clause of 'where auth.user_id() = userPreferences.user_id WITH CHECK - equivalent to a .eq? (I'm not clear on this).

I'm not sure how to query/output the RLS easily but I have;

Any pointers greatly appreciated.

Upvotes: 1

Views: 1146

Answers (1)

gorlaz
gorlaz

Reputation: 478

The issue seems to be that I didn't have an 'insert' RLS policy in addition to the 'Select' and 'Update' ones. I've assumed the update policy was inclusive of insert but that appears incorrect.

I did a bunch of mucking around swapping to supabase auth, and also rebuilt the userPreferences table so that id was referencing auth.id as a foreign key, and still had the same issue, and so the main change was the insert statement.

Upvotes: 0

Related Questions