Fortify Your Database: Supabase's Row Level Security

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 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. 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

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

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” to protect your data from malicious actors even when accessed through 3rd party tooling.

Supabase 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

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

polices

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

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 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, Discord and LinkedIn