When we created the table, we left the RLS checkbox checked.

What is Row Level Security?

RLS is PostgreSQL’s way of controlling which rows users can access. It’s like the user-specific permissions we implemented with JWT, but enforced at the database level.

Instead of Checking in Our API Code

// Our JWT approach from Lesson 3
if (req.user.id !== userId) {
  return res.status(403).json({ error: "You can only access your own data" });
}

RLS Handles it Automatically in the Database

For example, we can create rules that enforce the following:

  • Users can only see their own posts.
  • Only logged-in users can create posts.
  • Users can only update/delete their own posts.

Important: DO NOT disable RLS. You WILL be hacked.

Creating RLS policies

There is more than one way to create RLS policies:

1. Through Supabase’s Policy Builder UI

  1. Go to Authentication > Policies.
  2. Find the posts table.
  3. Click the Create policy button to create policies.

2. Through Running SQL Scripts

Go to the SQL Editor via the menu, then click New SQL Snippet. This is the method we will use now to create the policies.

Select Policy (Read):

Run this script to allow users to read their own posts:

CREATE POLICY "Users can read own posts" ON posts
  FOR SELECT TO authenticated USING (auth.uid() = user_id);

Insert Policy (Create):

Run this to allow users to create posts:

CREATE POLICY "Users can create own posts" ON posts
  FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);

Update Policy:

Run this to allow users to update their own posts:

CREATE POLICY "Users can update own posts" ON posts
  FOR UPDATE TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

Delete Policy:

Run this to allow users to delete their own posts:

CREATE POLICY "Users can delete own posts" ON posts
  FOR DELETE TO authenticated USING (auth.uid() = user_id);

Key concept: auth.uid() returns the ID of the currently authenticated user, similar to req.user.id in our JWT middleware. Access is only allowed if the logged-in user is equal to the user_id in this table row.

Understanding the RLS Policy Syntax

Policy Structure:

  • CREATE POLICY "policy_name" - Names the policy for identification.
  • ON table_name - Specifies which table the policy applies to.
  • FOR operation - Defines which operation (SELECT, INSERT, UPDATE, DELETE).

Policy Conditions:

  • USING (condition) - Controls which existing rows you can SELECT, UPDATE, or DELETE.
  • WITH CHECK (condition) - Controls what data you can INSERT (or UPDATE to).
  • TO authenticated - the policy is only applied for users who are authenticated. Unauthenticated users will be automatically denied.

In our policies:

  • SELECT uses USING to filter which posts you can see.
  • INSERT uses WITH CHECK to ensure you can only create posts with your own user_id.
  • UPDATE uses USING to filter which posts you can edit (your own posts only).
  • DELETE uses USING to filter which posts you can delete (your own posts only).

The auth.uid() Function:

  • Built-in Supabase function that returns the authenticated user’s ID.
  • Returns null if no user is logged in (which fails all comparisons).
  • Automatically links to the user who made the request.
Tags: