Stop! Can I have your attention for a moment?

Do you want to change your life? Join me on my 50-Day Life Transformation Challenge. Get involved! It won't cost you anything except a few bad habits.

Supabase throws "new row violates row-level security policy for table" even though I had created a row level policy for inserts

I was confronted with a Supabase problem that (almost) drove me crazy: I have a simple table "projects" that only stores a project uuid and a project name. While developing on my Web-App I disabled role level security (RLS) for this table. I usually proceed disabling row level policies in the first phase of developing as it helps me to focus on the frontend and the general project scheme.

Inserting data in the table throws a row-level security alert

As I enabled row-level security on my "projects" table and trying to insert new data I received the following error:

new row violates row-level security policy for table "projects"

This is how my policy looks like:

Supabase row level security policy

This is a simple rule that checks if the user is authenticated while inserting new data. I took the rule from the example rules provided by the Supabase Team itself.

Solution: Add a SELECT policy

I found out that Supabase (or Postgres?) seems to not only INSERT the data you provide but also directly tries to SELECT it. So if you don't have a SELECT policy also the INSERT process fails.

In my case this is not quite easy to solve: I have another table called project_members where I reference all users that are related to the project. So at first time when inserting new data to the projects table there is no data in the project_members table. This leads to a situation where the SELECT policy for my projects table always fails on the first INSERT.

But fortunately there is another way:

Solution 2: Modify insert statement

Just add { returning: 'minimal' } to your insert statement. This suppresses the direct selection of the added data. The complete statement looks like this:

await supabase.from('sample').insert({ key: 'value' }, { returning: 'minimal' })

This works perfectly!

Tags

Comments

Subscribe to my Sunday thoughts

Join a growing community of friendly readers. Every Sunday I share my thoughts about rational thinking, productivity and life.