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.