After creating your migration, it's time to add the required code.
In our example, we create the schema for a simple tasks application.
Permissions
Makerkit defines a set of default permissions in an enum named public.app_permissions
.
To add more permissions for your app, please update the enum:
-- insert new permissions
alter type public.app_permissions add value 'tasks.write';
alter type public.app_permissions add value 'tasks.delete';
commit;
In the case above, we added the permissions tasks.write
and tasks.delete
. We can use these in our RLS rules to make sure
the permissions are able to restrict access.
Tasks Table
Let's now add the new tasks
table
-- create tasks table
create table if not exists public.tasks (
id uuid primary key default gen_random_uuid(),
title varchar(500) not null,
description varchar(50000),
done boolean not null default false,
account_id uuid not null references public.accounts(id),
created_at timestamp with time zone not null default now(),
updated_at timestamp with time zone not null default now()
);
grant select, insert, update, delete on table public.tasks to
authenticated, service_role;
Let's explain:
-
uuid
is a primary key generated automatically
-
title
is a text field constrained to 500 chars.not null
makes sure it cannot be null.
-
description
is a text field constrained to 50000
-
done
is a boolean field
-
account_id
is the account that owns the task
We then add the required permissions to the roles authenticated
and service_role
.
Anonymous users have no access to this table.
Accounts
Accounts are the primary entities of our schema. An account can be a user or a team.
We can connect an entity to the account that owns it using a foreign key
account_id uuid not null references public.accounts(id)
Enabling RLS
When you create a new table, always enable RLS.
-- enable row level security
alter table tasks enable row level security;
RLS Policies
RLS Policies are fundamental to protect our tables.
We insert an RLS policy for each action: select
, insert
, update
and delete
.
Selecting Tasks
When writing an RLS policy for selecting data from the tasks
table, we need to make sure the user is the owner of the task or has the required permission.
Generally speaking, entities belong to public.accounts
- and we can use the account_id
to check if the user is the owner.
create policy select_tasks on public.tasks
for select
to authenticated
using (
account_id = auth.uid() or
public.has_role_on_account(account_id)
);
Did you know that an account can be a user or a team? We can use the public.has_role_on_account
function to check if the user has a role on the account.
Therefore, this RLS policy works in both ways:
- if the user is the owner of the task - we check that the
account_id
is equal to theauth.uid()
- if the user has a role on the account - we check that the user has a role on the account
Inserting Tasks
When writing an RLS policy for inserting data into the tasks
table, we need to make sure the user is the owner of the task or has the required permission.
create policy insert_tasks on public.tasks
for insert
with check (
account_id = auth.uid() or
public.has_permission(auth.uid(), account_id, 'tasks.write'::app_permissions)
);
In th above, we check if the user is the owner of the task or has the tasks.write
permission.
- If the
account_id
is equal to theauth.uid()
- the account is personal - so permissions are not required - If the user has the
tasks.write
permission - the user can insert the task
Updating Tasks
When writing an RLS policy for updating data in the tasks
table, we need to make sure the user is the owner of the task or has the required permission.
create policy update_tasks on public.tasks
for update
using (
account_id = auth.uid() or
public.has_permission(auth.uid(), account_id, 'tasks.write'::app_permissions)
)
with check (
account_id = auth.uid() or
public.has_permission(auth.uid(), account_id, 'tasks.write'::app_permissions)
);
Did you know that we need to add the using
and with check
clauses?
using
is used to filter the rows that the user can updatewith check
is used to check if the user can update the row
In the above, we check if the user is the owner of the task or has the tasks.write
permission.
Deleting Tasks
When writing an RLS policy for deleting data from the tasks
table, we need to make sure the user is the owner of the task or has the required permission.
create policy delete_tasks on public.tasks
for delete
using (
account_id = auth.uid() or
public.has_permission(auth.uid(), account_id, 'tasks.delete'::app_permissions)
);
In the above, we check if the user is the owner of the task or has the tasks.delete
permission.
Our schema is now complete! Yay! 🎉
Resetting Migrations
When adding a new schema, we need to reset the migrations.
pnpm run supabase:web:reset
Then, we generate the new types using the following command:
pnpm run supabase:web:typegen
You can now use the new types in your application when using the Supabase client.