https://supabase.com/ logo
Join Discord
Powered by
Which roles should I grant true value in RLS to ma...
# help
a
Which roles should I grant true value in RLS to make my database function be able to update the table?
n
Hello @arcavid! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates. Want to change the title? Use the ``/title`` command! We have solved your problem? Click the button below to archive it.
g
Can you clarify what you are asking? You mention roles, which can mean alot of things. RLS and database function which are not directly related. And then update on a table.
n
arcavid (2022-05-27)
a
Sorry for the confusion. I wrote a trigger function that runs an update query to a different table.
b
@arcavid if you want all authenticated users to be able to update any row in the table, then you should set the update policy to 
true
. but, there are cases where you would not want users to be able to update every row of the table, so being more specific can help further give some guidance here, e.g. if a user could only update rows that contain a column 
user_id
that matches their 
id
, you could do 
auth.uid() = user_id
, assuming you're calling the function with an authenticated user
a
Actually, two update queries on different tables. One of them works fine and the other doesn't.
It's because the RLS policies are different on the two tables.
I would like to limit my table to be updated only by service supabase or system.
Now I'm not sure which target roles to choose or what expression to set on the policy to achieve it.
g
You just set policy to false if you only want service role to access.
a
And that doesn't work sadly.. service supabase or rpc works fine but not query executed in my trigger function (database function)
g
So your trigger function probably needs "security definer" added.
a
That sounds something new to me. Thanks I will search an example for that!
g
Sorry for rushing, but things going on...
a
That's perfect
Copy code
sql
set search_path = public
is this needed?
My function is still not updating it.
g
It is recommended for any security definer function to have a search_path if it can be called from rpc. a trigger function can not so is probably ok without. Can you provide your entire function?
a
Copy code
sql
create OR REPLACE function comment_counter() returns trigger 
LANGUAGE plpgsql
security definer
SET search_path = public
as $$
begin
  update public.link
  set comment_count = comment_count + 1
  where id = new.link_id;
  return new;
end
$$;
g
Still looking at your code, but with security definer it does not matter what your RLS policy is. You have your policy set to true anyway, so anyone can update it.
a
oh.. sorry
My dashboard must have been stuck with the DNS error.
It kept showing the wrong number.
g
Ha yeah, that is part of why I said I'm hurrying, as lots of other issues right now
a
Perfect, now it updates fine even with no update policy set on the RLS! Everything makes sense now. Thanks for the support. Now I know what security definer is. Thanks!!
g
Just be careful with security definer for rpc call functions as you probably want to do your own checks in the function as it bypasses RLS.
a
Thanks for the warning! Can somebody call a database function using rpc with non-service supabase client? If so, is it even possible to do it to a trigger function?
g
Rpc can be called with anon only key. Trigger functions I think can’t be called but I’ll double check. They would not have a new variable so would at least error if that is used.
PreviousNext
Powered by