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

[donPuerto]

Hi I'm having trouble updating my User Table. If I disabled RLS well it has no problem it will update on the table.
User table

-- Drop Tables
DROP TABLE IF EXISTS public.users;

-- Drop users Types 
DROP TYPE public.USER_STATUS;


-- Create Custom Types
CREATE TYPE public.USER_STATUS AS ENUM ('Active', 'Away', 'Do not disturb', 'Invincible');

-- Create User Table
CREATE TABLE IF NOT EXISTS public.users  (
id  	UUID REFERENCES auth.users NOT NULL PRIMARY KEY, -- UUID from auth.users
first_name	TEXT, 
last_name 	TEXT,
username 	TEXT,
email   	varchar(50) NOT NULL,
status  	USER_STATUS DEFAULT 'Active'::public.USER_STATUS,
is_active	BOOLEAN DEFAULT FALSE,
is_deleted	BOOLEAN DEFAULT FALSE,
deleted_at 	TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
created_at 	TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
updated_at 	TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
UNIQUE	(username, email)
);
comment on table public.users is 'Users Table';

-- Enable Row Level Security
alter table public.users enable row level security;

-- Enable Public Access
create policy "User can view own user data"
  on users for select
  using ( auth.uid() = id );

create policy "User can insert own user data."
  on users for insert
  with check ( auth.uid() = id );

create policy "User can update own user data"
  on users for update
  with check ( auth.uid() = id );

RLS Policy

Updating User Table

const { data, error } = await supabase
      .from("users")
      .upsert(
        {
          first_name: "Don",
          last_name: "Puerto",
          username: "donpuerto",
          email: "[email protected]",
          is_active: "false",
          user_status: "Active",
          is_deleted: "false",
        },
        { returning: "minimal" }
      )
      .eq("id", sessionUser.id);

    if (error) throw error;

    return data;

[GaryAustin1]

Update RLS policy requires a Using statement. With check is optional.

[SleeplessFox]

Do you have an example? Im running into the same issue.

[richleach]

Did you solve this? I'm having the same issue too

[GaryAustin1]

The Op for sure had the wrong policy for update.

The "error" just means you are not meeting RLS. Either not signed in or your policies are not met.
Upsert requires, insert, update and select policies all be met as it can do insert or update depending on what it hits.

[mrmlb]

I had this problem on a table with RLS. I fixed it by changing the value of 'user_id' in the table being upserted into to auth.uid().

[Blackwidow-sudo]

I just ran into a similar problem.
Somehow when using upsert() the policy is violated, but when using update() it works.

[saltcod]

100%

Just spent 2 hours on this — thanks for posting!

[GijsSi]

This was indeed the problem. Thanks for posting!

[3210jr]

Just figured it out, the docs say: "Primary keys must be included in values to use upsert." so if the "id" column is your primary key then it must be present in the query.

The example from the OP doesn't seem to include the id field (if he had one).

[michaelessiet]

Thank you!! This saved me from an entire refactor

[OFFBLACKgroup]

I ran into similar issue (using the supabase JS client not SQL) where I didn't enable the correct policies for INSERT, UPDATE, DELETE. (I skipped the DELETE one and that caused the issue)

[richleach]

