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

To protect your data, you can apply row-level-security policies to your data. This article lists some common policies you can apply to your data

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

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

Truthfully, this is not a very useful policy. It's unlikely that you'll want to restrict access to a table based on the user ID in a multi-tenant application. However, it's a good example if you store per-user data in a table.

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.

Restricting access to a table based on a column and a role

In Makerkit - multi-tenancy is achieved using organizations. As such, you'll want to restrict access to data based on the organization_id column.

Let's create a policy that restricts access to the tasks table based on the organization_id column - taking into consideration the user's role in the organization.

User roles are defined using a hierarchy. The hierarchy is as follows:

Owner (role = 2)
Admin (role = 1)
Member (role = 0)

You can update this as you see fit - but this is the default hierarchy. Remember that the role column is stored in the memberships table and needs to be updates should you wish to change the hierarchy.

Create the function to get the user's role

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;

Create the policies

Now that we have the function to get the user's role, we can create the policies. The policy state that only the owner can insert, update, or delete tasks.

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
    );
create or replace policy "Only the owner can delete tasks"
    on tasks
    as restrictive
    for delete
    to authenticated
    using (
        get_current_user_role(organization_id) >= 1
    );

Let's now allow every organization member to read tasks.

We can use the built-in current_user_is_member_of_organization function to check if the user is a member of the organization.

create or replace policy "Only the owner can delete tasks"
    on tasks
    as restrictive
    for select
    to authenticated
    using (
        current_user_is_member_of_organization(organization_id)
    );

NB: when using a select policy, you don't need to use with check. This is because the using clause is sufficient to restrict access to the data. With that said, remember than failing policies won't throw an error - they'll just return no data.

We can also allow every member to create tasks within their organization using the insert policy:

create or replace policy "Only the owner can insert tasks"
    on tasks
    as restrictive
    for insert
    to authenticated
    with check (
        current_user_is_member_of_organization(organization_id)
    );

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:

assign a plan to a Product ID
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 organization's subscription:

create or replace function get_active_subscription(org_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
    join organizations_subscriptions on subscriptions.id = organizations_subscriptions.subscription_id
    where organizations_subscriptions.organization_id = org_id and (subscriptions.status = 'active' or subscriptions.status = 'trialing');
end;
$$ language plpgsql;

A function to get the organization's task count

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

create or replace function get_organization_task_count(org_id bigint)
returns int as $$
declare
    task_count int;
begin
    select count(*)
        from tasks
        where organization_id = org_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 organization can create a task:

create or replace function organization_can_create_task(org_id bigint)
returns boolean as $$
declare
    task_count int;
    organization_price_id text;
    plan_task_quota int;
begin
    select get_organization_task_count(org_id)
        into task_count;
    select price_id
    into organization_price_id
    from get_active_subscription(org_id);
    if organization_price_id is null then
        raise exception 'Organization does not have an active subscription';
    end if;
    select task_quota from plans where price_id = organization_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_organization_task_count(org_id) into task_count;

Then we get the organization's price ID:

select price_id
    into organization_price_id
    from get_active_subscription(org_id);

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

if organization_price_id is null then
    raise exception 'Organization does not have an active subscription';
end if;

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

if organization_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 = organization_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 organization's task quota:

create or replace policy "Only allow organizations to create tasks if they have enough quota"
    on tasks
    as restrictive
    for insert
    to authenticated
    with check (
        organization_can_create_task(organization_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.