databasepostgresqlsupabaserow-level-security

How to restrict RLS on certain column(s) in PostgreSQL/Supabase?


Lets say you have a Supabase database with the following entity:

type Game = {
  id: number | null;
  is_finished: boolean;
  points: number;
};

Authenticated users should be able to change the is_finished boolean, but not the points column. Is there an (easy) way to solve this?

I am currently restricting access for unauthenticated users by adding some RLS policies, but I am not able to define the policies per column.


Solution

  • Regular permission system can do that:
    demo at db<>fiddle

    grant select,delete,insert,references(id,is_finished,points)on game to the_user;
    grant update(id,is_finished)on game to the_user;--`update` on points not granted
    

    It's important to be specific. Similar to how permissive RLS policies work, if you were to first/also grant all or individual rights on all columns of the table, then only revoke update(points) on that one, it would have no effect: the revoke and the grant on the column would get OR'd so effectively the user would get in.