Understanding RLS with Supabase

If you're like me and using Supabase's authentication with Row Level Security (RLS) for the first time, you might be a bit confused and intimidated to learn yet another authorization system. However, RLS is actually quite simple and powerful once you understand the basics.

Restricting access

Let's start with a simple example:

CREATE POLICY "Users can read their posts" ON "public"."post" --- 1
    FOR SELECT --- 2
    USING (auth.uid() = user_id); --- 3, 4

RLS is defined as policies on tables, so this policy is for the post table.
You can optionally specify the operation that this policy applies to. In this case it's SELECT, and there are other operations to choose from like INSERT, UPDATE, and DELETE.
The USING clause is where you define the condition that must be satisfied for the policy to allow the operation. I'm not sure why they didn't name this WHERE but that's essentially what it is.
auth.uid() is a function provided by Supabase that returns the authenticated user's ID.

So bringing it all together, this policy allows users to read posts where the user_id column matches the authenticated user's ID. If the condition is not met, the query will return an empty result set. This is just a simple example, but you can create more complex policies by combining multiple conditions with AND and OR operators. You can also define policies for different operations and roles.

Validating data

The other missing piece is what happens when you try to insert or update data? A user might have access to mutate a row, but what if they try updating their user_id to another user's ID? This is where WITH CHECK comes in:

CREATE POLICY "Users can update their posts" ON "public"."post"
    FOR UPDATE
    USING (auth.uid() = user_id)
    WITH CHECK (auth.uid() = user_id);

This is basically the same as before except with FOR UPDATE and a new WITH CHECK clause. The WITH CHECK is used to validate the data before allowing the operation. In this case, it ensures that the user_id column is not changed to another user's ID. If the condition is not met, the query will return an error.

Applying to roles

The last thing to note is that you can apply policies to different roles. By default, policies apply to all roles, but you can restrict them to specific roles:

CREATE POLICY "Admin users can update their posts" ON "public"."post"
    FOR UPDATE
    TO administrator
    USING (auth.uid() = user_id)
    WITH CHECK (auth.uid() = user_id);

This is similar to our last example, except that the policy only applies to users with the administrator role.

And that's basically it! There are some more advanced usages such as being able to configure TTL policies, but this should cover the majority of your CRUD use-cases.

Don't forget to read the official docs: