Implement Gated Content Using Row Level Security with Supabase

Jon Meyers
InstructorJon Meyers

Share this video with your friends

Send Tweet
Published 3 months ago
Updated 2 weeks ago

We're using Row Level Security (RLS) to enforce authorization rules for each row in the database. This applies to the entire row, not specific columns. Therefore, if we want to create some premium content that is only available to our subscribed users, we need to create another table. Again, we can reference the lesson table's ID, using a foreign key relationship.

We will create a premium_content table that will store the video_url column for each lesson. We will fetch this data on the lesson details page, and write an RLS policy that will only enable read access for subscribed users.

This will ensure that our logged-in user has an active subscription, in order to view the video for our lesson. If the user is not signed in or has not paid for a subscription, they will only see the content from the lesson table.

Lastly, we want to display our video (hosted on YouTube) in an inline video player. For this, we can use the react-player package, and set some simple styling defaults that look great!

Instructor: [0:00] Let's create some special premium content for our subscribed users. This will be on the Lesson Details page and we'll show an inline video for the lesson if the user has an active subscription. Let's start by creating a new table for our premium content.

[0:14] We're going to enable row-level security. Our Premium Content table will be an extension of our Lesson table. We can set up a foreign key relationship so that our premium content ID references our lessons ID. Let's click Save to set up that foreign key relationship.

[0:30] Let's add a column for our special premium content. In this case, this is going to be our video URL which is of type text. Keep in mind this could be any kind of premium content that you only want to be available to your subscribed users. Let's click Save to create this table.

[0:45] Let's add a video URL for each one of our lessons. We have ID one and ID two. When we enter in our ID, we can click View Data to see the record that this is associated with. We want to enter this video URL for ID one, and this for ID two.

[1:06] Let's head back over to our app. On the Lesson Details page, we want to get that premium content. In this function, we want to fetch that data from Supabase and that was from the Premium Content table.

[1:21] We want to select the video URL column where the ID is equal to our lesson ID. We just want a single row. Now we need somewhere to store this video URL. Let's import useState from React and create a new variable for our video URL.

[1:39] Once we get that data back from Supabase, we can call set video URL with our data.video URL. If our user is not subscribed, then data will be undefined. To stop ourselves from getting an error, we can use optional chaining here.

[1:55] It will only try to access the video URL if we actually have data. We want to call our getPremiumContent function when our component first mounts. Let's bring in useEffect and call it on mount.

[2:09] Here we want to call our getPremiumContent function and now we want to display our video URL on the page. We're just going to use a paragraph for now, but when we refresh our page, you'll see it's still not displaying our video URL.

[2:22] This is because we enabled row-level security on our Premium Content table, but have not yet written a policy to enable selecting. Let's do that now. Again, we're going to create from scratch.

[2:33] This will be called Subscribed Users Can Select Premium Content, and it's going to be for the select action. For this policy, we want to select the user's profile and check whether they're subscribed. We can do that by saying SELECT * FROM Profile, where auth.uid. Remember that's that function that gives us the currently logged in user. We want to check whether that is equal to the profile ID and make sure the profile.isSubscribed column is set to true.

[3:01] Since we don't actually care about the columns that come back from this query, just whether it can find a record or not, we can replace our * with the number 1, which will return true if it can find this record. We can then wrap this in exists.

[3:15] Now this expression will evaluate to true, if this user is subscribed, and false, if they're not. Let's click Review and save policy. If we go back to our application and refresh, we're still not seeing our video URL, but this is because our policy is actually working.

[3:33] We need our user to be subscribed. If we have a look at our currently logged in user, isSubscribed is set to false. Let's subscribe our user. When we refresh, we'll see our video URL. Let's put this in a slightly nicer video component.

[3:47] We're going to use React player, which we can install from npm. We can then start our development server again and import our video component from React player. We can now replace our paragraph with our video component, with its URL prop set to our video URL.

[4:06] We can constrain its width to 100 percent. We only want to show this video component if we have a video URL. Now when we save, you'll see that our video component is displaying for each one of our lessons. This is because our user is logged in and subscribed.

[4:22] If we were to unsubscribe our user, we can no longer see that premium content.