Using a Row Level Security Policy to Limit the Number of Rows Inserted Per User
When designing a database schema, we often want to bake in constraints to stop our users doing silly things! Often, this helps avoid massive AWS bills, but we could also use them to monetise our product via the removal of such constraints at different payment tiers — see every SaaS product in the world!
A common constraint we might want to implement is limiting the number of rows a particular user can insert into a table — a user can only create three teams, for example. This tweet from Ingo prompted me to look into this problem, and the solution turned out to be a good example of policies, sub-queries and aliases, so I wrote it down with the goal of never needing to work this out again 😅
So let’s create the schema:
All code snippets in this article can be run directly on your PostgreSQL database through the SQL Editor in the Supabase dashboard.
create table if not exists teams ( id bigint generated by default as identity primary key, created_at timestamp with time zone default timezone('utc'::text, now()) not null, name text, creator uuid default auth.uid() not null, constraint fk_user foreign key(creator) references auth.users(id) );
This creates a table called
teams, which has a
name and a
This article assumes we are using Supabase and have configured auth. This is where our
auth.userstable comes from.
Initially, I thought this could either be a Row Level Policy or a Check Constraint, but after giving it a little more thought realised a check constraint would not be able to do an additional query to look up how many teams the current user has created, or even know who the current user is.
This is the perfect use case for a Row Level Security (RLS) policy, as this will run immediately before the new team is created, and, when using Supabase, has access to the currently signed in user.
RLS blanket denies all actions for a table — you can no longer
delete. To enable these actions again, you need to write a policy. Check out my video on RLS to learn more.
Let’s enable RLS on our teams table:
alter table teams enable row level security
There are three steps we want to perform in our
- Count how many teams this user has already created
- Check if this exceeds our max — we’ll make this
- Run this policy anytime a new team is created — on
Let’s start by looking up how many teams this user has already created:
select count(*) from teams where creator = auth.uid()
auth.uid()is a PostgreSQL function available in Supabase projects, that returns the currently signed in user — the one attempting to create a new team.
We then wrap this query in another query to check whether the user has exceeded the maximum number of teams they can create:
select 1 from ( select count(*) from teams where creator = auth.uid() ) teams_created where teams_created.count < 3
Okay, there are a couple of tricks going on in this one.
- We are using
select 1which gives us back
trueif the sub-query returns any rows, or
nullif it doesn’t
teams_createdis an alias for the result of the sub-query — kind of like a variable — which makes the
whereclause less ambiguous for PostgreSQL, but also makes it read a little more nicely for us 👍
- We are checking that the user has created
< 3teams, as this policy will run immediately before the new team is created
We can then wrap this in a call to
exists, which will return
true if the query was able to find something, otherwise
false — determining whether the new team is created or not.
exists ( select 1 from ( select count(*) from teams where creator = auth.uid() ) teams_created where teams_created.count < 3 )
And finally, a convenient copy pasta for the entire policy — click it to copy to clipboard ⚡️
create policy "user can create a maximum of three teams" on "public"."teams" as permissive for insert to public with check ( exists ( select 1 from ( select count(*) from teams where creator = auth.uid() ) teams_created where teams_created.count < 3 ) )
And that’s it!
Each user will now be able to create up to three teams. When they attempt to create the fourth team, they will get a RLS policy violation, which should be handled gracefully by your application 🚀
If you enjoy these PostgreSQL and Supabase tips and tricks, check out our weekly Happy Hour stream, where we do some casual Friday afternoon coding!