Restrict Access to User Data

So, now that we have our basic authentication set up 0:00 so that users can create accounts, 0:04 we need to implement some kind of restrictions in our database 0:06 to ensure users can only access records that belong to them. 0:09 Well, that's where row-level security comes in. 0:14 Now, the key way to restrict what data users can access 0:17 inside of your application is by using something called row-level security. 0:20 And so in Supabase, I have my marketing tasks table. 0:24 And when I first created this, I disabled all RLS, a.k.a. 0:28 row-level security. 0:32 And so what this means 0:34 is that basically anyone could do whatever they want with my table. 0:34 So a non-authenticated user could edit the data, could read 0:38 the data, could delete the data. 0:41 They could do whatever they like which obviously isn't secure. 0:43 And so to secure this table, I'm going to want to use row-level security. 0:46 And so I'll press enable RLS for this table. 0:51 I will enable RLS and so from here I could press add RLS policy. 0:54 Then create policy 1:00 and from here I could manually put in the sequel to create the policy 1:01 or I could select one of the pre-made ones on the right hand side. 1:04 However the simplest way to create RLS policies is by actually just using WeWeb 1:07 AI Now before I do that one thing to note is that when you dealing with tables 1:12 where the records in the table need to be linked to a certain user 1:16 so that specific user can only read and edit the data, 1:20 you're likely going to need some kind of user ID column in the table 1:23 that links to the user that the record belongs to. 1:27 And so in my marketing tasks table, I'm going to need to add a user ID column 1:30 that links to the user 1:34 that the record belongs to so only they can edit and access the data. 1:36 And so now inside of WeWeb, I'm going to prompt the AI to first add 1:40 that user ID column and then add all of the row level security that I need. 1:44 So we've now been given the query that we can run. 2:00 And so I'll deploy this, confirm it. 2:03 and so if I now check super base 2:08 I should see a new user ID column added to the marketing tasks table which I do 2:10 and when I go into the table and check the RLS policies 2:15 I can see we now have all of these appropriate policies 2:19 and so now in the marketing tasks table I can see in this user ID column 2:22 all of the records currently 2:26 have a user ID of null meaning they not linked to anyone 2:28 and so if I switch back to WeWeb I'm currently logged in as my own user 2:31 And so since none of those records are linked to me 2:36 when I refetch the data, I shouldn't actually receive any records back 2:39 because I'm only able to access records that belong to my user account. 2:44 And so when I refresh the page to get the latest data, 2:47 I can see it's an empty list. 2:50 And if I check the base collection itself, 2:52 I can see we're not getting any data back. 2:54 and that's because I don't have any records linked to my user account. 2:56 And so what I can do is in Supabase for this example, 3:00 I'm going to click into this record and I'm going to assign it to my user account. 3:03 And so since this record is now linked to me, 3:08 when I refetch the data in WeWeb, I should receive this record back. 3:11 So when I press Fetch Supabase to get the latest data, 3:15 I can see I've got that single record back and I can see it in my interface. 3:17 So we can see we now have the restriction in place 3:22 so that users can only read data that belongs to them. 3:24 And the next step in the context of my app is that when a user adds a task, 3:27 I need to ensure that the task created belongs to their user account. 3:31 And so what I need to do 3:35 is that inside of the workflow that sends this task to Supabase, 3:36 and inside of the insert action where I assign all of the fields 3:42 that I want to send to Supabase in the field selector I going to want to enable 3:45 the user ID field because I want to define which user this new record 3:50 belongs to And to do so, I'm going to want to bind the user ID, 3:54 open the authentication tab of the binding window, 3:58 expand this user object, and here I'm going to want to pass the ID of the user. 4:01 And so now, when I put this into preview mode and fill out the form, 4:07 when I press save this record will be added to super base 4:13 and when we check super base we should see that the user id of this 4:16 new record belongs to my user account so when I press save 4:20 we can see we have that new record inside of our interface 4:24 because it belongs to me as a user and when I check super base 4:27 I can see we have this new record in the table 4:31 and its user id belongs to my user account. 4:34 And so now that we have these security policies set up, 4:37 maybe we want to get even more granular with what users are allowed to do. 4:39 So in this example, we've set up security so that only users can read 4:44 records that belong to them. 4:47 But what if we want admins to have full privileges, meaning admins 4:49 could read all of the records, delete all of the records, 4:53 do whatever they wish with all of the records? 4:56 Well, that's where we need to introduce roles. 4:58 I'll see you in the next step to learn how we do this. 5:01

You need to sign up for Treehouse in order to set up Workspace

Sign up