Rough outline 0:32 Overview of traditional three piece architecture with API, Database and Client 0:57 Architecture with Row Level Security implemented on the database 1:40 Row Level Security Example 2:22 Overview of application using the Database 3:52 How to Enable RLS 4:52 How to add new Policy 6:53 How to review final policy and show equivalent SQL statement 8:01 Create a policy so that everyone can select 13:05 Create policy for inserts 14:29 How to use a policy with delete 16:10 How to check that user who is currently logged in is user who created post
Great tutorial and a great product. Thank you! As a cyber security expert who is trying to code his cyber security SaaS-based product, this is priceless!
USING expression: Controls which rows are visible to the user for SELECT and DELETE operations. WITH CHECK expression: Controls which rows can be inserted or updated by the user for INSERT and UPDATE operations.
0:00 Intro 1:40 Presentation of schema 3:37 where is setting RLS signifie "Row-Level Security" 4:10 Create RLS : Enable delete access for users based on their user ID 7:55 Create RLS : Enable user to select a published post 10:50 Create RLS : Enable user to view comments for posts they can view 12:37 Create RLS : Allow user to Insert comments on post 13:57 Create RLS : Allow user to Update comments on post 14:55 Create RLS : User can moderante comments on their on posts 16:32 Final App :) 17:09 The End share :)
Thanks for this video. I was really on the fence about trying Supabase (or any BaaS really) precicely because I need this functionality and didn't know it existed.
Thank you for the video! Please help me with the following: What about if James is allowed to view all posts from Gary and Ash but they both can only keep viewing their own posts as shown in the video. I guess I need to add a field "isAdmin" in the user table to distinguish James from Gary and Ash, however I cannot figure it out the sql statement to retrieve/fetch the value from "isAdmin" field in user table. Please, help me. Thank you in advance
How to insert into a table that's RLS enabled using supabase-py? There's one video from someone on TH-cam that says to add a line like this supabase.postgrest.auth = access_token.. But it doesn't work.
How do you use RLS while also having the usual middleware authorisation? I have some tasks which I only want my application to be able to perform but dont understand how to do this while also having RLS enabled.
Awesome. Firebase allows me to allow read a single document as long as the document id is given as a parameter - is that possible in supabase? (so a user is not required to be logged in - but he has to guess/know the guid id in order to see a document)
In RLS you can basically do anything supported by SQL - read from other tables, check values exist, call postgres functions etc - so as long as you can write it with SQL, you can include it in a policy. Since you can call postgres functions, this means you can also include extensions and basically do anything you want. We will be adding more videos to this playlist over the next few weeks demonstrating how powerful postgres functions are: th-cam.com/play/PL5S4mPUpp4OtkMYxAxNpEuuDQuI2pWF3-.html
Great video but would have been cool to have a no-code way to create these policies, at least for very simple cases like this video.Similar to what is possible with Directus 9 headless CMS
I tried finding this but was not able to get an exact answer. In this example let's say I can comment on POST1 but cannot comment on POST2, now I as an attacker add a comment on POST1 (since I am allowed to insert a comment there) and later change POST id to POST2 by an update call (since I can update POST created by myself). how to write an update policy for the comment table where I am (attacker) not able to update the comments table foreign key field.
Wow! For me who knows absolutely nothing about postgres and rbs this sounds really hard, especialy when it will grow bigger and get more complexity with joins and much more... . Is there a way to write test cases for that or do I need external tools for that? If I need some external tools, is there a way to run the tests against a temporary test database?
You can write test cases in SQL by using the `set_config` function to impersonate different roles. Keep an eye on our TH-cam channel as we will be putting together a video on this soon!
Maybe I'm missing something, but it's not at all clear to me how the db gets the user's id, for instance. Is this placed in the request? My understanding is that the db receives the request from the app, but is not aware of any app-side data. Also feels like we need to learn to write more complex sql queries, which is a bit annoying considering the promise of Supabase is to build quickly
My app will be the only entity performing CRUD operations on my database so there isn't any user_id fields to authenticate against. I'm setting up a createClient JS function which takes the DB's url as well as key. If all my operations are behind that, do I even need specific RLS?
Hello! This was a great presentation and absolutely solved more than half of the problems with the concept. I would love to have followed from the beginning of the entire blog(post/comment) app in this video. I am learning react and Supabase and wished I could learn from the example here how the tables, RLS and the queries were all built from the start. Is there a link I can access the docs in one place?
Very well explained and understood. But I have two issues. 1. On the dashboard we option to add only one site url as allow-list. But with firebase we can add as many as needed - because most of the times we have a localhost for dev to test a feature and the live site too. 2. I want to add say a referral ID, (as in the ID of a user who refers another user who's signing up) but I cannot insert to profiles table (which have a basic RLS for authenticated users) since the email is not verified yet (so session is still null), in this case what's the appropriate RLS to use? Thanks
Good news! There is plenty more awesome just like this! I have recently released a free egghead course that goes into much more detail on building a SaaS product using Next.js, Supabase and Stripe: egghead.io/courses/build-a-saas-product-with-next-js-supabase-and-stripe-61f2bc20 We also have a new postgres functions playlist that we will be adding to over the next few weeks: th-cam.com/play/PL5S4mPUpp4OtkMYxAxNpEuuDQuI2pWF3-.html
I definitely understand how RLS can keep your data safe, but giving the client direct DB access via SQL makes me nervous. Couldn’t a user still engage in abusive behavior by issuing inefficient queries with the goal of DOS’ing the database?
You are not giving the client direct access to the DB. Everything is still going through a REST API, the "Postgrest" part of Supabase. It just feels like going directly to the DB because the REST API was created automatically by Supabase.
@@mbalslow OK, I see now that PostgREST's interface is designed to only allow for joins on foreign key columns. That should constrain queries' complexity and hopefully prevent abuse via obtuse queries. It looks like in a production environment PostgREST expects itself to be behind a proxy such as Nginx that can perform request rate limiting. Query abuse was my primary concern about using PostgREST as I have experienced poorly performing queries bring down application services. Thanks!
It seems clear how the RLS (with well designed rules) can prevent inappropriate actions at the db level, but are some of these rules being duplicated within the client at the client level? For example, Gary only has a delete icon for his own comment, but Ash has delete icons for both. The client wouldn't automatically know that, since those RLS rules would only be applied on database actions. I imagine this is illustrated in another tutorial, but it seems like it would involve duplication of rules...
wow - what an easy-to-follow tutorial on an arcane topic like RLS - Really found the 2 side-by-screen views with one-step-at-a-time change to be very easy to follow. It is a pity that I watched this video after watching the other 5 videos in the Auth deep dive which were not as easy to follow as this one. Can you please put this video at the top of the playlist or better yet, delete those videos and do a summary of them now in 2023, we have gotten Storage RLS which is a bit different.
Supabase just killed all roles memberships by implementing users without a 1:1 mapping to a real user. Good luck implementing a group policy without using pg_has_role().
This one is just to help explain the concept of RLS and policies. There are many ways to do authorization for different use cases! Would be great to see an alternate video using role based authorization 👍
How so? They’re just policies. When you enable RLS, no user has authorization to perform actions. It’s all opt-in, so each policy you write is explicitly granting specific permissions. This is much safer than writing an API that accesses the same database that then has to implement these controls. The database is wide open. You have to have these rules somewhere, why not at the row?
@@dete503 It's only safer if you messed up with the security on the API, otherwise it's as safe not safer. Also, using RLS will have an impact on the performance, an API will do the check just once at the start of the call which is inherently faster than performing a check multiple times during a query. Finally, RLS only works at the row level, so it's less safe in this sense compared to an API, if you want to secure your columns you'll have to deal with triggers or something else I guess.. :/ So RLS do have drawbacks, I'm not saying one is better than the other but I currently prefer implementing the security at the business logic layer.
Dude am i stupid ? I understand the concept, but how do you log yourself ? you have no tables user ? What do you give on the route for knwing it's your user_id ? bad tuto
feedback: the video of the face is too big and not adding much value imo. i would prefer to get rid of it and just focus on the screen or have a smaller loom style round video of the face.
Is this not going to create a mess of n+1 queries? There is a reason we moved away from logic in the db a long time back. Why are you guys pushing this again? What has changed?
Learn more: supabase.com/docs/guides/auth/row-level-security?Ow_Uzedfohk
Rough outline
0:32 Overview of traditional three piece architecture with API, Database and Client
0:57 Architecture with Row Level Security implemented on the database
1:40 Row Level Security Example
2:22 Overview of application using the Database
3:52 How to Enable RLS
4:52 How to add new Policy
6:53 How to review final policy and show equivalent SQL statement
8:01 Create a policy so that everyone can select
13:05 Create policy for inserts
14:29 How to use a policy with delete
16:10 How to check that user who is currently logged in is user who created post
Thanks for putting this together! Super helpful! 💯
Jon is an excellent teacher. He's entertaining to watch, but also does a great job of providing meaningful examples. Thanks!
I think Jon's convention to make table names singular is interesting and feels more natural when doing queries
RLS has been a concept that has constantly eluded my mind. At least until now
Thanks! The note "we see it from the point of view of how the user gets the data" or something like that saved me.
Awesome video! Much needed for understanding RLS!
Thanks Morgan! Glad you enjoyed it!
This was a really helpful video. Thanks for going through so many examples in detail.
Thanks Haani! Glad you enjoyed it! 🙌
Great tutorial and a great product. Thank you! As a cyber security expert who is trying to code his cyber security SaaS-based product, this is priceless!
Thank you Jon for explaining RLS with supabase!
Not a problem at all! Glad you enjoyed it! 🙂
super nicely done!! very clear explanation and easy to use UI
Thanks Swyx! Glad you enjoyed the video!
Ohhh, this video it's fantastic, I have been fight with theses issues for 2 days. Thank you.👍
Thumbs up for the Pokemone references
Thats was super clear and incredibly useful!
what is the difference between 'USING expression' and 'WITH CHECK expression' ?
USING expression: Controls which rows are visible to the user for SELECT and DELETE operations.
WITH CHECK expression: Controls which rows can be inserted or updated by the user for INSERT and UPDATE operations.
0:00 Intro
1:40 Presentation of schema
3:37 where is setting RLS signifie "Row-Level Security"
4:10 Create RLS : Enable delete access for users based on their user ID
7:55 Create RLS : Enable user to select a published post
10:50 Create RLS : Enable user to view comments for posts they can view
12:37 Create RLS : Allow user to Insert comments on post
13:57 Create RLS : Allow user to Update comments on post
14:55 Create RLS : User can moderante comments on their on posts
16:32 Final App :)
17:09 The End share :)
Just what I needed! Thanks Supabase team!
Thanks Levi, glad you enjoyed it! 🙌
Thanks for this video. I was really on the fence about trying Supabase (or any BaaS really) precicely because I need this functionality and didn't know it existed.
Great explanation thank you jon
Thank you for the video! Please help me with the following: What about if James is allowed to view all posts from Gary and Ash but they both can only keep viewing their own posts as shown in the video. I guess I need to add a field "isAdmin" in the user table to distinguish James from Gary and Ash, however I cannot figure it out the sql statement to retrieve/fetch the value from "isAdmin" field in user table. Please, help me. Thank you in advance
Is it possible to see the github repo for this example? :)
That was a fun video to watch, thanks Jon!
How r the user_id values in the 2 tables created so they match up with the user_id from the authenticated schema?
question does rls inner select queries impact performance noticibly?
This is awesome!!!
Thanks James! I'm glad you enjoyed it! 🙂
How to insert into a table that's RLS enabled using supabase-py? There's one video from someone on TH-cam that says to add a line like this supabase.postgrest.auth = access_token.. But it doesn't work.
how do you simulate a user logged in for supabase in nodejs or js?
It seems like the radio buttons for the CRUD (in this case SIUD!) operations should be checkboxes.
Good call! 👍
How do you use RLS while also having the usual middleware authorisation? I have some tasks which I only want my application to be able to perform but dont understand how to do this while also having RLS enabled.
Awesome. Firebase allows me to allow read a single document as long as the document id is given as a parameter - is that possible in supabase?
(so a user is not required to be logged in - but he has to guess/know the guid id in order to see a document)
Yeah! Basically anything can be written as a policy. eg:
create policy "policy like firebase"
on table_name for select
using ( id = 'XXXXXXX' );
@@Supabase i think the answer is to create a postgres function and use it inside a policy
I am wondering if there exists limitation about what RLS can do, so that to inevitably reach for other auth mechanism behind an api?
In RLS you can basically do anything supported by SQL - read from other tables, check values exist, call postgres functions etc - so as long as you can write it with SQL, you can include it in a policy. Since you can call postgres functions, this means you can also include extensions and basically do anything you want. We will be adding more videos to this playlist over the next few weeks demonstrating how powerful postgres functions are: th-cam.com/play/PL5S4mPUpp4OtkMYxAxNpEuuDQuI2pWF3-.html
this is what we want. simple and ⚡️
thank you jon🔥
What is 'WITH CHECK' for? I don't understand why it's necessary.
Thanks a lot for this video! It was "Supa" helpful!
Awesome, glad you enjoyed it!
Great video but would have been cool to have a no-code way to create these policies, at least for very simple cases like this video.Similar to what is possible with Directus 9 headless CMS
How can we do this on a rest api for instance fast api
I tried finding this but was not able to get an exact answer. In this example let's say I can comment on POST1 but cannot comment on POST2, now I as an attacker add a comment on POST1 (since I am allowed to insert a comment there) and later change POST id to POST2 by an update call (since I can update POST created by myself).
how to write an update policy for the comment table where I am (attacker) not able to update the comments table foreign key field.
hey, that's awesome. you're doing a great job! Jon
Y'all should share the blog post repo used here.
This was really, really helpful, nice work!
I don't like to have any settings / authorization code outside of version control. Maybe Supabase is not for me then?
i want to implement auth using nodejs and js its so hard to find good resources out there! we need a more detailed video
This video rekindled my interest in Dadaism.
Good stuff! Glad you enjoyed it!
Wow! For me who knows absolutely nothing about postgres and rbs this sounds really hard, especialy when it will grow bigger and get more complexity with joins and much more... . Is there a way to write test cases for that or do I need external tools for that? If I need some external tools, is there a way to run the tests against a temporary test database?
You can write test cases in SQL by using the `set_config` function to impersonate different roles. Keep an eye on our TH-cam channel as we will be putting together a video on this soon!
@Jon did you end up publishing the video on testing the SQL? 🤞🏼
Maybe I'm missing something, but it's not at all clear to me how the db gets the user's id, for instance. Is this placed in the request? My understanding is that the db receives the request from the app, but is not aware of any app-side data.
Also feels like we need to learn to write more complex sql queries, which is a bit annoying considering the promise of Supabase is to build quickly
My app will be the only entity performing CRUD operations on my database so there isn't any user_id fields to authenticate against. I'm setting up a createClient JS function which takes the DB's url as well as key. If all my operations are behind that, do I even need specific RLS?
Great tutorial! Is there a github link to the project shown in the video?
what is the difference between 'using expression' and 'with check expression'?
how did you logged it the users ?
Super helpful and quite entertaining too. Thank you
Youre a great teacher!
Please link to code snippets or blog post! (these are awfully hard to copy :( )
Hello! This was a great presentation and absolutely solved more than half of the problems with the concept. I would love to have followed from the beginning of the entire blog(post/comment) app in this video. I am learning react and Supabase and wished I could learn from the example here how the tables, RLS and the queries were all built from the start. Is there a link I can access the docs in one place?
I like the VScode theme, may I ask you the name?
Hope more videos about RLS
What if we want to server-side render everything? Then all of this is totally irrelevant right?
Can I using RLS for Next.js API routes too ???
Happy to be the '404'th person to like this! Thanks for the clear demo and examples, really helps
Thank you so much. This is really helpful 🎉
Great Video!
Thanks Nico! Glad you liked it! 🙂
auth.uid() doesn't seem to work when making requests on the server. Any ideas?
having the same issue, did you ever figure it out?
Violet Town?! *triggered* Great video wish someone explained this a decade ago or smth.
Very well explained and understood. But I have two issues.
1. On the dashboard we option to add only one site url as allow-list. But with firebase we can add as many as needed - because most of the times we have a localhost for dev to test a feature and the live site too.
2. I want to add say a referral ID, (as in the ID of a user who refers another user who's signing up) but I cannot insert to profiles table (which have a basic RLS for authenticated users) since the email is not verified yet (so session is still null), in this case what's the appropriate RLS to use?
Thanks
Great tutorial 👏👏
Thanks Zakaria, glad you enjoyed it! 🙌
Is this codebase opensourced?
Finally found this
i want more like this awesome
Good news! There is plenty more awesome just like this! I have recently released a free egghead course that goes into much more detail on building a SaaS product using Next.js, Supabase and Stripe: egghead.io/courses/build-a-saas-product-with-next-js-supabase-and-stripe-61f2bc20
We also have a new postgres functions playlist that we will be adding to over the next few weeks: th-cam.com/play/PL5S4mPUpp4OtkMYxAxNpEuuDQuI2pWF3-.html
Yaay!!! I am sold
Great video ^^
Need full source code of that
wheres the source code for this?
this is the video i want
Isn't it to expensive to check if a comment belongs to a post that I can see? For every comment?
How else would you do it?
I want to implement all of codes with same ui. Could you make for full-stack video for the entire project?
I definitely understand how RLS can keep your data safe, but giving the client direct DB access via SQL makes me nervous. Couldn’t a user still engage in abusive behavior by issuing inefficient queries with the goal of DOS’ing the database?
You are not giving the client direct access to the DB. Everything is still going through a REST API, the "Postgrest" part of Supabase. It just feels like going directly to the DB because the REST API was created automatically by Supabase.
@@mbalslow Can someone still issue arbitrary SQL statements against the database using the REST service?
@@pchasco no. I don't think that was ever possible? You can call exposed stored procedures but those are still under the RLS policies.
@@mbalslow OK, I see now that PostgREST's interface is designed to only allow for joins on foreign key columns. That should constrain queries' complexity and hopefully prevent abuse via obtuse queries. It looks like in a production environment PostgREST expects itself to be behind a proxy such as Nginx that can perform request rate limiting. Query abuse was my primary concern about using PostgREST as I have experienced poorly performing queries bring down application services. Thanks!
awesome :) keep going
Hi, I dont get it..
RLS is like putting where in sql statement..
Isn't that should be put inside the code where it pulls data based on where?
God bless u
It seems clear how the RLS (with well designed rules) can prevent inappropriate actions at the db level, but are some of these rules being duplicated within the client at the client level? For example, Gary only has a delete icon for his own comment, but Ash has delete icons for both. The client wouldn't automatically know that, since those RLS rules would only be applied on database actions. I imagine this is illustrated in another tutorial, but it seems like it would involve duplication of rules...
wow - what an easy-to-follow tutorial on an arcane topic like RLS - Really found the 2 side-by-screen views with one-step-at-a-time change to be very easy to follow.
It is a pity that I watched this video after watching the other 5 videos in the Auth deep dive which were not as easy to follow as this one. Can you please put this video at the top of the playlist or better yet, delete those videos and do a summary of them now in 2023, we have gotten Storage RLS which is a bit different.
good ~
Supabase just killed all roles memberships by implementing users without a 1:1 mapping to a real user. Good luck implementing a group policy without using pg_has_role().
This one is just to help explain the concept of RLS and policies. There are many ways to do authorization for different use cases! Would be great to see an alternate video using role based authorization 👍
😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍
I know that Ash and Gary are kind to each other but, to be honest I think Gary doesn't like ash that much...
This is going to be a nightmare to manage and test. This is ok for small and hobby projects, but beyond that......
How so? They’re just policies. When you enable RLS, no user has authorization to perform actions. It’s all opt-in, so each policy you write is explicitly granting specific permissions. This is much safer than writing an API that accesses the same database that then has to implement these controls. The database is wide open. You have to have these rules somewhere, why not at the row?
@@dete503 It's only safer if you messed up with the security on the API, otherwise it's as safe not safer. Also, using RLS will have an impact on the performance, an API will do the check just once at the start of the call which is inherently faster than performing a check multiple times during a query. Finally, RLS only works at the row level, so it's less safe in this sense compared to an API, if you want to secure your columns you'll have to deal with triggers or something else I guess.. :/
So RLS do have drawbacks, I'm not saying one is better than the other but I currently prefer implementing the security at the business logic layer.
Palette Town
Dude am i stupid ? I understand the concept, but how do you log yourself ? you have no tables user ? What do you give on the route for knwing it's your user_id ? bad tuto
you are. i can see that you didnt read supabase auth docs
feedback: the video of the face is too big and not adding much value imo. i would prefer to get rid of it and just focus on the screen or have a smaller loom style round video of the face.
it's data not data
😆 I'll keep this in mind for the next one!
Is this not going to create a mess of n+1 queries? There is a reason we moved away from logic in the db a long time back. Why are you guys pushing this again? What has changed?
What's the difference between USING and WITH CHECK here?