RLS policy to read only my "profile" VS read "all names" from "profile" that are on "comments" table
What I have: 2 tables, one called "profiles" with fields like "name", "surname", "avatar_url" and others of my interest, and another called "comments" where is the "text" of the comment and a field called "author_id" that it's the UUID of the user that add the comment.
The problem is with the Policy: right now I have a policy to only "read my own profile" (just like the tutorial or example on the website), so when I want to join "comments" table with my "profiles" table to show on a field the name of the author, I get "null" because I only allowed to fetch my own profile.
We want to keep a level of privacy with the profiles, so no user (for now) should have access to other profiles. And even if I just have the role with the less permission, at least I still want to see the comments of an entity and be able to see the names, ¿any idea how should be tackle this issue?, ¿what it's the best recommendation?
3 Replies
If you want to have some columns accessible to all users then you are better off splitting into two tables one for private_profile with RLS only for auth.uid(). Then public_profile with columns all can read and a select policy of true.
Thank you so much. I just was thinking about the same, but was looking for other options with security invoker, views or functions, but this actually sounds like the easy one and don't see any issues with it (at the end, at least your name on the app should be "public" for the other users)
It is in general more maintainable. You can certainly do an RPC that could bypass the auth.uid() RLS and send back specific columns if you want.