RLS policy set but message: "new row violates row-level security policy for table xxxxxx"
I attempt to insert a record via DB function using RPC. RLS is set on table and I send a access-token tied a authenticated user. got error.
I've built another DB function to ensure the user from access-token is really authentificated. I get
{
....
"aud": "authenticated",
"role": "authenticated",
...
}
Tried this RLS :
create policy "Enable users to handle their own data only"
on "public"."organisation"
to authenticated
using (
(( SELECT auth.uid() AS uid) = user_id)
);
Still get error.
Do you need more data ?
what sould I do ? ...something I've been mistaken with ?
36 Replies
Is user_id a UUID column or text?
user_id is a UUID yes (native to supabase)
so follows the type in the user_id column in xxxx table
do you think of a type cast to apply ?
Not sure what native to supabase means. But if it is type UUID then your policy looks correct.
Have you tried impersonation in the SQL editor or table UI to see if you can access as a user? Do you have any other policies on the table? Otherwise you likely don't really have a session when you make the call. You could also do auth.getSession() right before the insert to see if you have a session.
Have you tried impersonation in the SQL editor or table UI to see if you can access as a user? Do you have any other policies on the table? Otherwise you likely don't really have a session when you make the call. You could also do auth.getSession() right before the insert to see if you have a session.
Not sure what native to supabase means. But if it is type UUID then your policy looks correct.Supabase comes with built-in User with uuid type
Yes but you created the column in your table.
when I pick the user I query with,
and I impersonate th user iun SB admin,
records no longer appear
Yes, I created the user_id column in organisation table
but to please the RLS
I would like that any user authenticated can insert a organisation
ok I impersonated
If I try to add a record : "violation...."
whereas th user has role "authenticated"
Your policy only allows a user to insert if user_id matches auth.uid(). If you don't want that then just have to authenticated and true for the policy.
Your current policy requires user_id to be the user's auth.uid() either by default setting or passed in on the insert.
create policy "Enable insert for authenticated users only"
on "public"."organisation"
as PERMISSIVE
for INSERT
to authenticated
with check (
true
);
That allows any user to insert. You may need the same on select depending on how you are inserting.
way is that I want the user A to insert his organisation
but allowed only to see this one.
Select policy of to authenticated, USING true allows all users to see any company.
Your insert allows anyone to insert to the table also.
If you do both then not clear why you have user_id at all in the table for RLS at least.
my goal is :
any authed user can create a organisation
each user can see only his very own organisation
Then your very first policy was correct.
You just have to fill in user_id with the user UUID
so I guess I need to have user_id column in organisation
Yes and it needs to be set to the user's id.
You can set it by passing it in on insert or use the default column setting to auth.uid().
if this query :
insert into organisation (..., user_id)
values(..., xxxxxxxxxxx)
the RLS is able to match the user_id(xxxxx) of the SQL statement
with auth.uid() ?
and should be sucess ?
Yes.
Try it in impersonation mode
in RLS : target role shoud be "authenticated" isnt it ?
Yes.
permissive or restrictive ?
permissive or nothing
That policy does what you describe you want.
I would specify it for each operation though just to make things clear.
And then insert would need to be WITH CHECK if individual.
whit Imperonation :
from the admin UI, I create a record, and set the user_id
I can now see this organisation . not the other
But from the API (postman) I still cannot
despites I've put the apikey and the bearer access-token
https://myinstance.supabase.co/rest/v1/rpc/upsert_organisation
You are using upsert...
?
If you are doing an rpc call then you should show the code for that.
insert into organisation .....
ON CONFLICT (siret)
DO UPDATE
SET siret = EXCLUDED.siret
RETURNING id INTO new_organisation_id;
You also need to meet update policy and select.
ok so same as insert but "for UPDATE" I guess
Update requires USING with optional WITH CHECK
Your jwt (non expired and assuming a Supabase jwt from auth) would go in the Authorization header as 'bearer jwt'. apikey is anon key.
just "with check (true)" or "using (auth.uid = user_jd)" too ?
I would always use auth.uid() = user_id if you want a user to deal with just their own row. Because of other policies it might not be needed (like the select having it) but it makes things clear.
yes that why with_check (true) look not right.
something that weird is that :
I create a org that already exists : I get the generic error message "new row violates RLS..." but I dont know what was wrong : - company already exists - user has wrong role - user is attempting to update a company he dont own. I would like to avoid putting a user_id column in every table. But I think there are only two ways for RLS : - user_id in the table A - table A inner join table B if table B has user_id and do matching with Auth.uid() = tableB.user_id
I create a org that already exists : I get the generic error message "new row violates RLS..." but I dont know what was wrong : - company already exists - user has wrong role - user is attempting to update a company he dont own. I would like to avoid putting a user_id column in every table. But I think there are only two ways for RLS : - user_id in the table A - table A inner join table B if table B has user_id and do matching with Auth.uid() = tableB.user_id
If you have a company table with user_id then you can always use that in RLS if you have a company_id with the data in other tables. You do a select join from the company table with the user id. You have to have some unique identifier in every table.
ok thats it : user_id or join to the table that user_id
for xample :
posts have user_id
comment dont have
RLS on post : based on user_id
RLS on comments based on : join comments.post_id = post.id
and auth.uid() = post.user_id
otherwise :user_id everywhere.
Comments RLS would likely just be authenticated true or public true. Then when you query the post comments gets joined with the post. Post would probably be select authenticated true (so all signed up users can see) and insert/update with auth.uid() if you allow editing and want to have an owner for the post.
Ohh ok I get that better.
Was struggling with RLS and was somewhat blocking
Thxxx