RLS performance
RLSHi @garyaustin
Hope you dont mind the mention, but I think you authored this: https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations
At what point would you recommend switching between these options:
A: Have a definer RPC get all rows that determine access and comparing it in RLS: array [id] <@ (select api.get_profile_ids_in_my_school()))
B: Joining to find if the profile and you have a school in common
Because option A is what is recommended in the docs, but shouldn't there be some nuance, or is it really always the better option.
Because option A seems great under X amount of rows returned by the RPC. But lets say there are 10k profiles in your school. Is it then still the better option or should I just join at that point or maybe at a param to get_profiles_ids_in_my_school to help filter it down.
For your refrence get_profile_ids_in_my_school gets the overlap of the user making the request with admin role and all other profiles that have the school_id in common:
create table api.profiles_schools
(
profile_id bigint not null references api.profiles (id) on delete cascade,
school_id bigint not null references api.schools (id) on delete cascade,
is_primary boolean not null default false,
roles api.user_role[] not null check (cardinality(roles) between 1 and 3),
constraint check_student_role_combination check ( (not ('student' = any (roles))) or (cardinality(array_remove(
array_remove(roles, 'student'::api.user_role), 'payment'::api.user_role)) = 0) ),
primary key (profile_id, school_id)
);
Hope you dont mind the mention, but I think you authored this: https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations
At what point would you recommend switching between these options:
A: Have a definer RPC get all rows that determine access and comparing it in RLS: array [id] <@ (select api.get_profile_ids_in_my_school()))
B: Joining to find if the profile and you have a school in common
Because option A is what is recommended in the docs, but shouldn't there be some nuance, or is it really always the better option.
Because option A seems great under X amount of rows returned by the RPC. But lets say there are 10k profiles in your school. Is it then still the better option or should I just join at that point or maybe at a param to get_profiles_ids_in_my_school to help filter it down.
For your refrence get_profile_ids_in_my_school gets the overlap of the user making the request with admin role and all other profiles that have the school_id in common:
create table api.profiles_schools
(
profile_id bigint not null references api.profiles (id) on delete cascade,
school_id bigint not null references api.schools (id) on delete cascade,
is_primary boolean not null default false,
roles api.user_role[] not null check (cardinality(roles) between 1 and 3),
constraint check_student_role_combination check ( (not ('student' = any (roles))) or (cardinality(array_remove(
array_remove(roles, 'student'::api.user_role), 'payment'::api.user_role)) = 0) ),
primary key (profile_id, school_id)
);
