How to design security policies for a following system including counters in postgres/supabase if postgres functions are used?

Question

I am unsure how to design security policies for a following system including counters in postgres/supabase. My database includes two tables:

Users:
uuid|name|follower_counter
------------------------------
xyz |tobi|       1

Following-Relationship
follower| following
---------------------------
 uuid_1 |   uuid_2

Once a user follows a different user, I would like to use a postgres function/transaction to

1. Insert a new following-follower relationship

2. Update the followed users' counter

   BEGIN
       create follower_relationship(follower_id, following_id);
       update increment_counter_of_followed_person(following_id);
   END;
   

The constraint should be that the users table (e.g. the name column) can only be altered by the user owning the row. However, the follower_counter should open to changes from users who start following that user.

What is the best security policy design here? Should I add column security or should exclude the counters to a different table?

Do I have to pass parameters to the "block transaction" to ensure that the update and insert functions are called with the needed rights? With which rights should I call the block function?

Answer 1

It might be better to take a different approach to solve this problem. Instead of having a column dedicated to counting the followers, I would recommend actually counting the number of followers when you query the users. Since you already have Following-Relationship table, we just need to count the rows within the table where following or follower is the querying user.

When you have a counter, it might be hard to keep the counter accurate. You have to make sure the number gets decremented when someone unfollows. What if someone blocks a user? What if a user was deleted? There could be a lot of situations that could throw off the counter.

If you count the number of followings/followers on the fly, you don't need to worry about those situations at all.

Now obvious concern with this approach that you might have is performance, but you should not worry too much about it. Postgres is a powerful database that has been battle tested for decades, and with a proper index in place, it can easily perform these query on the fly.

The easiest way of doing this in Supabase would be to create a view like this the following. Once you create a view, you can query it from your Supabase client just like a typical table!

create or replace view profiles as
    select 
      id,
      name,
      (select count(*) from following_relationship where followed_user_id = id) as follower_count,
      (select count(*) from following_relationship where following_user_id = id) as following_count
    from users;