Supabase Auth Deep Dive Part 3: User Based Access Policies

in this video i'm going to show you how to restrict access to certain rows in your database based on access tokens that are granted to users that have signed up for your service so the first thing we're going to do is sign up a couple of test users now let's go over to here to the authentication tab and i'll just invite myself as a new user and i'll also invite a second user superbase.io and then what i have to do is i have to go to my email and accept the invitation now instead of just clicking the link in the invitation email i'm going to copy it and paste it in here just to show you what it consists of and how it works so you'll see that it's a link to my superbase api the auth api here and on the verify endpoint and it comes with a token this is an invite token and you'll see at the end type invite so when a user clicks on this link you'll see they get forwarded to localhost 3000 which is taken from here in all settings if i change this to some other application then it will redirect to my application and then you'll see that it comes with an access token which i'm going to copy this big long string and an expiry which should be 60 minutes from when the user accepted a refresh token and the type of link that led us here which was the invite link so if i go now to jwt.io with this access token and paste it in here you'll see that it is indeed my email address in there and with an authenticated role now an authenticated role in superbase just means that this is one of your regular users that have signed up and they're authenticated you'll also see this sub the subject is the uid of this particular user so if i go into my sql editor here and select all from auth.users you can see the two users here and you'll see that the uid matches the one in the token so that's great now just like the last video we're going to create a table called leaderboard and just like the last video we're going to have two fields we're going to have user id which is a uid references auth dot users not null and we're going to have a score which will be an int and it will also not be null so let's submit this great now what we're gonna say is we only want users to be able to see their own scores on the leaderboard now you might think that defeats the purpose of a leaderboard uh but this is an example and a deep dive so i don't care so let's again head over to the api docs on the side here head over to leaderboard and we want the go on the bash documentation here with our non-key and we want to see how we write to this table and just like in the last video we want to take this url and we want to post some data to it so let's head here switch this to post to the leaderboard then we want to add two headers an api key and an authorization bearer now for the api key header we need to add our and on key which is the same as in the last video this gets us past the api gateway but now for the bearer token we want to use the access token of the user so let's go back and grab this access token that we used earlier and we'll send user id and a score of 200 and i'm just going to grab the user id for my user and a two base that i o put that in here the one last thing we need to add is the content type header and that has to be application json now we haven't added any restrictions yet any row level security policies so anybody should be able to write to this table so let's test that out there we go 201 which means that the data should have been written let's select all from leaderboard there we go 200 now let's lock the table for everybody i'll click the padlock and we should find now that if this user tries to add a new record it's forbidden what we want this user to be able to add records where the user id is equal to the user id in the token that they're using to access the api and this is where we can write our first user-based row level policy if we head over to authentication and on the leaderboard table we add policy and then we actually want our user to be able to do all of these things select their rows insert new rows for themselves update their existing rows and delete their their entries so you'll notice if you click this advanced button in the corner you get a new option all so we can say allows users to operate on their own records and you'll see two options here definition and with check now the definition will run before the row is inserted um and a with check will run after the event so if you want to make sure that some trigger is fired and some other data is changed in the database then you can add a width check but the simple solution here is to just write it in the definition so our definition is if the auth dot uid now this is the uid that comes through with the token that's this sub uid here is equal to the user id that we're trying to insert into the table then we'll allow it now if we try and insert some new data let's say a score of 600 with our user it should succeed but if we replace the bearer token with the anon token for example then it should fail now we see new row violates row level security policy so we know that the and on key can't insert new rows but let's try another authenticated user so for this we want to generate a new access token for our second user beta at superbase.io because i didn't store the the last one that we received on the invite and for this we can send a magic link so if i head over to my email now i'll grab the new link that was sent to me paste it in a new tab here you can see that this magic link is very similar to the invite link that we received the main difference being that this is a magic link token not an invite token and the type here is magic link so when a user click clicks this it will open in the browser it should forward to a similar thing your app address with an access token which will copy and you'll see at the very end it'll say type magic link so this type at the end is to let your web app know where this user came from so that you can for example if this is an was an invite link or a email confirmation then you can show a welcome screen so we'll take the access token we'll go here just to show that it has the correct information based at a tube base with a different uid we'll paste it into our client as the bearer token and we'll try and insert a row for the old user and it should fail now you can get real creative with these role of policies as i showed before you can have them specific for reads for inserts for updates delete or you can have a catch-all if you want to use it to be able to do all of these things and also you don't need to use this ui interface to write your policies you can also just write them in pure sql and just to show you what that would look like we can go back to the welcome page look at one of these quick start schemas for example the slack clone and if we scroll all the way to here we can see these policies in sql format so you would say create policy give it a description on a particular table for selecting using and in this case we want users of a slack clone app to be able to read each other's messages so anyone who is logged in anyone who is authenticated and has this authenticated role can read the messages in in the slack channels and likewise the insert below is similar to our demo app here where only a particular user with a uid can write his or hero messages and you'll notice that there is no delete policy in this example the user won't be able to delete his or her own messages now if you look just above this as well this statement here alter table public.messages enabled row level security this is the equivalent of clicking the padlock on the table so when i toggle this padlock it actually enables and disables the row level security on a particular table now you might wonder where these functions are coming from now these are functions that we inject in your superbase auth schema for you when we provision the database and i'll show you what they look like in case you want to add more and extend the functionality so the three that we add are these three you'll see auth.uid auth.role and auth.email and all it does is when a request comes in with the jwt attached it looks at a specific claim so in this case the sub is this value here these are all claims down the left the role we have here role authenticated and the latest one we added was email now the cool thing about this email one is if you want to for example build some internal application that only people with an at super base the io email address are able to access then we can write that as a policy so let's say that we only want people with an attitude based io email address to be able to use and interact with this leaderboard table we can delete this old policy and add a new policy allow access for at superbase.io and for the definition i'm going to get a little bit fancy and use this built-in postgres function right and i'll tell you about this in a second let's add in this and super bass or rather at super bass dot io so what right does is it basically looks at the email address and it takes the right most 12 characters which in this case should be the at superbase.io you'll obviously have to adjust this if you want it for a different domain and the cool thing about these policies is you can stack them up as well so let's say i want to allow access for people from two different domains and let's say i want anyone with a gmail address to be able to to read or write for my service we can go [Music] let's go all again allow access for gmail users and in the definition we'll say write auth.email 10 characters i think in at gmail one two three four five six seven eight nine ten and save that now either of these domains can access the table and of course let us know your feedback in this little widget in the corner here and so if you have any thoughts or feelings areas we can improve things you like about the site just pop it in here and we'll try and respond to every message that goes into this feedback widget thanks a lot

Channel: Supabase

Views: 20,240

Keywords: superbase, auth, row level security, postgres, postgresql

Id: 0LvCOlELs5U

Length: 16min 10sec (970 seconds)

Published: Thu Jan 28 2021