Common Row-Level-Security Policies you can apply to your data

Supabase uses RLS policies to protect your data. RLS are useful for two main reasons:

1. Supabase exposes your database directly to your clients. This means that your clients can query your database directly. RLS policies allow you to restrict what data they can access.
2. You can use SQL policies to apply access rules to your data. This helps you to keep your data secure without having to write any additional code.

In this document, we'll list some common RLS policies you can apply to your data in Makerkit.

You can copy and paste some of this (provided the table names match) into your database so that you can get started quickly.

Common RLS Policies

Restricting access to a table based on a column

This policy will restrict access to a table based on a column. In this example, we're restricting access to the users table based on the id column.

create policy "Restrict access to user tasks to their own tasks"
  on tasks
  for select
  to authenticated
  using (user_id = auth.uid());

Restricting write access to a table based on a column

This policy will restrict write access to a table based on a column. In this example, we're restricting write access to the users table based on the id column.

create policy "Restrict write access to authenticated users"
  on users
  for insert
  to authenticated
  with check (id = auth.uid());

Restrictive Policies

If you want these rules to be restrictive (i.e. they apply to all queries), then you can add as restrictive:

create policy "Restrict write access to authenticated users"
  on users
  as restrictive;
  for insert
  to authenticated
  with check (id = auth.uid())

This means that if you have 2 select policies, one restrictive and one permissive, then the restrictive policy will take precedence. If the restrictive policy fails, then the permissive policy will not apply.

This is useful when a policy needs to apply to all queries.

Subscriptions and RLS

RLS policies are applied to subscriptions. This means that if you have a subscription that returns data that the user doesn't have access to - then the subscription will fail.

Let's see how we can use RLS for restricting access to subscriptions.

This is very dependent on your use case. You'll need to think about how you want to restrict access to your data. But for the sake of this example, let's assume that we want to restrict how many tasks a user can write to the database.

Storing your Plans in the database

First, we'll need to store our plans in the database. We'll create a plans table that stores the id, name, and max_tasks for each plan.

You have two options here when talking about Stripe:

1. assign a plan to a Product ID
2. assign a plan to a Price ID

If you need different quotas for monthly and yearly plans - then you'll need to assign a plan to a Price ID. If you don't need different quotas for monthly and yearly plans - then you can assign a plan to a Product ID.

In the example below, we're assigning a plan to a Price ID.

create table plans (
  name text not null,
  price_id text not null,
  task_quota int not null,
  primary key (product_id)
);

We also allow read access to this table for all authenticated users:

create policy "Allow all authenticated users to read plans"
    on plans
    as restrictive
    for select
    to authenticated
    using (true);

A function to get the organization's subscription

Let's create an SQL function that returns the user's subscription:

create or replace function get_active_subscription(user_id bigint)
returns table (
  period_starts_at timestamptz,
  period_ends_at timestamptz,
  price_id text,
  "interval" text
) as $$
begin
    return query select subscriptions.period_starts_at, subscriptions.period_ends_at, subscriptions.price_id, subscriptions."interval" from public.subscriptions
    where subscriptions.user_id = user_id and (subscriptions.status = 'active' or subscriptions.status = 'trialing');
end;
$$ language plpgsql;

A function to get the user's task count

Let's create an SQL function that returns the user's task count:

create or replace function get_user_task_count(user_id bigint)
returns int as $$
declare
    task_count int;
begin
    select count(*)
        from tasks
        where tasks.user_id = user_id
        into task_count;
    return task_count;
end;
$$ language plpgsql;

A function to check if the organization can create a task

Let's create an SQL function that returns true if the user can create a task:

create or replace function user_can_create_task(user_id bigint)
returns boolean as $$
declare
    task_count int;
    user_price_id text;
    plan_task_quota int;
begin
    select get_user_task_count(user_id)
        into task_count;
    select price_id
    into user_price_id
    from get_active_subscription(user_id);
    if user_price_id is null then
        raise exception 'User does not have an active subscription';
    end if;
    select task_quota from plans where price_id = user_price_id into plan_task_quota;
    return task_count < plan_task_quota;
end;
$$ language plpgsql;

Here we store the total tasks count in a variable:

select get_user_task_count(user_id) into task_count;

Then we get the user's price ID:

select price_id
    into user_price_id
    from get_active_subscription(user_id);

If the user doesn't have an active subscription - then we throw an error:

if user_price_id is null then
    raise exception 'User does not have an active subscription';
end if;

What if you wanted to provide a default plan for users that don't have an active subscription? You could do something like this for allowing 10 tasks for users that don't have an active subscription:

if user_price_id is null then
  return task_count < 10;
end if;

Then we get the plan's task quota:

select task_quota from plans where price_id = user_price_id into plan_task_quota;

Finally, we check that the count of tasks is less than the plan's task quota:

return task_count < plan_task_quota;

Wrapping it all up in a policy

We can now write a policy that restricts access to the tasks table based on the user's task quota:

create or replace policy "Only allow users to create tasks if they have enough quota"
    on tasks
    as restrictive
    for insert
    to authenticated
    with check (
        user_can_create_task(user_id)
    );

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).

Restricting Functions

Sometimes - you need a security definer functions. This is useful for bypassing RLS policies. But it's risky.

If you want to run a function - making sure only the admin service role ca call it, check the role of the user calling the function:

create or replace function create_task(user_id bigint, name text)
returns tasks as $$
begin
    if current_setting('role') != 'service_role' then
        raise exception 'Only admins can call this function';
    end if;
    insert into tasks (user_id, name) values (user_id, name);
    return (select * from tasks where id = currval('tasks_id_seq'));
end;
$$ language plpgsql security definer search_path = public;

This function will throw an error if it's called from the client. You can then call this function from the server using a service role key and ensure that the user has enough quota to create a task server-side before calling this function.