Fortify Your Database: Supabase's Row Level Security
Tags: supabase, database, security
Published: 2023-09-25 14:13:31

Over the last few weeks, I have been working on stripping away complexity from my apps. In those adventures, I needed a database service that would enable me to handle authentication with them as well as store my app data.

This has to happen with me seeing what exactly is happening in my database. I don't want anything hidden or wrapped in something to form a sort of bridge. That meant no ORM, no third party auth service, etc...

I remembered [Supabase](https://supabase.com) was one that seemed interesting when I heard about it. The pathway was chosen...

One of the first things I had to do was figure out how **Supabase Auth** worked. This was surprisingly easier than I assumed it would be. That assumption - of auth being complicated - came from some issues I encountered with NextAuth when I was using it.

The only thing I would say is simpler is [Clerk](https://clerk.com). But with Clerk, I would end up having to do some `id` gymnastics to get things to sync up - thereby defeating the stripping away of complexity.

---

## Authentication Service

Supabase has a built in **Authentication Service** where users are added when they sign up for your app - this is done automatically when a new user is signed up. This makes it highly efficient to handle the auth logic of your project.

![auth service](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lw66yfgapy2otq2xk7hg.png)

This feature of Supabase simplifies my life when it comes to signing up new users to my app.

The only issue is that these users who have signed up have no clearly defined permissions. If they got access to your database, they could do whatever they want, and we don't want that.

That is where **Row Level Security** comes in...

---

## Row Level Security

On each table in your project, you can enable RLS and define who has access to do what on each table.

By default when you create a table in Supabase, RLS is activated. What it does is make it such that no-one can access the table. However, in most instances you would want specific users to access a table based on specific conditions.

![create new table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m8ob8ukkkxe79y5zw3bm.png)

Enter... **Policies**

### Policies

Policies in Supabase are simply rules as to who can perform which action on a specified table. All policies are; is SQL logic that you attach to a Postgres table. There is no limit as to how many policies you can have on a table.

They - policies - can be accessed from the Authentication section of your project or the dropdown beside your table's name.

> RLS is a Postgres primitive and can provide "[defense in depth](https://en.wikipedia.org/wiki/Defense_in_depth_(computing))" to protect your data from malicious actors even when accessed through 3rd party tooling.
> 
> - [Supabase Docs](https://supabase.com/docs/)   

---

## My Use Case

In one of my apps I have two main tables - a **profiles** table and a **user\_data** one. I need it such that only a user whose `id` matches is able to view, add/modify information related to that `user id`. While I could write the SQL myself, Supabase simplifies this process with their UI.

![create policy](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hsknxb5jfxpqh58kdoci.png)

Both my `profiles` and `user_data` table have 3 policies: select, update and insert

![polices](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8unzdysuhs6eqrm9xcb1.png)

Recall I mentioned that policies are just logic? Well, the logic for my insert would look something like this:

```sql
CREATE POLICY "Users can insert their own profile" ON "public"."profiles"
AS PERMISSIVE FOR INSERT
TO public

WITH CHECK ((auth.uid() = id))
```

I allow insert actions on my profiles table, but only if the authentication id (from the authentication service) matches the id of the user. The connection of the `auth id` and `user id` dives into relations and I am not delving into that right now...

---

## Conclusion

Supabase has been an absolute joy to work with. From creating a project, and making tables, to defining permissions on said tables. I also appreciate the straightforward approach to accessing your data from whatever frontend you decide to use.

Their docs are also decent which helps, and if not, the [discord](https://discord.supabase.com/) is an excellent place to ask for assistance.

Things... just... work...

My next exploration will be with the CLI and linking a local instance of Supabase with a hosted one.

In my goals of reducing complexity, Supabase succeeds in aiding me in accomplishing them.

---

### Thank you for reading, let's connect!

Thank you for visiting this little corner of mine. Let's connect on [Twitter](https://twitter.com/Psypher1), [Discord](https://discord.com/users/Pharaoh#5441/) and [LinkedIn](https://www.linkedin.com/in/jamesmidzi/)