Implement Authorization using Row Level Security with Supabase

[Music] hey john from superbase here and in this video we're going to learn all about row level policies now this is a concept that does trip up a lot of people especially if they're new to sql or even just writing authorization rules at this layer but i promise it's not as scary as it seems so let's learn how so most people have this mental model of this kind of three-piece architecture where you have a client an api and a database and the client is only able to directly communicate with the api and the api is the only thing that's allowed to talk directly to the database so if the client wants records that are in the database they have to go to the api and they have to say this is who i am and then the api decides do i trust you or don't i trust you and if they do they're the only ones that have a special key to the database so they go up to the database get those records um and then send them back to the client now in the row level security architecture the client can talk directly to the database and so there's no actual need to have that api in the middle the client just sends across their authorization token or says who they are to the database and then the database decides based on those row-level policies should the user have access to this data or should they not if they should then it can send those results directly to the client so not only can this make your queries a little bit more efficient because you don't need to go that extra hop through the api but you also protect yourself from writing some bad logic in the api that potentially exposes some data from your database by writing the access rules for the data in the database itself it makes it very hard to accidentally leak out that data because only the people who should have access to it can have access so let's see how we might implement row level policies in our own application here i have a blog project that i'm building on superbase it has two tables so a post and a comment each post has an id a title the content of the post user id and so this is the user that authored the blog we then have a created at timestamp and whether or not the blog is published currently we then have a comment table and again this has an id and content it has the user that authored the comment a timestamp and then the post that it's associated with so this just models a fairly common one-to-many relationship where each post has many comments and every comment belongs to a post so let's have a look at the application that's using it so here i have a superbase application i'm going to sign up for a new account as ash pokemon.com and he can have the super secure password password on the right here we're going to sign up a second user and so obviously we're going to sign up ash's rival gary at pokemon.com and he's going to have the same password because i don't think they're very security conscious over in violet town now ash is going to create a new post so he can give this post a title and then write about the adventure of his first pokemon and now he's not quite ready to publish this he thinks that maybe he can add a little bit more to this story so he's just going to click save and come back to it later and so ash can now see this post he could continue editing it if he wanted he could delete it if he decides this story isn't worth telling the world and if he goes back to all posts he can see his post listed there which is what we'd expect because even though it hasn't been published ash should still be able to see it and be able to continue working on it the problem is if we come over here and refresh on gary's side gary can also see the blog even though it hasn't been published yet and so let's fix this up using a row level policy so ash should be the only one that's able to see his unpublished blog and definitely the only one that's able to edit or delete it so let's head back over to our superbase dashboard and go to authentication and then policies and you'll see that rls or row level security is disabled on tables by default and so let's enable that for our post table and the first thing we'll notice is if we go back to our application and try and view all posts now you'll see that even ash is not able to see his post and if we refresh on gary's side he obviously can't see it and that's because by default road level security is going to entirely lock down that table so it's going to automatically deny any request to select insert update or delete any row in that table and the only way to unlock those actions again is to create a new policy so here we can create a policy from a template or from scratch i'm going to show what it looks like if we do it from a template if we go down to this bottom option so enable delete access for users based on their user id and so this sounds similar to what we want we want to enable all actions instead of just delete but we definitely want it to be based on the currently logged in user and so if we say we want to use this template we can then change whether we want it to be for select insert update delete or all and so all is going to contain all four of these actions we can then update the name to just say enable all actions for users based on user id and now if we have a look at the policy that we're actually creating here this is just a conditional so a good way to think about this is like a where clause in regular sql so if we wanted to select all the columns from post where the user id column is set to the currently logged in user what we're specifying in the row level policy is just this where clause so this where clause is going to automatically be attached to any insert select update or delete statement that we're trying to do and so we don't need to remember to do that ourselves at the api level if you're not super familiar with sql syntax then this is similar to a filter statement in javascript so if we had a list of all of our posts and we wanted to filter any posts where the post.userid is equal to the currently logged in user's id then we would get back an array from this so this would be like filtered posts and this would only contain posts where this condition was true and so that's what we're doing in our policy here with this condition we're saying we want to basically filter out any of the results where auth.userid which is a special function that gives us back the currently logged in user so this is provided to us by superbase and we're checking whether that user id is equal to the column in this table for user id and so remember this is the person who authored this post so we're saying if the currently logged in user is equal to the post dot user id column then this is all good this returns true and it is all good to select it insert it update it or delete it now because we have included update here we also need to add a width check and so we can just copy and paste the same condition here so we just want to check whether the currently logged in user is equal to the user id column and then we can click review and if we have a look at this there's nothing magical going on this is just creating the sql behind the scenes for us so we could actually just copy this entire sql statement and come over to our sql editor and run this manually and it would do exactly the same thing but all we're doing is creating a policy with the name enable all actions for users based on their user id we're setting this on the public.post table and we want to run this condition so auth.uid is equal to user id anytime we try to perform one of those actions so now we can click save policy and so now if we go back to our application and refresh on ash's side he will now be able to see that post and he can click into it to get some more information about it he could edit the post and say i can't wait to tell gary and then he could even say okay i'm ready to publish this post and click save but now even though that post is now published if we come over to gary's side and refresh you'll see we still can't see that post and that's because we created a policy so that the person who authored the blog could see it but we still haven't enabled access for anyone else so let's create a policy for anyone to be able to see a published post so let's come back over to superbase and create a new policy and this time we're going to create it from scratch so we can see how easy it is for this policy we want to enable user to select a published post and we want to set this on select and then all we want to do here is say if is published is equal to true then we want to display this post so let's review and save that policy and now when we come back to gary's side and refresh he can now see that post which is awesome uh but now if ash decided that he didn't want that post published anymore he could come over here and untick is published and then if gary refreshes he can no longer see that post awesome so our policy is working the way we expect it if i come back here and edit and republish this post a cool thing to note is that if i copy this url to edit uh this blog post and paste it over here for gary he's able to see this page because we haven't implemented any logic in our application to stop that and if he decided he wanted to be extra malicious and come down and delete his rival's post it looks like everything went well we didn't get an error message but now we've been redirected to the landing page and the post is still there so it hasn't been deleted and that's the real power of row level policies we completely forgot about this edge case in our application but because we wrote that policy at the database layer it still wouldn't let us perform that action so no matter how bad the code is we write at the api layer and how much power we think we've given people to do malicious things our data is still safe and that's what's most important so this is looking good let's have a look at how we might enable comments so if we click into one of our posts and if we come to the same post on ash's side we see we've got this comment section and now gary might add a comment saying nice work ash so proud of you and now he adds that comment and we can see it there if we refresh on ash's side we can see that comment and we can say wow thanks gary great guy and again if we come over to gary's side and refresh we can see that very kind message from ash there so let's enable row level security for the comments table and again we can check that if we come back here and refresh we can't see anything for either user so let's write a policy to be able to see these comments again now if we have a think about what kinds of comments we should be able to see it's kind of directly linked to the posts that we can see if we can see a post it makes sense that we can see all of its comments and it doesn't really make sense for us to see a comment for a post that we're not allowed to see so let's have a look at selecting data from another table within our policy to determine whether we should see that comment or not let's start off by creating a new policy on the comment table and again we're going to create this one from scratch we're going to be looking at select and this policy is enabling a user to view comments for posts they can view so rather than just re-implementing the same policies that we have on the post table we can use a little trick here to say well for this comment that i'm currently trying to select i know which post it belongs to and so if i go and write a select statement for that post if i'm able to see it then it means that i should be able to see this comment as well so we can do that by saying select star from post where post dot id is equal to the current comments dot post id so this is going to take the comment we're currently trying to select it's going to look at which post it belongs to and then it's going to go and look up a post with that post id from the post table and then if it's able to select that post it's going to return us all of the columns and so we don't actually care about the columns we don't actually care about the data that's in there we just care whether we can select it or not so we can replace this with a 1 and then that's just going to return us true rather than returning us the values of the columns so if we can select this post we're just going to get back the value true and that means we can wrap this select statement in another statement to see whether it exists so now if this select statement returns true then this policy will return true and we will be able to select that comment and so now if we go back to our application and refresh on ash's side we should see those two comments and if we refresh on gary's side we should see those two comments and so now as gary if i try to say thanks ash and add a new comment we're going to get an error that this violates a row level security policy and so that's because we haven't added a rule for insert yet so let's go and do that and so let's create a new policy and again we can create it from scratch this is going to be insert so allow users to insert comments on posts they can see and this policy is going to be very similar to our select policy so we only want to be able to insert comments for posts that we're allowed to see but if we have a think about this this is actually going to allow us to insert a comment with someone else's user id attached to it and so we probably want to protect that so only a user can only insert a comment for themselves so let's chain on an and here so we can specify a second clause and let's say we want the auth dot user id to equal the comment dot user id and so let's review and save that policy and now gary should be able to leave that very important comment thanking ash and if we refresh on ash's side we can now see that comment so let's add policies for our remaining two actions so we want to be able to update and delete and these policies are going to be exactly the same as our insert so for update we want to be able to update comment that we can view post for and that we own and then we're going to paste that exact same condition both in using and with check and we're going to click review and save policy and the same for delete awesome so if we go back to our application we can see that gary can delete uh comments that he made on ash's post but he can't delete ash's comment so if we try deleting this thanks ash comment and then we go and refresh on ash's side we'll see that that's gone so that's good but one thing that we would want to be able to do is the person who owns the post should probably be able to moderate the comments that are on it and so ash should be able to delete gary's comments but gary should not be able to delete ash's comments so if we have a look here our application is assuming that so if i come down and try to delete gary's comment you'll see that it looks like it's gone but again if i refresh it's back again and that's because our row level security is not letting us perform that action that we haven't written a policy for and so let's write that now new policy create from scratch and this one is for delete and we can say user can moderate comments on their posts and so for this one we're also going to want to look up the associated post so we can say select star from post where post dot id is equal to comment dot post id and so similarly to the previous example we want to look up the post id for that comment in the post table but this time we do actually care about the data that comes back because we want to only allow this action for people who own the post so in this case we could actually select the user id column and then we could wrap this in a statement that checks whether auth.uid is in this subquery so if the user who's currently logged in is the user who created the post for the comment that we're currently trying to delete then we should be able to delete it and if we go back to our application and again try and delete that comment from gary and refresh it's still deleted and if we go and refresh on gary's side he may be a little bit annoyed but that comment is also gone from there so we now have a blog that works the way that we would expect we can create a new post and if it's not published no one else will be able to see it and when we publish a post our other users will be able to read it and we can add comments to that post we can view comments that other people have made on the post that we can see we can moderate those comments and even delete the post that we own and then our other users will no longer be able to see it awesome and that's how easy it is to implement row level policies in postgres using superbase we'll have a link to the auth documentation in the description below in case you want to dig a little bit deeper make sure you check out our youtube channel for more awesome videos just like this one give us a follow on twitter and also check out our new discord there are some very cool folks over there and come tell us what you're building with superbass we would absolutely love to hear about it thanks for watching [Music]

Channel: Supabase

Views: 3,256

Keywords: authorization, row level security, policy, policies, next.js, auth, authentication, backend, api, react, javascript, postgres, postgresql, database, secure, protect, rls

Id: Ow_Uzedfohk

Length: 17min 39sec (1059 seconds)

Published: Fri Sep 03 2021