Supabase error new row violates row-level security policy for table

Question

If I try to insert a new row into the table, I get an error: "New row violates row-level security policy for table".

I have a table "user_profiles" that have a foreign key "user_id" to auth table "auth.users.id"

I am using "supabase.auth.onAuthStateChange" hook where I want to add new row when user logs in.

In policies section, I have RLS enabled, but I didn't add any policies.

Then, I tried to add a policy from template that only authenticated users can add a row, but it didn't change anything.

Then, I came across this SO question and and tried to add a policy from template that only authenticated users can select, but I am still getting the same error.

I implemented authentication in chrome browser extension in background script:

supabase.ts

import { createClient } from '@supabase/supabase-js'
import qs from 'qs'

const supaUrl = import.meta.env.VITE_SUPABASE_URL
const supaANONKey = import.meta.env.VITE_SUPABASE_ANON_KEY
export const supabase = createClient(supaUrl, supaANONKey, {
  global: {
    fetch: (...args) => fetch(...args),
  },
})

export const extensionSupabaseLogin = async () => {
  const redirectUri = chrome.identity.getRedirectURL('supabase-auth')
  const options = {
    provider: 'notion',
    redirect_to: redirectUri,
    ux_mode: 'redirect',
  }
  const url = `${supaUrl}/auth/v1/authorize?${qs.stringify(options)}`

  const originalTab = (await chrome.tabs.query({ active: true, currentWindow: true }))[0]
  const authenticationTab = await chrome.tabs.create({ url: 'about:blank' })
  chrome.tabs.onUpdated.addListener(function notionAuthHook(tabId, _changeInfo, tab) {
    if (tabId === authenticationTab.id && tab.url?.startsWith(redirectUri)) {
      const splitUrl = tab.url?.split('#')[1]
      // Prompt user to sign in again if unparsable (error)
      if (!splitUrl) {
        console.error('Unparsable sign-in URL', tab.url)
        return
      }
      // Parse tokens from URL
      const authResult = qs.parse(splitUrl)
      const refreshToken = authResult.refresh_token as string
      const accessToken = authResult.access_token as string

      // Prompt user to sign in again if no access_token (error)
      if (!refreshToken || !accessToken) {
        console.error('No access token or refresh token returned', authResult)
        return
      }

      ;(async () => {
        const { error } = await supabase.auth.setSession({
          access_token: accessToken,
          refresh_token: refreshToken,
        })
        if (error) console.error(error)
      })()

      chrome.tabs.onUpdated.removeListener(notionAuthHook)
      chrome.tabs.remove(tabId)
      if (originalTab?.id) {
        chrome.tabs.update(originalTab.id, { active: true })
      }
    }
  })
  // Send users to the sign-in flow
  if (authenticationTab.id) {
    await chrome.tabs.update(authenticationTab.id, { url: url })
  }
}

background.ts

import { Client } from '@notionhq/client'
import { extensionSupabaseLogin, supabase } from './supabase'

chrome.runtime.onMessage.addListener((request, sender, sendResponse) => {
  if (request.action === 'signIn') {
    extensionSupabaseLogin();
  } else if (request.type === 'GET_NOTION_PAGE') {
     const { accessToken, ...options } = request.options as GetPageNotionOptions
     debugger
     if (!notionClient) {
       setNotionClient(accessToken)
     }
     if (notionClient) {
       const response = await notionClient.pages.retrieve({ ...options })
       sendResponse({ type: 'SUCCESS', data: JSON.stringify(response) })
     }
  }
});

let notionClient: Client | null = null

const setNotionClient = (notionCode: string) => {
   notionClient = new Client({
      auth: notionCode,
   })
}

const storeNotionIdentity = async (notionIdentity: { accessToken: string; refreshToken?: string }): Promise<void> => {
  setNotionClient(notionIdentity.accessToken)
  await chrome.storage.local.set({
    notionIdentity,
  })
}

const storeNotionUser = async (user: User): Promise<void> => {
  await chrome.storage.local.set({
    user,
  })
}

const setupAuthListener = () => {
    supabase.auth.onAuthStateChange(async (event, session) => {
      if (event === 'SIGNED_IN' || event === 'USER_UPDATED') {
        //this user has just signed in or their session has been updated
        console.log('user is signed in :', session)
        if (session?.access_token && session?.refresh_token) {
          debugger
          await storeNotionIdentity({ accessToken: session.access_token, refreshToken: session.refresh_token })
        }
        if (session?.user) {
          await storeNotionUser({
            name: session.user.user_metadata?.name,
            fullName: session.user.user_metadata?.full_name,
            email: session.user?.email ?? session.user.user_metadata?.email,
            userId: session.user.id,
          })
        }
        if (typeof loginTabId === 'number') {
          chrome.tabs.sendMessage(loginTabId, `notion login success`)
        }
      } else if (event === 'SIGNED_OUT') {
        //the user has just signed out or their account has been deleetd
        console.log('user is signed out')
        loginTabId = null
        await chrome.storage.local.remove(['notionIdentity', 'user'])
      }
    })
  }
  
  setupAuthListener()

Answer 1

For me adding auth.uid() as the default value user_id column from the suggested expressions helped

Answer 2

It is recommended to create a trigger for inserting a row in the user_profiles table upon a new signup. https://supabase.com/docs/guides/auth/managing-user-data#advanced-techniques

Here is an sample:

-- inserts a row into public.profiles
create function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public. user_profiles(id)
  values (new.id);
  return new;
end;
$$;

-- trigger the function every time a user is created
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();