Everything you need to know about Postgres Row Level Security | POSETTE 2024

Key Takeaways

[Music] hi I'm Paul the CEO and co-founder of superbase today I'm going to cover everything that you need to know about postgress Road level security I'll cover how to use post for Lial security why you might want to use it and some need performance tricks let's get into it so as I mentioned um we're going to cover in this talk everything you need to know about post g Road LEL security I've got a handy table of contents down the bottom so if you're watching this in video format you can just skip ahead first of all who am I my name is Paul Cobblestone I'm the CEO and co-founder of a company called superbase what is superbase we are a hostress hosting company and uh if you use us or if you're familiar with us you'll know that we also host a bunch of tools that make it really easy to use uh postgress in particular there is a tool uh called postrest which is a really cool open- Source uh project and it helps you to serve a restful API on top of any postgress database by automatically translating the database schema and rest for API um postgress leverages postgress very heavily including the role uh system and Road level security and this is the reason why we have become very familiar at Super based with Road level security and have a few sort of insights over the past four years we are also an open source company and we have now uh hosted and launched over a million postgress databases so um let's get into uh before I get into it um just to trigger warning I use some lowercase SQL in this that's largely because of the profile of customer that we were targeting in the early days was um not so much of a database developer so we decided to consolidate on lower case to make it more approachable so if you like upper case SQL I'm sorry um okay so what is Road level security you can think of it as really authorization for postgress and or comes in two flavors both authentication and authorization and authentication tries to answer the question uh is a user or role allows to access a database and then if that is true and they are allowed to access the database authorization uh answers the question now that they're in what specifically are they allowed to access and in this case with postgress you can write some SQL function that can determine either a true or a false around certain sets of data that a user can access I actually usually explain this to developers or at least this helped me in the early days uh was by using this mental model that roadl security is an implicit wear clause for your uh queries um for example let's say that we are selecting from a profiles table where I'm actually only allowed to see my own profile in this case I have forgotten to put a wear clause in so ideally I would have this um this security clause which specified that me user 1234 is only allows to access this um and so this is what postgress road level security is you can attach a policy I've got a very simple example uh which I'll go through in a second but the main thing that you need to know is that this wear Clause essentially is um this policy is essentially um giving us this sort of wear Clause filtering this means that if a user in this case user ID 1337 tries to access the U profiles table and they don't actually have um access to any any particular row then the RLS policy will get assessed for the profiles table and because it does not return true in this Clause then they will essentially return an empty data set so why would you use Road level security um there are numerous reasons the most obvious of course is security and in um security there's a concept called defense at debt and this really means that you add multiple layers in terms of your security so you might have security at your front end inside your application your API your middleware all the way down to your database the reason why I really like it at the database level is that it uh increases your default security posture in that everything that's built uh typically a postgress database is kind of the bottom of the stack and so that every application on top of the database will at least have the default security model of your database so we think it's really important to put um some data security at the database level and then any tool which is accessing the data will at least uh take that security posture the second reason is that you um might increase performance in some cases so um in this case um typically what happens with an application is that it has to make on the left hand side two requests to two different services so an app might first make an ALT request to an au service and that will U validate that a user is allowed to log in and get their permissions and then it might make a second request to your database to get the data itself now if you can bundle that all into a single Paradigm then you essentially cut down one whole network request so um if you use it in that way it's actually extremely fast um the other reason and probably one that resonates for a lot of people listening to this is just that you might be a bit of a postgress maxi we have a bit of a database Centric development workflow uh in superbase and that's one of the ways that we recommend using postgress um it's not for everyone quite honestly um but for those who really like SQL and really like the features of posts then using Road level security is a very neat way to to develop applications okay so let's go through the ins and outs of using an RLS uh using Road level security it's actually quite basic so here I have a table called profiles um all I need to do to enable Road level security for this table is called altered table profiles enable Road level security this means that anyone trying to access the table will get uh denied access unless they have a bypass RLS policy or you might be a super user or you can be the table creator um for this reason actually I generally recommend that you enable uh RLS for most tables anyway and it increases the default security posture of your role creation you then have to specify bypass RLS and you have to be a bit more explicit with your security on the database level so uh even if you don't plan to write RLS policies it's generally worth thinking about at least using Ro level security turning it on for all of your tables that's pretty much it so let's go through the anatomy of an actual policy first of all we're going to call create policy and then we give the policy a name I generally like to use a small descriptive name in quotes uh you can also use snake case as you typically would for postgress objects um if you do use a name like I have here then just know that it has to be relatively short otherwise it'll be truncated then from there you will attach the policy to a particular table so you just specify the table name and the schema um you will either have a permissive or restrictive policy I'll go through this in a sec but by default uh all policies are permissive then you ify the operation which can be select insert update delete or you can have all I actually don't recommend using all that is the default if you don't specify this uh I really recommend that you create a policy per operation and I'll get into the reasons wi so then the policy you can attach it to a postgress role I also recommend doing this uh for granularity and sometimes for performance so um you don't have to but I really enjoyed having a very granular policies then finally you can either have uh you specify the actual Clause inside either of a using or a width check and I'll go through an example where we have both of these to explain it more in depth but just from a high level using I like to think we'll check the existing value that is in the database so I'm going to validate against what is already there and a width check will check uh the incoming value specifically for inserts and updates so if I've got a value that's coming in I wants to check uh whether they should be allowed to insert this first of all we'll just focus on a using Clause here for our select so I will drill into this using Clause here um here I just have a um I'm selecting an or function this one actually exists inside super base it's very common this one's going to return U my current user ID uid and then I'm going to validate it against the column inside the profiles table user ID so what would this look like stepping through a really really small example uh oh yes and it should always return a true or a false and if it returns true then I'm allowed access false not so here is my profiles table I have three rows uh user ID 1 2 and three and I am user ID3 so here I have my statement and the select Clause will actually validate that I am user ID3 it will return that and now we're going to assess against each row whether three is equal to the user ID so first row 3 is not equal to 1 second row 3 is not equal to two third row 3 is equal to 3 it will allow me to select this particular row now I promis that I would show you also uh how to use these two uh using and uh with check together this is very common on a update statement so first of all we might I've just copied and pasted exactly the same Clause as I had before but what we want to do is first check whether you the user can update their own rows so that is using the using clause and then I actually want to also make sure that whatever value they're updating it to is also their own user ID otherwise they might be able to change their own user ID within a particular table so this is generally the um the way that you start uh building your your policies they're very granular and a lot of copying and pasting uh there is an important thing to know about policies you can actually specify two policies for uh one table so here I have one policy uh for a select statement on which will deny access returning false and I have another one which will allow access uh returning true so you might ask well will these be Ed or or together the answer is that actually the access will be allowed by default they will or together so um that there's one cabier to this if you have a restrictive policy then they will be anded together now I find this very complicated so my general recommendation around Road level security is that you try to keep it really simple by creating one policy per operation per role and then you put all of the conditions within that one policy um it really doesn't matter too much in terms of uh postgress but it really helps on the mental model uh for security trying to make sure that you can focus in on one particular use case and write all of the logic inside that um okay so a few dos and don'ts for post roal security the first due this one actually took us a few years at uh superb to realize if you are calling a function inside your um RLS policy then you should try to wrap it in a select statement you can see I've got a select and I've also got these brackets the reason why is that on the right hand side the all. uid uh will be this function will be assessed for every single row versus if they put it on the left with a select then the postest planner will actually call that uh Value First and it will only call it once so um generally try to wrap any function call into uh with a select statement um also as I mentioned try not to use uh the default for all I really recommend keeping your policies as granular as possible so one per operation select insert update delete the other thing is that uh here is that off. ID function that we were actually calling and this is roughly what it looks like inside superbase is going to return the user ID from a a JWT generally you want these um or as much as possible you want to write these functions so that they're stable not volatile and specifying that they're stable for the postest planner this means that if they are volatile then they have to be assessed per Row in the database so they'll get called many times so for performance reasons if you can make them stable then they can be assessed once at the start of the query then if you are also using these functions I generally recommend putting them inside a private schema and then creating these functions with a role which has bypass RLS um then if you create them with that and you have security definer it will assume the security posture of the person creating the function uh if it is security invoker and that role does not have bypass RLS then actually anything you call within let's say you're selecting the permissions from a permissions table it will also apply uh the RLS trickling down through each of the uh function calls so because we just want to return a say data set uh we can actually in this case use uh a role which can bypass RLS to return back the data set and then inside the RLS policy use the data set to assess a true or false statement and then finally I know that I said um or I think second to last I know that I said that you should um you can think of it as an implicit wear uh policy but for performance you should always still um specif by your we statement when you're selecting from a table I thought it was very cool at the start um actually that you could just write um blanket select statements and that it would only return the data um that you had access to but actually for performance reasons you want the wear statement because it will cut down the amount of data before applying the RLS rules so it's very fast if you just have that filter uh to start with then finally um post now has support for RLS in views uh the way that this works is uh once again through security and Booker versus security definer um so you can now specify views I think it's from PG 15 and above um with security invoker true what this means is that um if you are a security invoker false whoever created The View it'll assume their uh level of Grants security and focus true we'll assume the RLS rules of the role which is calling the table so in this case if my view is actually referencing uh private profiles it will apply the RLS RS that we have attached to the profiles table so um generally I recommend U using the statement from uh actually almost always these days um there are a ton of tips and tricks I've just covered a few of them but we have some docs inside the superbase docks uh with benchmarks of um each one of the things that I've gone over and a few more and uh how much performance uh gains each one of these actually uh will give you finally I just want to give some shout outs um a lot of this content is derived from Steve uh and uh who is the maintainer of postgress and works at superbase and also Gary who did a lot of the performance benchmarking of RLS and basically a lot of the community around superbase who have really leaned into RLS so um just a big shout out to them finally if you are looking for a post press database then um yeah do check us out and you can launch new database with database.