You don't always need RLS

Sometimes - you don't need RLS. As long as RLS is enabled on the table - you can think about not applying any RLS policies.

Instead - you can manage access to the data using traditional server-side code. This is useful if you want to apply more complex access rules to your data or when the data is not directly updated by the client - but instead by a server-side process (such as a cron job or a webhook).

Basic

User can access the rows with their own id in the row

CREATE POLICY policy_name_or_description ON your_table_name
    FOR SELECT // method
    USING (user_id = current_user_id()); // definition

Basically you write a boolean definition in USING block.

Referencing another table

User can access to the whole table if user’s id is in another referencing table

CREATE POLICY policy_name_or_description ON your_table_name
    FOR SELECT
    USING (EXISTS (SELECT 1 FROM membership WHERE membership.user_id = current_user_id()));

Use database functions

Define a function

Official docs from Supabase:

https://supabase.com/docs/guides/database/functions

Below is an example from MakerKit docs:

https://makerkit.dev/docs/next-supabase/row-level-security

create or replace function get_current_user_role(org_id bigint)
returns int as $$
declare
    user_role int;
begin
    select role
        from memberships
        where organization_id = org_id and user_id = auth.uid()
        into user_role;
 
    if user_role is null then
        raise exception 'User is not a member of the organization';
    end if;
 
    return user_role;
end;
$$ language plpgsql;

Use functions

Haven’t test yet, where is this organization_id from?

create or replace policy "Only the owner can update tasks"
    on tasks
    as restrictive
    for update
    to authenticated
    using (
        get_current_user_role(organization_id) >= 1
    ) with check (
        get_current_user_role(organization_id) >= 1
    );