I DID fix my issue, I think what happened was I built some code originally from older docs/tutorials and had built out my own custom supabase client, which stopped working for some reason (still haven't had the time to diff the 2 files but will down the road). I make sure to use

const supabaseClient = useSupabaseClient();

in my code before my queries...

and import { useUser, useSupabaseClient } from '@supabase/auth-helpers-react'

(at the top of the file)

HTH,

Rich

[Blackwidow-sudo]

I fixed mine too.
I didnt notice the comment in the docs that says that upserts only work with RLS when the primary key is provided

[jacktsin1]

That should be more clear in the docs... I've lost about 7 hours trying to figure out why upserts don't work while all other operations do.

Also, It's not the Primary key that must be present on the upserted item(s) in order for this to work with RLS, but also whatever key is used to validate the RLS condition (eg. a user_id or a boolean).

[michaelessiet]

FFS why wouldn't they make this clear in the docs?!🤦‍♂️

[purwnt]

Adding a policy seems like the best way, with this you don't need to disable the RLS policy and don't need to use a secret key which is very unsafe for your data.

To do this,

1. Go to "Authentication" > "Policies" > Select the "New Policy" table
   

2. Then add from the template for policy insert data.
   

3. Follow the next steps
   

4. Review your policy

5. Save New Policy

[Yusuf-shaik]

This helped, thank you!

[zeekrey]

I followed the tutorial in the Supabase Docs and the following policy is set:

create policy "Profiles are viewable by users who created them."
on profiles for select
using ( auth.uid() = id );

This policy only allows reading entries. To also allow writing data, the policy can be set as follows:

create policy "Profiles are viewable, editable, deletable by users who created them."
on profiles for all
using ( auth.uid() = id );

You can find a detailed description here: https://www.postgresql.org/docs/current/sql-createpolicy.html

[hmtcelik]

additionally, if someone getting this error when he wanna get some files from a new bucket (you created)
you should add some policies about the bucket, because after creating a new bucket, there is no policy by default,

Here's the location of adding some policies into your bucket

1. you should go to dashboard > storage > policies
2. and click 'New Policy' (on your bucket name in the list)

[tddawson]

I was doing a quick and dirty test of inserting data so I created a blanket policy to allow INSERT. I was puzzled when I still got this error. My problem was that immediately after my .insert() I was calling .select() and I hadn't set up a READ permission yet. Once I took care of that everything worked as expected.

[Hoag89]

can you explain what you mean by read permission ?

[GaryAustin1]

If you have RLS on you need a select policy and meet it.

[Hoag89]

yea but that is the thing i turned off RLS for this project. i dont get it. i even made some policies that allow adding and deleting but it still gives me this error:

CreatePost.jsx:28 Error uploading image:
{statusCode: '403', error: 'Unauthorized', message: 'new row violates row-level security policy'}
error
:
"Unauthorized"
message
:
"new row violates row-level security policy"
statusCode
:
"403"

[Hoag89]

[GaryAustin1]

You can't turn off RLS for storage (at least on hosted system). Not sure what you mean by that.
You must have policies in the storage section of the UI, or use SQL to generate them on storage.objects or you can't upload.
Edit: Unless you are using service_role from server side only.

For upload you need an insert policy.

[Rj-Aman]

I was getting same error. Used pgsql. After lots of research i got to know that we need to add trigger for the respective table.

-- Drop the trigger function
DROP FUNCTION set_tenant_id_on_insert;

-- Create the trigger function
CREATE OR REPLACE FUNCTION set_tenant_id_on_insert()
RETURNS TRIGGER AS $$
BEGIN
    NEW.tenant_id := current_setting('my.tenant_id');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql; 

-- Register the trigger
CREATE TRIGGER insert_tenant_id_into_credentials
BEFORE INSERT ON credentials
FOR EACH ROW
EXECUTE FUNCTION set_tenant_id_on_insert();

[valvcnt]

Hi i need your help.
When i insert a row the error is : Error: Exception: new row violates row-level security policy for table "users"
but the policy is :

CREATE POLICY "Allow insert for unauthenticated users"
ON users
FOR INSERT
WITH CHECK (true);

and my code is that :

import 'package:supabase_flutter/supabase_flutter.dart';

class DbService {
  final SupabaseClient _client = Supabase.instance.client;

  Future<void> createUser(String phoneNumber) async {
    print('Creating user with phone number: $phoneNumber');
    final response = await _client
        .from('users')
        .insert({
          'phone_number': phoneNumber,
        })
        .execute(); // Execute the query and get the response

    if (response.error != null) {
      throw Exception(response.error!.message);
    }
  }

  Future<Map<String, dynamic>?> getUserByPhoneNumber(String phoneNumber) async {
    final response = await _client
        .from('users')
        .select()
        .eq('phone_number', phoneNumber)
        .single()
        .execute(); // Execute the query and get the response

    if (response.error != null) {
      throw Exception(response.error!.message);
    }

    return response.data as Map<String, dynamic>?;
  }

  Future<void> updateUser(String id, String fullName, String email, String industry) async {
    final response = await _client
        .from('users')
        .update({
          'full_name': fullName,
          'email': email,
          'industry': industry,
        })
        .eq('id', id)
        .execute(); // Execute the query and get the response

    if (response.error != null) {
      throw Exception(response.error!.message);
    }
}
}

and my widget is that :

// ignore_for_file: use_build_context_synchronously

import 'package:flutter/material.dart';
import 'package:flutter_riverpod/flutter_riverpod.dart';
import 'package:somom/providers/db_provider.dart';
import 'package:somom/providers/auth_provider.dart';

class PhoneInputScreen extends ConsumerStatefulWidget {
  @override
  PhoneInputScreenState createState() => PhoneInputScreenState();
}

class PhoneInputScreenState extends ConsumerState<PhoneInputScreen> {
  final _formKey = GlobalKey<FormState>();
  final TextEditingController _phoneNumberController = TextEditingController();

  @override
  Widget build(BuildContext context) {
    final dbService = ref.read(dbProvider);
    final authService = ref.read(authProvider);

    return Scaffold(
      appBar: AppBar(title: Text('Enter your phone number')),
      body: Padding(
        padding: EdgeInsets.all(16.0),
        child: Form(
          key: _formKey,
          child: Column(
            children: <Widget>[
              TextFormField(
                controller: _phoneNumberController,
                decoration: InputDecoration(
                  labelText: 'Phone number (with country code)',
                  hintText: '+11234567890',
                ),
                validator: (value) {
                  if (value == null || value.isEmpty) {
                    return 'Please enter your phone number with country code';
                  }
                  final phoneRegExp = RegExp(r'^\+\d+$');
                  if (!phoneRegExp.hasMatch(value)) {
                    return 'Please enter a valid phone number with country code';
                  }
                  return null;
                },
              ),
              ElevatedButton(
                onPressed: () async {
                  if (_formKey.currentState!.validate()) {
                    final phoneNumber = _phoneNumberController.text;
                    try {
                      print('Creating user with phone number: $phoneNumber');
                      await dbService.createUser(phoneNumber);
                      print('Sending verification code to: $phoneNumber');
                      await authService.sendVerificationCode(phoneNumber);
                      if (!mounted) return;
                      Navigator.pushNamed(context, '/verification', arguments: phoneNumber);
                    } catch (e) {
                      print('Error: $e');
                      if (mounted) {
                        ScaffoldMessenger.of(context).showSnackBar(SnackBar(content: Text('Failed to send verification code')));
                      }
                    }
                  } else {
                    print('Form validation failed');
                  }
                },
                child: Text('Next'),
              ),
            ],
          ),
        ),
      ),
    );
  }
}

i don't understand please help me

[mrtns]

The following two policies allow me to successfully issue an UPSERT command in both the insert and update case:

CREATE POLICY "Authenticated can insert" ON x FOR INSERT TO authenticated WITH CHECK (true);
CREATE POLICY "Authenticated can update" ON x FOR UPDATE TO authenticated USING (true);

Previously I was encountering this exception:

  error: {
    code: '42501',
    details: null,
    hint: null,
    message: 'new row violates row-level security policy (USING expression) for table "x"'
  }

and I had to change

CREATE POLICY "Authenticated can update" ON x FOR UPDATE TO authenticated WITH CHECK (true);

to

CREATE POLICY "Authenticated can update" ON x FOR UPDATE TO authenticated USING (true);

[sanjeevkumarraob]

I am having the same issue and I am using the above recommendations. I am using nodejs to connect to the supabase and using "createclient" as below to create the connection. I am using public schemas for my tables and I have a reference key for user_id for auth.users.id in my table.

const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_ANON_KEY
);

Error creating trip: {
   code: '42501',
   details: null,
   hint: null,
   message: 'new row violates row-level security policy for table "X"'
 }

[sanjeevkumarraob]

@gc-ft ☝️ Uploaded the tables, query and the policies

[himeshp]

If you remove it, does it work? @sanjeevkumarraob

      .select()
      .single();

[sanjeevkumarraob]

No. It doesn't work whatsoever. IF I just make the using condition as true, then it works. (which I don't want). I suspect the auth.uid() is not populating properly. I tried with typecasting as well, converting auth.uid()::text and still it fails.

[sanjeevkumarraob]

My flow:
Authenticate using supabase authentication -> send the accessToken to node server as Authorization header -> do CRUD operations.

A simple way of accessing DB is creating a nightmare for me with RLS policies. And I am not using complex logic :(

[nitoorBar]

@sanjeevkumarraob i have exactly the same issue
you reach a solution ?

[euphydev]

Hi! I found a solution on my end
Basically, I just added 2 policies for my USERS table for INSERT and SELECT.
Let me know if this works!

[IbrahimSam96]

Thank you