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: "don.puert@hotmail.com",
          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!

[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).

[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();