Using a Row Level Security Policy to Limit the Number of Rows Inserted Per User

Jon Meyers @jonmeyers_io

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

This article assumes we are using Supabase and have configured auth. This is where our auth.users table 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 select , insert , update or 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 insert policy:

1. Count how many teams this user has already created
2. Check if this exceeds our max — we’ll make this 3
3. Run this policy anytime a new team is created — on insert

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.

1. We are using select 1 which gives us back true if the sub-query returns any rows, or null if it doesn’t
2. teams_created is an alias for the result of the sub-query — kind of like a variable — which makes the where clause less ambiguous for PostgreSQL, but also makes it read a little more nicely for us 👍
3. We are checking that the user has created < 3 teams, 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!