I have a Supabase table named "users" that contains a unique key column (text) named "id_rc1" and another column named "version" (text), among other fields all of which are nullable. RLS is enabled with the following two policies (I couldn't find a way to copy the entire policy definitions as text, sorry for the images):

The supabase_flutter INSERT command works fine.

The supabase_flutter UPSERT command never works, whether or not a row exists with the provided key id value or not. It always results in this error: "new row violates row-level security policy for table "users", code: 42501, details: Unauthorized, hint: null"

I created a single Flutter file to demonstrate this, with no UI (to run it, you will have to use your own Supabase credentials in place of REDACTED):

import 'package:flutter/foundation.dart';
import 'package:supabase_flutter/supabase_flutter.dart';

late SupabaseClient supabase;
bool supabaseInitialized = false;
String supabaseProjectApiUrl = ";;
String supabasePublicAnonKey = "REDACTED";

void main() async {
  await initSupabase();
  supabaseUpsertTest();
}

Future<void> initSupabase() async {
  debugPrint("initSupabase START");
  try {
    await Supabase.initialize(url: supabaseProjectApiUrl, anonKey: supabasePublicAnonKey);
    supabase = Supabase.instance.client;
    debugPrint("initSupabase DONE");
    supabaseInitialized = true;
  } catch (err) {
    debugPrint("initSupabase ERROR: $err");
  }
}

Future<void> supabaseUpsertTest() async {
  if (supabaseInitialized) {
    final data = {"id_rc1": "ABC123", "version": "1.0"};
    try {
      debugPrint("Upsert START");
      final response = await supabase.from("users").upsert(data, onConflict: "id_rc1");
      // final response = await supabase.from("users").insert(data);
      if (response.error != null) throw response.error;
      debugPrint("Upsert SUCCESS");
    } catch (err) {
      debugPrint("Upsert ERROR: $err");
    }
  } else {
    debugPrint("Supabase not initialized");
  }
}

The console output of the above code (identical whether or not a row was previously inserted using the key value "ABC123"):

I/flutter (24043): initSupabase START
I/flutter (24043): supabase.supabase_flutter: INFO: ***** Supabase init completed ***** 
I/flutter (24043): initSupabase DONE
I/flutter (24043): Upsert START
I/flutter (24043): Upsert ERROR: PostgrestException(message: new row violates row-level security policy for table "users", code: 42501, details: Unauthorized, hint: null)

What do I need to change in the UPDATE policy or in the Flutter code in order for this to function as expected?

I have a Supabase table named "users" that contains a unique key column (text) named "id_rc1" and another column named "version" (text), among other fields all of which are nullable. RLS is enabled with the following two policies (I couldn't find a way to copy the entire policy definitions as text, sorry for the images):

The supabase_flutter INSERT command works fine.

The supabase_flutter UPSERT command never works, whether or not a row exists with the provided key id value or not. It always results in this error: "new row violates row-level security policy for table "users", code: 42501, details: Unauthorized, hint: null"

I created a single Flutter file to demonstrate this, with no UI (to run it, you will have to use your own Supabase credentials in place of REDACTED):

import 'package:flutter/foundation.dart';
import 'package:supabase_flutter/supabase_flutter.dart';

late SupabaseClient supabase;
bool supabaseInitialized = false;
String supabaseProjectApiUrl = "https://REDACTED.supabase.co";
String supabasePublicAnonKey = "REDACTED";

void main() async {
  await initSupabase();
  supabaseUpsertTest();
}

Future<void> initSupabase() async {
  debugPrint("initSupabase START");
  try {
    await Supabase.initialize(url: supabaseProjectApiUrl, anonKey: supabasePublicAnonKey);
    supabase = Supabase.instance.client;
    debugPrint("initSupabase DONE");
    supabaseInitialized = true;
  } catch (err) {
    debugPrint("initSupabase ERROR: $err");
  }
}

Future<void> supabaseUpsertTest() async {
  if (supabaseInitialized) {
    final data = {"id_rc1": "ABC123", "version": "1.0"};
    try {
      debugPrint("Upsert START");
      final response = await supabase.from("users").upsert(data, onConflict: "id_rc1");
      // final response = await supabase.from("users").insert(data);
      if (response.error != null) throw response.error;
      debugPrint("Upsert SUCCESS");
    } catch (err) {
      debugPrint("Upsert ERROR: $err");
    }
  } else {
    debugPrint("Supabase not initialized");
  }
}

The console output of the above code (identical whether or not a row was previously inserted using the key value "ABC123"):

I/flutter (24043): initSupabase START
I/flutter (24043): supabase.supabase_flutter: INFO: ***** Supabase init completed ***** 
I/flutter (24043): initSupabase DONE
I/flutter (24043): Upsert START
I/flutter (24043): Upsert ERROR: PostgrestException(message: new row violates row-level security policy for table "users", code: 42501, details: Unauthorized, hint: null)

What do I need to change in the UPDATE policy or in the Flutter code in order for this to function as expected?

• flutter
• supabase
• upsert
• rls

• 1 Can you try adding a select rule and see if it solves your issue?, if it doesn't, see if you can update a row from supabase console. – Daniel Onadipe Commented Mar 24 at 19:44
• I was just going to say UPSERT probably requires everything except delete. – Randal Schwartz Commented Mar 24 at 20:46

1 Answer 1

supabase/docs/guides/database/postgres/row-level-security says "To perform an UPDATE operation, a corresponding SELECT policy is required. Without a SELECT policy, the UPDATE operation will not work as expected."

Try adding SELECT permission to see if that fixes it.