Row Level Security

Secure your data using Postgres Row Level Security.

When you need granular authorization rules, nothing beats Postgres's Row Level Security (RLS).

Row Level Security in Supabase#

RLS is incredibly powerful and flexible, allowing you to write complex SQL rules that fit your unique business needs. RLS can be combined with Supabase Auth for end-to-end user security from the browser to the database.

RLS is a Postgres primitive and can provide "defense in depth" to protect your data from malicious actors even when accessed through third-party tooling.

Policies#

Policies are Postgres's rule engine. Policies are easy to understand once you get the hang of them. Each policy is attached to a table, and the policy is executed every time a table is accessed.

You can just think of them as adding a WHERE clause to every query. For example a policy like this ...

.. would translate to this whenever a user tries to select from the todos table:

Enabling Row Level Security#

You can enable RLS for any table using the enable row level security clause:

Once you have enabled RLS, no data will be accessible via the API when using the public anon key, until you create policies.

Creating policies#

Policies are simply SQL logic that you attach to a Postgres table. You can attach as many policies as you want to each table.

Supabase provides some helpers that simplify RLS if you're using Supabase Auth. We'll use these helpers to illustrate some basic policies:

SELECT policies#

You can specify select policies with the using clause.

Let's say you have a table called profiles in the public schema and you want enable read access to everyone.

Alternatively, if you only wanted users to be able to see their own profiles:

INSERT policies#

You can specify insert policies with the with check clause. The with check expression ensures that any new row data adheres to the policy constraints.

Let's say you have a table called profiles in the public schema and you only want users to be able to create a profile for themselves. In that case, we want to check their User ID matches the value that they are trying to insert:

UPDATE policies#

You can specify update policies by combining both the using and with check expressions.

The using clause represents the condition that must be true for the update to be allowed, and with check clause ensures that the updates made adhere to the policy constraints.

Let's say you have a table called profiles in the public schema and you only want users to be able to update their own profile.

You can create a policy where the using clause checks if the user owns the profile being updated. And the with check clause ensures that, in the resultant row, users do not change the user_id to a value that is not equal to their User ID, maintaining that the modified profile still meets the ownership condition.

If no with check expression is defined, then the using expression will be used both to determine which rows are visible (normal USING case) and which new rows will be allowed to be added (WITH CHECK case).

DELETE policies#

You can specify delete policies with the using clause.

Let's say you have a table called profiles in the public schema and you only want users to be able to delete their own profile:

Bypassing Row Level Security#

You can create Postgres Roles which can bypass Row Level Security using the "bypass RLS" privilege:

This can be useful for system-level access. You should never share login credentials for any Postgres Role with this privilege.

RLS performance recommendations#

Every authorization system has an impact on performance. While row level security is powerful, the performance impact is important to keep in mind. This is especially true for queries that scan every row in a table - like many select operations, including those using limit, offset, and ordering.

Based on a series of tests, we have a few recommendations for RLS:

Add indexes#

Make sure you've added indexes on any columns used within the Policies which are not already indexed (or primary keys). For a Policy like this:

You can add an index like:

Benchmarks#

Call functions with select#

You can use select statement to improve policies that use functions. For example, instead of this:

You can do:

This method works well for JWT functions like auth.uid() and auth.jwt() as well as security definer Functions. Wrapping the function causes an initPlan to be run by the Postgres optimizer, which allows it to "cache" the results per-statement, rather than calling the function on each row.

Benchmarks#

Add filters to every query#

Policies are "implicit where clauses," so it's common to run select statements without any filters. This is a bad pattern for performance. Instead of doing this (JS client example):

You should always add a filter:

Even though this duplicates the contents of the Policy, Postgres can use the filter to construct a better query plan.

Benchmarks#

Use security definer functions#

A "security definer" function runs using the same role that created the function. This means that if you create a role with a superuser (like postgres), then that function will have bypassrls privileges. For example, if you had a policy like this:

We can instead create a security definer function which can scan roles_table without any RLS penalties:

Minimize joins#

You can often rewrite your Policies to avoid joins between the source and the target table. Instead, try to organize your policy to fetch all the relevant data from the target table into an array or set, then you can use an IN or ANY operation in your filter.

For example, this is an example of a slow policy which joins the source test_table to the target team_user:

We can rewrite this to avoid this join, and instead select the filter criteria into a set:

In this case you can also consider using a security definer function to bypass RLS on the join table:

Benchmarks#

Specify roles in your policies#

Always use the Role of inside your policies, specified by the TO operator. For example, instead of this query:

Use:

This prevents the policy ( (select auth.uid()) = user_id ) from running for any anon users, since the execution stops at the to authenticated step.

Benchmarks#