Row Level Security

Using Row Level Security with Supabase Auth.

Postgres Row Level Security (RLS) is a feature of Postgres that allows you to control which users are permitted to perform SELECT/INSERT/UPDATE/DELETE statements on specific rows within tables and views. For example, you could restrict a blog_post table such that the current user is only allowed to UPDATE rows where their user id is set in the table's author_id column.

Supabase Auth is designed to work perfectly with RLS.

You can use RLS to create Policies that are incredibly powerful and flexible, allowing you to write complex SQL rules which fit your unique business needs.

Policies#

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:

Authenticated and anonymous roles#

Supabase Auth maps every request to one of the roles:

• anon: an anonymous request (the user is not logged in)
• authenticated: an authenticated request (the user is logged in)

These are actually Postgres Roles, and so they have significant value for the performance of your RLS Policies. You can use these roles within your Policies using the TO clause:

Helper functions#

Supabase provides some helper functions that make it easier to write Policies.

auth.uid()#

Returns the ID of the user making the request.

auth.jwt()#

Returns the JWT of the user making the request. Anything that you store in the user's app_metadata column or the user_metadata column will be accessible using this function. It's important to know the distinction between these two:

• user_metadata - can be updated by the authenticated user using the supabase.auth.update() function. It is not a good place to store authorization data.
• app_metadata - cannot be updated by the user, so it's a good place to store authorization data.

The auth.jwt() function is extremely versatile. For example, if you store some team data inside app_metadata, you can use it to determine whether a particular user belongs to a team. For example, if this was an array of IDs:

Important considerations#

We recommend reading the Row Level Security guide in the database section to learn more about Postgres RLS. When using Postgres on Supabase there are some important things to keep in mind to maintain data security.

Never use a service key on the client#

Supabase provides special "Service" keys, which can be used to bypass RLS. These should never be used in the browser or exposed to customers, but they are useful for administrative tasks.

Always enable RLS on public tables#

You should always enable RLS on tables created in a public schema. This is considered "default safe". Unfortunately this is not enabled by default on Postgres, so you will need to keep this in mind - especially if you are using the SQL Editor or database migrations. RLS is already enabled by default if you create a table using the Table Editor. If you want to allow public access to a table, just add a Policy with true:

Using external authorization systems#

If you want to use another authorization method for your applications that's also fine. Supabase is "just Postgres", so if your application works with Postgres, then it also works with Supabase. If you take this path, don't put your tables in the public schema - instead create a new schema for your tables and functions:

If you do put anything in the public schema, make sure to enable RLS (you don't need to add any policies):

This makes the tables inaccessible via the APIs.

Usage#

Row Level Security is extremely versatile, since it simply uses SQL to express access rules for your data.

Using functions#

You can use any Postgres function inside a Policy. The Helper Functions above are simply Postgres functions we've made available in the auth schema. This is an example which:

1. Creates a table called profiles in the public schema (default schema).
2. Enables RLS.
3. Creates a policy which allows logged in users to update their own data, using the auth.uid() function.

Note: If you want to use upsert operations, the user needs to have INSERT, UPDATE, and SELECT permissions.

Using joins#

Policies can include table joins. This example shows how you can query "external" tables to build more advanced rules. RLS policies are executed on every access of the table, so be careful to make sure that policies are efficient.

An important note here: if RLS is also enabled for members, the user must also have read (select) access to members. Otherwise the joined query will not yield any results. Another alternative is to use a "security definer" function which is created by a user with bypassrls privileges.

Using security definer functions#

You can use security definer functions inside Policies. This is useful in a many-to-many relationships, and important for performance. Following the teams and members example from above, this example shows how you can use the security definer function in combination with a policy to control access to the members table.

Using built-in functions#

Postgres has a number of built-in functions. Most commonly you'll use in() and any() which will match a column's value to a list of values.

You can use any Postgres functions inside Policies. For example, we can use the right(string, n) function to match email domains:

Using Multi-factor Authentication#

RLS can be combined with Multi-Factor Authentication in Supabase Auth. For example, you could restrict a user from updating their profile unless they have at least 2 levels of authentication (Assurance Level 2):

More resources#

• Testing your database
• Community repo on testing RLS using pgTAP and dbdev