Implement Authorization Using Row Level Security and Policies
Row level security is a feature of PostgreSQL, that secures our database by automatically denying all read and write requests. We can then create policies to enable particular actions for specific tables. This is similar to declaring an SQL where clause that is automatically appended to every query.
We use the Supabase dashboard to write a simple select policy. This allows anyone (signed in or not) to view rows from the lesson table.
Taking this concept a little further, we create a select policy for the profile table. This will allow any logged in user to select their specific profile.
Row level security is a powerful way to implement authorization, as it is enforced by the database itself, and runs for each row returned by a query. No need to go through an API to determine whether the user should have access or not. This can drastically improve the performance and reliability of our data fetching, as we can remove an unnecessary hop! 🎉
Instructor: [0:00] Currently, our database is entirely open to the public. This means, if someone were to gain access to our anon key, which is currently exposed to our client, they could essentially perform any read or write action across any of our tables in our database.
[0:12] Postgres has a super powerful feature called Row-Level Security, which we can use to lock down our tables. If we go to Authentication and then Policies, we can enable this on a table-by-table basis. Let's enable RLS for our Lesson table.
[0:26] We'll now see that, if we refresh our application, we see a blank page. This is because, by default, Row-Level Security will deny all read and write actions, so Select, Update, Insert, or Delete.
[0:37] If we would like to enable the SELECT action for our Lesson table, we need to create a new policy. We'll create this one from scratch. We'll give it the name "Anyone Can Select Lessons." We want this policy to be for the SELECT action. Here, we just need to specify an expression that, if it evaluates to true, will enable the SELECT action.
[0:56] Because we want anyone to be able to select a lesson, we can just say true. Now we can click Review to see the SQL that's been generated behind the scenes, and save policy to run that SQL. When we go back to our application and refresh, we should see our lessons are back and clicking into these lessons should show us more details.
[1:13] Let's also enable row-level security for our profile table. If we open up our console, clear all these previous console logs, and then refresh our application, you'll see that we have our user object. If we expand this out, we don't actually have any of the fields from our profile table.
[1:29] Let's write a policy to enable that. We'll call this one, "User Can Select Their Profile." Again, we want this to be on the SELECT action. If we were to just specify true again, this would mean that any user could select a profile from any other user, which is not what we want.
[1:46] We only want the user to be able to select their specific profile. For this, we can use a special function that Supabase gives us called auth.uid. This is going to give us back the ID for the currently logged in user.
[1:58] Then we can check whether this value matches the ID column of the profile we're trying to select. If I say review and save policy. When we go back to our application and log out and back in, we have our user object.
[2:13] Only now we have our enriched profile data like isSubscribed and Stripe customer back.