One of the biggest mental shifts when building apps on Supabase or when using modern Postgres features is understanding how to secure your database using Postgres’s Row Level Security (RLS) features.
I recently tweeted how this is one of the hardest things to understand about using Supabase specifically because Supabase tightly integrates authentication with RLS, forcing developers to git gud at it as it becomes central to their entire app architecture.
I’m no expert in RLS or Supabase for that matter, but I think I’ve finally wrapped my head around RLS (at least, enough for my purposes), and I’ve come conclusion that it’s actually a simple system and simple is better when using it. Part of wanting to write this post was to help myself better understand it as I have absolutely struggled with it in the past as well.
What is Row Level Security (RLS)?
First, what is RLS? Basically, it’s a way to enforce security at the database level instead of at the app level.
To help illustrate this, imagine we have a data model where
user‘s are members of
team‘s (represented by a
user having a foreign key reference to a
team, allowing users to only be members of one team) and we want to ensure that only a
user that is a member of a
team can access or modify that
In the old way of doing things, an API handler or some other application-level code would explicitly run SQL queries to check if the
user was a member of the
team and only allow the operation if the app decided it was okay. It’s easy to imagine that if we have a bug in this code or if we didn’t have this app level code at all that these security requirements on the data model and their enforcement would cease to exist at all.
With RLS, we can have the database make this check itself as part of the relational data integrity system we’ve defined. It’s a beautiful way to further enhance the fidelity of our data model by making these security requirements core to the model. And by making RLS strict such that no one except the service role user (i.e. an admin) can query or update data on a table by default, we require RLS to be defined for our model.
Understanding your Data Model
Before creating RLS policies, it’s critical to understand what your data model will look like. For the average SaaS or consumer app, there are probably two likely ways your app will model users and their relationship to the data they can access or modify:
Users and no Teams
This is probably typical for most consumer apps that have an individual user with their own individual data. This is pretty straightforward: every table in the app has a reference to the user (or a custom
profile or app-level user table as recommended in Supabase), and so every RLS policy will simply check if the
user foreign key reference is the same as the id on the authenticated user operating on the data. Easy.
Users and Teams
The other common model is where data is owned by a
team and users can access and modify that data based on whether they are a member of the team and possibly some additional constraints (such as whether they are a regular member or an admin-level member).
This is typical of most SaaS products that sell to companies where each company account can invite multiple users, but it makes RLS policies more challenging at first.
Defining RLS Policies in Supabase
While RLS is a core Postgres feature that any user can use, part of the beauty of Supabase is that it makes RLS core to the authentication system and exposes helpers to make it possible to easily define RLS policies based on the authenticated user.
By default RLS is enabled on all new tables, and any query or update on the table by anyone but the service role user will fail. This is critical to remember: if you’ve defined a query and you’re either getting the wrong/no data back or you are getting an error when inserting or updating data, there’s a 99% chance it’s because of a missing or incorrect RLS policy. When in doubt, check your RLS policies as a first measure. And keep in mind joins that operate across multiple tables can involve multiple RLS policies!
To define a new RLS policy, open the Authentication tab in Supabase, find the table you’d like to define the policy on, and click “New Policy”.
This is where things get tricky, because you can either start with a template or start from scratch. But you might not be proficient enough to start from scratch and want to choose a template. However, the default templates Supabase gives you for policies are actually probably not what you want to use, and it can make it seem like you’re doing something wrong when you throw out the default policy and create your own. At any rate, I always start with a template for the operation I need (
DELETE) and then I delete the default fields in the wizard.
Let’s look at three different policies: one for a
SELECT, and one for an
INSERT, and one for an
UPDATE which has a huge gotcha, to see how they differ. We will pretend we have a
posts table that represents something like a “tweet” in our app.
SELECT RLS Policy
To start, chose the “Enable read access to everyone” template which will show you an RLS policy that looks like this:
CREATE POLICY "policy_name"
FOR SELECT USING (
Which then looks like this in the RLS wizard:
Comparing the raw RLS policy with the wizard shows you how the pieces decompose. The
USING in this template is pretty useless to us, because, for this example, we definitely don’t want just any user to be able to read this table! That would mean anyone in the world could access it because of how Supabase makes it possible to run queries on the client.
Instead, set the
Target roles to
authenticated which will require a user to at least be logged in, and then delete the text in
Depending on our data model, there are two ways to structure the
USING query, which must evaluate to a
For the case where we have the first data model (
user‘s own their own data), the check can be as simple as making sure the
auth.uid() (representing the logged in user’s user id) matches the
user_id column that is a foreign key reference to our
profiles custom user table:
For the second case where we have a
user (again, represented through a custom
profile user table) that is a member of a
team, the check is more complicated. We must verify the user’s foreign key reference to a
team matches the
team_id column in the data the
user wishes to access, and we have to return a
BOOLEAN again to satisfy the
This is pretty wordy, and we probably want to make this check reusable. Later on we will show how to define this a helper function to make these checks reusable.
INSERT RLS Policy
Let’s take a look at an
INSERT policy to see how it differs from a
SELECT. The first thing we’ll probably notice is there’s no
USING! What the hell is this
WITH CHECK? Well, it’s actually quite simple. When defining a
SELECT RLS Policy, you are checking against data that already exists in the database. But when you define an
INSERT policy, the data doesn’t exist yet in the database! So a
WITH CHECK checks the new rows instead of existing rows. Here’s the simple case where
user‘s own their own data:
For the second case where
users are members of
teams, our policy is more complicated:
Again, we see that we’re querying our
profiles custom user table to make sure that the
team_id matches for the
id of the authenticated user (represented by Supabase’s helper function
auth.uid()). Notice how we’re duplicating this query again? We will fix that soon.
UPDATE RLS Policy
UPDATE‘s confused me to no end, because they have both a
USING and a
WITH CHECK! But it’s actually quite simple: an
UPDATE modifies existing data and replaces it with new data, so it must check two things: existing rows and the new data (which we can think of as a new row).
So, our query ultimately looks like we just copied in the policy for
INSERT from above:
And for the
DELETE RLS Policy
Finally, we have a policy for
DELETE, which is the same as a
SELECT because we have no new rows to think about, only existing rows:
Defining RLS Helper Functions
For apps with more complicated data models, chances are your RLS policies are too complex to manually define in each policy, and duplicating code is error prone. Instead, what you likely need is a helper function that you can reuse in all your RLS policies. Once you define this helper function, your RLS policies can be extremely simple and you can rest assured knowing your policy is correct.
For example, say you’re building an app where Users (represented by a custom
profiles table), are members of Teams, as described above. In each
WITH CHECK we have to make the same query against
profiles to ensure any existing and/or new rows match the
team_id field on
profiles to ensure the user is in the team. We can easily turn this query into a postgres function and reuse it right in our RLS policies:
CREATE FUNCTION "private"."is_member_of"("_user_id" "uuid", "_team" "uuid") RETURNS boolean
LANGUAGE "sql" SECURITY DEFINER
SELECT EXISTS (
FROM profiles p
WHERE p.team_id = _team
AND p.id = _user_id
And then anywhere we would have used this query in our RLS policies we can just call the function. For example in policies that have a
And in policies that use a
I’ve described one possible way to architect a data model and the corresponding RLS policies for that model. Chances are, if your app differs, it will require a more complex query that you can still nicely define inside of a reusable function for
Please note: I’m new to RLS policies, and one of my biggest questions is about hidden performance implications of complex policies. It’s easy to see how a query can spawn additional queries in a way that is hidden to the developer, so it’s critical to make these RLS policy queries fast and to always be aware they are happening under the hood. This is the part I have the least amount of experience with but good indexing and query planning is likely critical here. There are also probably ways to cut down on queries in your policies by using more of the helpers from supabase (such as the
auth.jwt() and user metadata often used in the official Supabase RLS Policy docs), but I’ve not used those as they didn’t fit my data or mental model. Finally, I also wonder how traditional caching techniques can be applied here to avoid unnecessary queries.
I will say that while I initially hated RLS policies, I’ve grown to absolutely love them. They turn a brittle, application-level problem into an emergent fact about my existing data model. They are beautiful and make me feel a lot more confident about my app’s security and the security of my data.
If you have any comments or notice any errors, please let me know by replying to this tweet.