Shield Databases With Supabase RLS

Unlock the power of robust and fine-grained access control in your database with Supabase Row-Level Security (RLS) policies. Let us check how Supabase's dynamic RLS policies, provide a strong layer of security and privacy by restricting data access down to individual rows. Supabase provides few policy templates by default, apart from which we can even create our own policy from scratch. This blog is a continuation of https://blogs.mulecraft.in/authenticated-supabase-storage-with-policies/ blog. Kindly refer it for initial setup.

Database: Table Creation

In your Supabase project, create a new table in your Table editor. For the upcoming example, I have created a table mulecraft_blogs with the following columns.

– id –> auto-generated

– blog_title –> text

– author –> text

– user_id –> uuid

– is_published –> boolean

– created_at –> auto-generated

Let us insert few rows in the table editor for mulecraft_blogs table.

Insert few rows relevant to the column datatype for better understanding of policies which will be implemented further in this blog. For columns, id and created_at, values will be inserted by default. For user_id column, enter Supabase authenticated user_id, which we will further use in policy creation.

As shown in the above image, I have inserted two rows with different user_id and other relevant values.

As we have enabled Row Level Security(RLS) for this table. So, we have to implement policies to query data from the table.

To implement policies navigate to Authentication in the Supabase dashboard. In the Authentication page, select Policies under the Configuration.

Click on New Policy for the mulecraft_blogs table. Choose Create from template, to select a pre-defined policy template.

Choose Enable delete access policy template, which we will customise as per our necessities.

Choose ALL to allow access for all operations. Here we are using user_id for the conditional expression of our database query. It is because, we have user_id column in our table and the values of user_id column are unique.

Click on Review and confirm your customized policy to perform all operations on the database records. By implementing this policy, when a user logs in to our application, they can have access to all operations for all the database rows, for the records whose user_id matches to the logged in user's user_id.

In short, author Shanmathy will have Select, Update, Delete and Insert access to the table records, whose user_id column has the user_id values of Shanmathy. She cannot access records under Yogesh's user_id. For Yogesh, the situation is vice versa. Let us access it from our React app and clearly understand the difference.

In the above images, I have fetched the database records and stored them in records variable. I have simple displayed the values.

Based on our policy, only Shanmathy's records are visible for her, if she is logged in.

Let us add another policy based on is_published column in our table. If the value of is_published column is TRUE, then all users can read that data, else they cannot.

Navigate to the policies in the Authentication page. Click on New policy and choose Create a policy from scratch.

Here, we are checking the value of is_published column.

As, record 1 has is_published=true and record 2 has is_published=false. The second user can view both the records. Shanmathy's records is is_published=true, so it is visible and the second record is inserted with the current user's user_id. For Shanmathy, still only record one is visible.

Let us change the is_published value for record 2 as TRUE and re-check.

Thus, RLS policies give us numerous possibilities to protect our database records with respect to our application's needs.

Effective Shielding

In conclusion, Supabase RLS policies offer an indispensable layer of protection for your databases, elevating security and privacy to new heights. By implementing fine-grained access control down to individual rows, you can ensure that only authorized users can access sensitive data, effectively shielding your database from unauthorized access. The flexibility and dynamic nature of RLS policies make them a valuable tool in safeguarding your data.