How to write Row Level Security policies in Supabase?

 

Step 1: Set Up Your Supabase Project

 

Before writing Row Level Security (RLS) policies in Supabase, ensure you have a Supabase project set up. If you don't have one yet, follow these steps:

1. Go to the Supabase website and sign up or log in.
2. Click on "New Project" and provide the necessary details such as project name, password, and region.
3. Once the project is created, you'll have access to the project dashboard.

 

Step 2: Create a New Table

 

To apply RLS policies, you need a table in your database. Let's create an example table:

1. Navigate to the "Database" section in your Supabase dashboard.
2. Click on "Tables" in the sidebar, then "New Table".
3. Name the table orders and add the following columns:
   • id: Integer, Primary Key
   • user_id: UUID (Relationship with the Auth users table)
   • product: Text
   • quantity: Integer
4. Click "Save" to create the table.

 

Step 3: Enable Row Level Security

 

RLS is not enabled by default, so you need to turn it on for your table.

1. In the Supabase dashboard, open the "SQL Editor" section.

2. Execute the following SQL command to enable RLS for the orders table:

   ```sql
   ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
   ```

 

Step 4: Create an RLS Policy

 

With RLS enabled, define policies that specify which rows users can access. Let’s create a policy that allows users to view only their own orders.

1. Go back to the "SQL Editor".

2. Add and run a SQL query to create a policy:

   ```sql
   CREATE POLICY "Users can view their own orders"
   ON orders
   FOR SELECT
   USING (auth.uid() = user_id);
   ```

   This policy ensures that users can only see rows where user_id matches their own auth.uid().

 

Step 5: Test Your Policy

 

To confirm that your RLS policy works as expected, test it using the Supabase dashboard or any client application.

1. Try fetching data from the orders table as a logged-in user.
2. Ensure the results only include the user's own orders.

 

Step 6: Create Additional Policies as Needed

 

You might need more policies based on your application's requirements. For instance, allowing users to update or delete their orders:

1. Create a policy for updating records:

   ```sql
   CREATE POLICY "Users can update their own orders"
   ON orders
   FOR UPDATE
   USING (auth.uid() = user_id);
   ```

2. Create a policy for deleting records:

   ```sql
   CREATE POLICY "Users can delete their own orders"
   ON orders
   FOR DELETE
   USING (auth.uid() = user_id);
   ```

 

Step 7: Review and Maintain Policies

 

Regularly review your RLS policies to ensure they still meet your needs as your application evolves. Adjust or create new policies when necessary. Always test thoroughly after making changes to ensure that your policies function as intended.

 

Congratulations, you've set up Row Level Security policies in Supabase! This setup ensures that users can only see and modify data they are authorized to access, enhancing your application's security.