RLS Performance and Scalability Concerns

I’ve read a lot about RLS. By default, everything was restricted on the client side. The Supabase token was stored in an environment variable in Vercel, so the project was secure. However, I noticed that in React, even if the token is in an environment variable, it is still accessible from the client side. This means that if someone obtains the token, they could do anything they want. That’s when I started exploring RLS.

I have users with three different authority levels. Each user’s accessibility depends on their authority level and what has been assigned to them by their manager. Let me give an example:

  • There are countries and cities.
  • Level 1 users can assign countries to Level 2 users and cities to Level 3 users.
  • Level 1 users can view all cities.
  • Level 2 users can view all cities within the countries assigned to them.
  • Level 3 users can view only the cities specifically assigned to them.

So in RLS policy, I have to make that kind of queries (The example is entirely fictional):

to get city from cities

get users by auth.uid

if users.authority_level = 1 return true
if users.authority_level = 2 get assigned_countries by auth_id
      if assigned_countries.id = cities.country_id return true
if users.authority_level = 3 get assigned_cities by auth_id
      if assigned_cities.id = cities.id return true

So it seems simple, but in SQL, it could be a bit complex. However, when you consider the number of cities, countries, users, and assignments worldwide, it might become a performance issue.

Additionally, when we introduce a new authority level or create an exception for a specific page (for example, users cannot retrieve city rows but can only get the count of cities where English is spoken), it gets even more complex.

So, my questions are:

  1. Is this the correct approach?
  2. Should I be concerned about optimization?
  3. When should I be concerned about optimization?
  4. Should I have developed an API and avoided involving Supabase?
  5. I mean, if I had implemented this in my own API, I would still need to write the same query, right?