Implement Authorization using Row Level Security (RLS) with Supabase (Step By Step Guide)
ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- In this video, Jon Meyers explains how Row Level Security (RLS) works, why you should enable it and how to enable RLS / Row Level Policies in Supabase.
▶ Read more HERE: supabase.com/d...
Jon steps through implementing some common policies using the Supabase Dashboard and demonstrates how rules across multiple tables can work together to restrict access.
PostgreSQL provides a simple syntax for RLS, allowing access rules to be declared in the Database itself. This can make queries more efficient as the client can query the Database directly, without the need for a middleware API.
Additionally, RLS can help to protect the Database against poorly written API code that could potentially leak or compromise sensitive data.
▶ Read more about RLS and Auth HERE: supabase.com/d...
Jon from Supabase is back, helping you understand the power and benefits of Row Level Security (RLS) in PostgreSQL, and how easy it actually is to implement in your apps. RLS might sound difficult to implement or something that you can't do yourself, but with Supabase's new AI tools, it's simpler and more accessible than ever - ANYONE can do it.
What you'll learn in the RLS Supabase tutorial video:
✅ Understanding RLS: Discover what RLS is and how it serves as a robust method to implement authorization directly in your PostgreSQL database.
✅Learn the differences between queries NOT using RLS and the benefits of RLS (Row Level Security Policies) and why you REALLY need to start using them!
✅ How to Activate RLS in Supabase SAFELY: Learn how to activate RLS through the Supabase dashboard or by using SQL commands within the Supabase SQL editor, so your tables are securely managed.
✅ Creating and Testing RLS Policies: Step-by-step guidance on writing policies that control who can see, edit, or delete data, demonstrated with practical examples such as managing the visibility of blog posts and comments.
✅ Testing RLS for Specific Users: Find out how to test these policies you’ve set up to see how they behave for different user roles, making sure your application's security is dynamic depending on your policy.
By the end of this tutorial, you'll know how to set up RLS policies for your projects in Supabase, ensuring that your database only reveals what it should to the right users.
We hope this helped you master your database security. Start using RLS with Supabase today!
Chapters:
0:07 What you’ll learn
0:22 Architecture Without Row Level Security (RLS) - Client, API, PostgreSQL
0:53 Architecture With Row Level Security (RLS) benefits
1:37 How to implement Row Level Security policies in Supabase
2:22 Application using the database
3:20 Use and Enable Row Level Policy
4:52 How to add a new policy
6:31 How to review final RLS policy and show equivalent SQL statement
7:56 Create a new policy so that everyone can see published post
12:56 Create rule/policy for insert
14:30 Create a policy for deleting comments
16:09 Check logged-in user is the user who created the post
💻 Videos to watch next:
▶ Watch ALL Postgres/Supabase videos: • Learn Postgres
▶ Add RLS to your apps and websites with Supabase & AI: • Use AI to enable and g...
▶ Supabase is now GA: • Supabase is now GA
👇 Learn more about Supabase 👇
🕸 Website: supabase.com/
🏁 Get started: app.supabase.com/
📄 Docs: supabase.com/docs
🔔 Subscribe for more tutorials and feature updates from Supabase: / @supabase
📱 Connect with Us:
🐙 Github: www.github.com...
💬 Discord: discord.supaba...
🐦 Twitter: / supabase
▶ Instagram (follow for memes): / supabasecom
ABOUT SUPABASE:
Supabase is the open source Firebase alternative. Supabase provides a full Postgres database for every project with pgvector, backups, realtime, and more. Add and manage email and password, passwordless, OAuth, and mobile logins to your project through a suite of identity providers and APIs.
Build in a weekend, scale to millions.
#Supabase #AppDevelopment #RealtimeApps #DeveloperTools
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.
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.
Awesome video! Much needed for understanding RLS!
Thanks Morgan! 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.
This was a really helpful video. Thanks for going through so many examples in detail.
Thanks Haani! Glad you enjoyed it! 🙌
super nicely done!! very clear explanation and easy to use UI
Thanks Swyx! Glad you enjoyed the video!
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! 🙂
Thats was super clear and incredibly useful!
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
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
It seems like the radio buttons for the CRUD (in this case SIUD!) operations should be checkboxes.
Good call! 👍
Ohhh, this video it's fantastic, I have been fight with theses issues for 2 days. Thank you.👍
Just what I needed! Thanks Supabase team!
Thanks Levi, glad you enjoyed it! 🙌
Thanks a lot for this video! It was "Supa" helpful!
Awesome, glad you enjoyed it!
This is awesome!!!
Thanks James! I'm glad you enjoyed it! 🙂
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 :)
Great explanation thank you jon
This video rekindled my interest in Dadaism.
Good stuff! Glad you enjoyed it!
Is it possible to see the github repo for this example? :)
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.
Y'all should share the blog post repo used here.
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
Youre a great teacher!
i want to implement auth using nodejs and js its so hard to find good resources out there! we need a more detailed video
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
That was a fun video to watch, thanks Jon!
What is 'WITH CHECK' for? I don't understand why it's necessary.
Thumbs up for the Pokemone references
this is what we want. simple and ⚡️
thank you jon🔥
Great Video!
Thanks Nico! Glad you liked it! 🙂
Super helpful and quite entertaining too. Thank you
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
This was really, really helpful, nice 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.
Violet Town?! *triggered* Great video wish someone explained this a decade ago or smth.
question does rls inner select queries impact performance noticibly?
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?
How can we do this on a rest api for instance fast api
hey, that's awesome. you're doing a great job! Jon
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? 🤞🏼
I don't like to have any settings / authorization code outside of version control. Maybe Supabase is not for me then?
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?
Thank you so much. This is really helpful 🎉
what is the difference between 'using expression' and 'with check expression'?
Hope more videos about RLS
how do you simulate a user logged in for supabase in nodejs or js?
What if we want to server-side render everything? Then all of this is totally irrelevant right?
Great tutorial! Is there a github link to the project shown in the video?
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
Happy to be the '404'th person to like this! Thanks for the clear demo and examples, really helps
Great tutorial 👏👏
Thanks Zakaria, glad you enjoyed it! 🙌
how did you logged it the users ?
Please link to code snippets or blog post! (these are awfully hard to copy :( )
I like the VScode theme, may I ask you the name?
Is this codebase opensourced?
this is the video i want
Yaay!!! I am sold
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?
Can I using RLS for Next.js API routes too ???
Great video ^^
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?
good ~
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
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!
wheres the source code for this?
awesome :) keep going
Need full source code of that
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.
I want to implement all of codes with same ui. Could you make for full-stack video for the entire project?
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?
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...
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 👍
😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍😍
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
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?
Palette Town
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?
it's data not data
😆 I'll keep this in mind for the next one!
What's the difference between USING and WITH CHECK here?