How can I disallow any read or writes on a table using RLS but still allow a function to return a value from that table? I want to restrict reads to being read only through a function but if I turn on RLS with just a

false

policy it fails the function as well.

What's the use case for this as it feels a bit dangerous

Wanting to verify the request via captcha before returning a promo code. Don’t want to have the promo code table public but need to call the verify function from client side

You can add "security definer" to your function and then it has the privileges of the creater/owner of the function rather than the user, so can bypass RLS. BUT you better make sure what you return in that function is public, or do some security in the function.

If you can add, or already have, a backend you can always use the service_role secret to bypass RLS. I use Cloudflare Workers for something similar and it works great.

I don't have a backend so I was trying to use the function as a backend :/ that would be a very good solution though

so putting security definer after language type in the function says run with privileges of owner of function. If you create the function in the sql editor the superuser there ignores RLS, so to will the function. I think the function editor in the database page of the ui also bypasses RLS for its user, but I don't use it so not sure.

that makes a lot of sense I'm still confused how to implement that though. How do I define the function as a security definer?

create function room_exists(roomkey text) returns boolean security definer language plpgsql as $$ begin if exists( SELECT 1 FROM room_joins WHERE room_key=roomkey ) then return true; else return false; end if; end; $$; this function lets me see if a room key is in a table even if the userid does not match my RLS uid=auth.uid(). It would bypass RLS = false also.

Ooh I get it. I need to separate my validation logic into a security function and then my other one will work. Thanks!

Hmmm. Not really sure what you mean by validation logic and other one. I have RLS pretty much locked up to prevent table access and use this function for my logic to get around the RLS and do something I'm not worried about the result being public. I assumed you would do your coupon stuff in a security definer function so it could access the protected table and your RLS would insure no one else could access table. Security Definer does not mean it defines security actually, it means the function runs with the privileges of the creator of the function (supabase_admin in sql editor) and not the user (who can't bypass RLS) calling the function.

Oh i think I understand what you mean now. So I can return a value from them?

Yes in the above I returned a boolean true, but you can return text, table ,json, etc. just google postgres functions returning x. You could also just call a security definer function from another function where the security definer gets your data from the table for use in another function. I think though you might need to eventually show a function with what you are trying to do for help if you are trying to sort this... based on your questions so far. Good luck

Okay I totally get it now. I didn’t realize you could return a value with a security definer function and was getting stuck because I was thinking about returning values and the security function as two separate things. Thank you!

yeah sorry, the function I happen to have handy in my code returned a boolean.

Yeah I didn't see the returns statement on the first line. My bad for reading too fast