 Hi there, so hopefully everybody's in the right talk here. This talk's gonna be about role-level security as implemented in and committed to Postgres for inclusion in 9.5. It's not a guarantee, of course, but having been committed and now has been through a number of improvements and whatnot, feeling pretty comfortable about it, and very much looking forward to when it rules out. 9.5 is scheduled to be coming out kind of in the fall, the hope is to get back on the same kind of track that we used to be on where we had fall releases. 9.4 slipped a little bit for JSON-B and a few other things, but looking forward to moving on. So as mentioned, I'm the Chief Technology Officer at Crunchy Data Solutions. I'm also one of the committers. I'm a major contributor way back when I implemented the role system. So who here is familiar with the role system? Okay, good, and almost everybody. It's important, it's an important piece of Postgres. So hopefully everybody's familiar with it to some extent. I also did column-level privileges, so I've been kind of on this security thing for a while. Column-level privileges were added in 8.4. They were added principally because that other open-source database that has these weird different storage engines already had it, and I was like, well, that's not cool. Whenever I go to talk to people about, well, you don't want to run that, they're like, well, it's got column-level privileges. I'm like, oh, all right, let's go fix that, so we did. I've also made contributions to PLPGSQL, to PostGIS. I've got code in the Linux kernel. I have code in Sendmail. I've got code in a lot of different pieces of the open-source community. I've been working in open-source for, I don't know, 15 years or something like that. So lots of code out there. Kind of scary and frightening when you think about it. So the first thing I wanted to hit on a little bit was what is ROS when it comes to the Postgres ROS, right? What is the Postgres kind of consideration for it, right? Different products have different things that they call ROS or label-based security or whatnot. When it comes to Postgres, it's simply a way to provide an individual view of the data in the system, right? I say for each user, it's not necessarily that it's gonna be different for every different user. It could be, right? The whole point here is that we wanna filter out records based on some criteria about the user or the query or potentially anything, right? It's just this general filter records feature, right? It's a way of saying, I wanna add a where clause to the queries that are being run against this table. I mean, at the very base level, that's what it is. We also wanna be able to limit what records get added back into a table. So I think that's an important piece of it. I know I think about it a lot when I'm talking about it from a perspective of what is ROS. I think about the filtering aspect of it, but it's filtering in both directions, right? It's both filtering data that can be selected out by the individual user or the individual connection or what have you, and it's also about what is that connection allowed to add back into the system, right? What are they allowed to insert or update or potentially delete in the table? One of the big things that I'm really glad that I was pushed to work on for ROS is the ability to have them be specified for a particular role or a particular user, right? Originally, my thinking was that all policies would be generic, right? They would apply to all users in the system, but where that becomes an issue is that the policies end up being, the way that they get combined means that it becomes inefficient for us to be able to just kind of throw all policies at a given query, or at least it can be inefficient. It depends on exactly what the policies are doing, but I'll talk a little bit more about that in a minute. One of the other things that's important was how we require that policies are going to be applied. It's a security associated feature, and therefore we have to make it very clear when it's going to be applied, when it's not going to be applied, and I'll talk to that a bit more as we move forward, but the point is that it's got to always be there. So from the standpoint of right from the get-go, we have this default-deny approach when it comes to role-level security. As soon as you enable role-level security, there's a de facto policy that's implemented, right? Specifically to ensure that there's always something happening, right? There's always a policy being enforced there, and so I think that's a really key aspect of how role-level security has been implemented in Postgres. So that's kind of a real high level of ROS, right? I want to take a few minutes to kind of walk people through the way that that can be done even in 9.4, right? So there's a few different ways of implementing this filtering concept, right? One of the ways is using what are called security barrier views, okay? ROS feature is built on top of the same code paths that security barrier views are built on top of, right? The issues that I find with using security barrier views, because somebody may ask, why use ROS? Why not use security barrier views? The issue I find with it is that means you have to build a view, right? And that view has got its own permission set, right? Permissions that are independent of the table underneath. And now you've suddenly got two different objects that you're managing all of those grant-based permissions on top of, right? And that gets complex and hairy and can lead to security problems if you do it wrong. So if you end up granting the same rights to the view and the same as you do to the table, then the user, whoever's getting those rights, doesn't actually have to go through the view, right? If you say grant select on the view and grant select on the table, well, if they query the table directly, they no longer are going, the filtering's no longer being applied that you wanna be using with, which is the point of having this view. So another thing is that you have to make sure you remember the security barrier option. It's not a huge deal, but people coming from other databases may not realize it. One of the things that's really important about the way Postgres is able to optimize queries that are given to it by the user is our ability to do selective pushdown of particular qualifications. So say, for example, I want all of the rows greater than 1,000, right? I can push that qualification very far down and potentially use an index scan to make it a very efficient way of getting at the data. However, with the way Postgres is extremely extensible, right, there's a challenge with that when it comes to security of the system. And what I mean by that is that someone can create a function. Anybody can create a function in Postgres, and they can make that function, they can put that function in the where clause of the query that they're writing. So they have a query that's going against a view, and then they have this where clause with a conditional says my function, and it takes some column argument, or maybe it takes no arguments, it doesn't really matter. The user's also able to specify what is the cost of that function, and it can make it very inexpensive, right? It can make it so that that cost is so inexpensive. And what Postgres will naturally do is wanna push that all the way down, right? We'll push that as close to the data as we can, and we might push it past what are the qualifications in the view definition. Where that becomes a problem is that the function is running as the user who called it, but now it's getting fed records from the table prior to those records being checked through the conditional that was in the where clause. So the security barrier option is specifically implemented to address all of that, right? It essentially says that only those conditionals that we consider leak-proof, only those operators that we consider or operator families, but we consider leak-proof are allowed to be pushed through a security barrier view. The same is true for O-level security. Again, they're built on the same code paths and the database, and so when you mark a function as leak-proof, that means that that function is then able to be, or operator is able to be pushed down past other qualifications, and therefore you have to be really careful and make sure that it's actually not gonna end up leaking any of your data. So to create, and this is just a quick example of how one might create such a table in a view. I didn't include all the grant commands and whatnot, but this is essentially what you would do. You create your table and then you create your view with security barrier, and then you'd have this conditional where my user equal current user, right? When the user executes a query against my view, this conditional gets added because security barrier is on, none of the conditionals that the user specifies on their half of the query are pushed down unless they're using operators that are marked as leak-proof. Coming back in to the data, right? Having the data come back from the outside into the database, you can do that with the security barrier view using what's called a with check option. So the with check option basically says that if you insert to a view, so one of the things to remember right is that Postgres now supports what are called simple views as automatically updateable. So what that means is that if your view doesn't have any joins, if it doesn't have any aggregate, if it doesn't have all these other special bells and whistles that SQL supports, then we can say, okay, that view is so simple that we know how to take and transform a particular record or tuple that's being inserted against that view and transform it to operate against the table underneath. Right? What the, one of the issues with that is that someone could potentially insert records that are not visible through the view. Right? If they're not visible through the view, maybe you didn't want that user to be able to actually do that, right? Actually insert that data. So with the example that we have here, so this is more to the last one where my user equal current user, I've added this check option. Okay, what the check option does is it says, okay, if my user equal current user isn't accurate, right, if that fails on an insert, that conditional check fails on an insert, we will throw an error saying sorry, we're not gonna accept that. It's a little bit more complex than that based on the way the SQL standard implement, requires us to implement it, which it actually says after all other things are done, all other check constraints, index constraints, uniqueness, anything like that, after all that other stuff is done, then we check and see is that tuple visible? Right? That includes any other triggers or anything else that get fired, right? Basically it's the very last thing we do is say, okay, I've done all this work. Is this tuple visible to this user who's running this insert? And that's why it's called a with check. So we're adding that check onto the very end of the overall process. Okay, again, real level security is built on top of this with check option for security barrier views as well. And so it shares some of the same attributes. Right now as committed to nine five, it shares the with check timing as well. We're looking at, and I think it's pretty much agreed by everybody that it would be better for roll level security to not operate in quite the same way as the with check option. And the SQL standard doesn't specify anything about roll level security, and therefore we're free to do what changes we want there. And so the plan at the moment in probably gonna be committed here in the next couple of weeks is to move that check up in front of all the other check constraints and other things that are firing against that particular tuple for the view. The other thing about cascaded versus local that I'll mention, if you have views that are built on top of other views, you can say whether the with check option is applicable to just that particular view or whether that tuple has to be visible through all of the views that may be underneath. And to make sure that it comes back through all of them. That's what the distinction there between cascaded versus local is. That isn't really applicable for roll level security because roll level security operates at a table level. Those are implemented on relations and therefore there aren't things underneath them. All right, another way of achieving this filtering is this multi schema approach that I have seen in a lot of places. And it's neat as a capability, I did it myself once upon a time but it's got some serious drawbacks, right? One of the big issues that you run into with this idea is where you create a schema and that for every single users and all the tables and all the schemas are the exact same the first day. And then only the rows that that user gets visible supposed to see get inserted into their tables, right? The problem is that it's difficult to maintain consistency across all those sets of tables. You have to maintain this huge nightmare set of objects all throughout the system and it doesn't end up scaling very well, right? It becomes a real big problem for applications that are then using this approach to deal with, well, we're doing this rolling change over from this table definition to this table definition and the application has to deal with both or maybe three different versions of what the table definitions are inside of the system. Otherwise, you have to do everything wholesale all at once and that could lead to downtime or outages or inconvenience for the users, which is pretty painful. Another thing is that you may have the right dynamic SQL which it's not hard but it's just adding more complexity, it's not fun, right? Partitioning then also has to be matched up with the requirement for what's visible, right? So if you're gonna build a partitioning system which makes sense as tables get larger, well, you've already essentially partitioned your data, right? You've partitioned it by user with this approach and you only have the option to sub partition it beyond that, you can't partition it in a different way besides partitioning by user and that can be a problem as well. So let's talk about the actual, you know, row-level security that we have coming in 9.5. So the first thing is that every table that is going to be performing row-level security must be set to enable row-level security. It's a very simple alter table enable row-level security and now row-level security is enabled for that table, right? And therefore all access to that table from that point forward has to go through a policy of some kind. A couple of caveats on that. Caveat number one is that the super user and the owner of the table, both of whom can change and enable and disable this row-level security on the table, do not by default have their rows filtered. They still see all the data inside the table. So when you're running and using row-level security, you need to actually consider that while you're doing your testing, right? However, there is a default deny policy for all other users. If you're not the owner or a member of a role that is an ownership role on the table and you're not a super user, then if there are no policies on the table, you will not see any records in the table, right? Because of the default deny policy being applied. This also means that you can, so the other option on this is that you can disable ROS. But because it's enabling and disabling of row-level security on the table is independent from creating the actual policies on the table, that means that you could disable row-level security and still have policies on the table. Those policies will not be applied, right? Once the row-level security has been disabled for the table, policies can still exist, but they won't be used for anything. And then if you go and re-enable it, then suddenly any policies that exist on the table will be used, right? So one of the things that gets kind of interesting is how do you test this, right? If you're a table owner and you've just created a table and you're like, okay, so I've created this table, I've set this policy up, but I don't know whether it's actually doing anything or not. So the way we address that is that there's an option called setRowSecurity that was also added, right? It's a standard guck that you can have. And by default, it's on, right? Normal mode, policies are applied as appropriate. They're only applied if you are not the super user and you're not an owner of the table. Off means don't ever apply real-level security. This is useful for things like backups, right? In some instances, you may want to say, okay, I need to be able to backup the database, and that means I need to be able to see all the records in the data. So that's where the off option comes from. And in fact, off is actually set by default by PGDump, right? Because we're kind of thinking, if you're doing a PGDump, you probably want everything. There's an option to have row security set to on instead with PGDump. So if you actually want policies applied when you do your PGDump, you can get that functionality as well. Note that you may not always have privileges to disable real-level security. So that's what the error bit here is, right? If you set row security to off and you don't actually have the right to access that table without going through a policy because you're not a super user, you're not the table owner, and you don't have what's called the bypass RLS option that I'll get to in a minute, then it's gonna throw an error back at you instead. It's gonna say, sorry, I'm gonna error out instead of actually either just returning zero records or doing something else, because the expectation is that there's something in error in that case. There's a mistake that was made. The last thing is the force option, right? And this is where you can use it for testing. You can say set row security equals force, and then that row-level security gets applied even for the table owner and for super users. Now of course, remember, the table owner and the super user, you just go disable RLS if they wanted. So this is not intended to be used for a security measure. It's just intended to be used for testing, right? If you wanted to do testing, or maybe you wanna do a PG dump with all the data in your database as visible to that particular user doing the PG dump, you could set it to force even if you're running PG dump as a super user, for example. So that may be useful. So here's the create policy kind of command syntax, and I realize it's pretty complicated. I'm gonna hit a bunch of really kind of simple things and build it up as we go. Policy through table specific. They're applied for particular commands, right? So you can have a different policy for insert than you do for update or a different policy for delete. All right? They can also be for specific roles or for all roles. We use the notion of public to mean all roles, right? So if it's set to public, then the expectation, or the implementation is we apply it for everybody accessing the table. Again, excluding the table one of the super user, and unless raw security is at the force. I mentioned the default to deny policy already. The way we deal with the records going in to the database versus the records coming out of the database is with these two different clauses, right? There's a using clause, which is for rows that are visible, and then there's the with check clause for rows getting added. And that with check clause applies for both inserts and updates, right? It doesn't actually apply for deletes. Reason for that is because deleting is never adding records, right? It's not adding it or even changing a record. So if you have a policy on a table that says, okay, this is a delete policy, I use the using clause to say which rows are available to you to delete. I think that's one of the key things about one of the really good reasons why we have different policies based on command is that you can then allow them to view records that they are not allowed to delete out of the table, right? That one thing that can be very handy in certain environments, maybe there's a subset that they're allowed to delete, just not all of them. If no with check clause is specified, and in some instances it doesn't make sense to specify a with check. However, if one is not specified and it does make sense to have one for that particular type of policy, what we'll do is we'll simply use the using policy, right? So the policy that you said for using, which as I showed earlier on is current, my user equal current user, that will get set for both using and with check. So here's an example of that policy creation, and this is a very, very simple example, and the point here is to illustrate that, you know, we have a lot of defaults in here to keep it simple, right? It doesn't have to be super complex for users to work with policies if they wanna do relatively simple things and they can use a relatively simple command. So this particular policy applies for all types of commands, all roles, and it uses the same conditional set, my current user, for both any records that are being reported out from the table as well as any records that are going into the table. So I'm gonna keep that top thing there as it's the exact same, right? As it was on the prior slide, but what I'm gonna do here is I'm gonna start adding in the different options, right? These are all gonna mean exactly the same thing. I just wanna show that what the defaults end up being. So for this part, I'm gonna show that using and with check end up with the same conditional, right? Of course, you can specify them independently if you want, just like I'm doing here, but you could also have a different check. So moving on, we're now gonna add kind of that next level which is for all, right? This is the sub-command or the command type, right? Where we can say this is for select or for insert or for update. Now we're gonna add in to public. So it's for command type to the roles, right? Either public or a specific role or a set of roles, right? All of these mean the exact same thing. So if you wanted to create a policy that was specific to select queries, then this is what you would do, right? Create policy and then you say for select. Now what this does if there are no other policies on the table means that this user is only able to select records out where my user equal current user, right? They're not allowed to insert any records or update any records, right? Or delete any records because there are no policies for those different command types, therefore the default deny policy applies. So here's an example for insert. So for insert, only with check makes sense and so only with check is allowed, right? Because there's no using clause here because we're not looking, we're not worried about the visibility of records in the table when we're doing an insert, we're only worried about the contents of the tuple being added and therefore it only accepts a with check. This is update, update's one of the fun ones where it's actually the only case where we care about both sides in a single command type, right? So this is which rows are visible to you for update and then what are the results of that update, what are the contents of the results of that update allowed to go back in? So in this particular case, it's kind of interesting because we're saying for update only, you can only see rows that are yours for update. However, you can change that to be somebody else, right? And when you do that, that tuple will no longer be visible to you for update. You've essentially given it away, you've given it to another user in this particular scenario. Now you may want that, you may not want that, but the neat thing about this is that it's a capability that Postgres supports with this approach to real-level security. I talked a little bit about delete again, so I forgot to update that. So that should say for delete, sorry. So delete only, it only has the using policy makes sense for it, right, the using clause. Because again, delete is all about visibility of data. All right, altering the policy is pretty straightforward, while I was changing the policy definitions. So you can change pieces of the policy if you want. You don't have to change and redefine the whole thing from the get-go when you're altering a policy. You can say, okay, I wanna just change what roles are applied for this particular policy. I don't have to restate what the using or where the check clauses are when I'm doing that. I can just alter policy name on the table and then two and then list a set of roles. One thing you can't change is the command, right? The reason that you can't change the command is that then we're not, or you can't change the command by itself, I should say. And the reason for that is, you have to drop it and recreate it instead, is because the with check and the using clauses only apply sometimes. So it wouldn't really make sense to change a policy that was for select into a policy for insert, right? Because the select only the using clause applies and the insert only the with check clause applies and we don't know if that's the same thing or not. And so that's why we don't allow changing of the command for the policy. You can just drop it and recreate it. Policies do have names. So all policies have names and you can change the name. The policy name is specific to the table though, right? So you could have the same name of a policy across the entire system. And as long as you are only ever using that policy once per table, you won't run into any naming conflicts or anything like that, right? Policy names are specific to the table that they're applied to. So here's a couple of very simple examples. We can change the using clause for a policy. We can change the with check clause for a particular policy. Note that we don't have to specify the command here because again, you don't get to change the command and therefore we don't need to have that specified in the ultra policy statement. It would simply be redundant. One of the things you can do is you can change multiple things at once if you wanted to. You can change a set of roles and change the using and change the with check clause all in one statement if you want. Very similar to how altered table handles that. Of course, policies can also be dropped. But as noted and I've kind of hit on a couple of times, if you drop a policy and there ends up being no policies being applied for a particular user, then the default deny policy will kick in and the user will not see any records. As long as role level security is enabled on the relation, that default deny policy will always apply. So I wanted to show or talk a little bit about the distinction between the policy versus the grant system, right? So the policy system's all about rows, right? Whereas the grant system isn't really tied to rows so much, right? It's tied to maybe columns, definitely tables, right? And for a particular column, it's all or nothing for the values in that column, right? You either have select on that column or you don't have select on that column, right? Or maybe you have insert on the column, but you don't have delete or sorry not delete but update, right? Delete is one of those interesting ones that doesn't apply for column level privileges because delete only happens at a complete row level, right? So what ends up happening is a lot of times you use views to limit data or you use stored procedures, things like that to deal with those more complex cases and of course those are still available. But what the policy system does is it allows you to have that row level control without having to resort to using stored procedures or more complex methods to be able to get that result. So I have a number of examples here that I can talk through. I did want to kind of take a pause for a minute since I've been talking for half an hour straight. Anybody have any questions about kind of anything that we've gone over or things you're thinking about at the moment? All right, I can, okay, yeah. That's okay, you can't. The question was what happens if you name a row public? Yes, my recollection is that it is actually globally disallowed. Yeah, I believe it's globally disallowed. I'd have to go look. If you quote it, you put double quotes around it as an identifier, I think it's still disallowed. But if it's not, then you would have to use it that way whenever you reference it. But I'm pretty sure it's actually just globally disallowed. I went through the grammar not that long ago and my recollection is that it is just, you can't do that. There's a few very specific things like that. We try to avoid it, but there are a few cases where it makes sense because it has a special meaning. Any other questions before I kind of start going through the examples? Yeah, so the question was if you created a, if you had a roll R and you did a grant select or a grant update on a given table to R, and then you've also created a policy for R, right? Now, I wasn't quite sure if your question was what if you create a policy for a command that they don't have access to? So if you say grant, so here's a good example, right? So if you say grant delete on the table, however, in the create policy statement, you either don't create a policy for delete or you create a delete policy, then while they have that right on the table, the only records they can delete is either nothing because there's no policy and therefore there are no records visible to the delete to be deleted or they are only able to delete the records that the delete policy allows, right? Now, if you create a delete policy and you don't grant delete on the table, then it doesn't matter, right? Because the grant system will essentially be checked first, right? So the overall structure of what you're trying to do is checked through the grant system initially and it's only after it's gone through that that the visibility question comes up about the records or the with check option comes up about the tuples that are being inserted, right? If you don't have insert rights on the table, we're gonna blast you back with a permission denied error before we even get into thinking about policy questions. Okay, yes. So the interesting thing about this, the question was why isn't all of this stuff done in the application instead, right? And there are a lot of people out there that write web applications that run as super user and handle all the permissioning and everything at the application level and all the controls. And that's fine and dandy when you have one application touching the database. Soon as you have more than one application touching the database. Now, this second application also has to implement all of that and do it in the same way, right? In order to have consistency, right? By pushing all of that complexity and permissioning closer into the database, you basically avoid that problem completely, right? Because all of it's being handled at the database level instead. So there's obvious trade-offs there, right? Some of these things, Postgres does not scale well to millions of roles in the system. Absolutely true, right? There's no way to partition the role tables, for example, which you would tend to need to do if you wanted to create millions of them. However, with the role-level security approach, you don't always have to base it off of role, right? You could just have some custom guck that you set that is like the application user, right? And then have that guck be referenced in the policy definition against some column that has the user name or the user ID or what have you, right? So you don't have to use roles with policies to get role-level security coming out for your application users, right? And again, having all of that pushed down into the database makes it a lot simpler for the application and a lot less risky to have multiple applications accessing the same database. Does that answer the question? Okay, happy to discuss further, of course. So I just wanted to show a couple of examples here. So here's what you get when you do a backslash D in PSQL, PSQL is my favorite thing ever. Like, I love PSQL, so it's a fantastic interface. So this is what things look like when you do a backslash D. This is the policy that you'll get back from that. And then I always like to show off this table command. I don't know if many people are familiar with it, but it's essentially a select star from table, right? But you don't have to do all of that select star business. You just say table and then the table name and it just returns all the records. It's very cool. I like it. And it also accepts order by and limit too if you wanted to use those with table, which is actually an extension over and above the SQL standard where it's just table. But table is actually an SQL standard command. Kind of neat. Did I move forward? There we go. So this, you know, moving on from that example, what we do here, if we do a set role, you know, we did table before as a super user and we do a set role to a particular role that has a policy applied. And then we do table again. Together it's just a select star and now we see only the records that are applicable to that user. Again, if no with check clause is used, then we're gonna use the using clause. And here I'm trying to insert data into this table for some other user that I am not, right? And because of that, I'm gonna get this with check option failure. The wording in this error message that I think is actually gonna change here pretty quickly. Again, this isn't in a released version. So we don't have any concerns about changing those kinds of minutia at this point in the release cycle. Once nine fives released, we won't be changing that for the nine fives cycle at least, the nine fives set of releases, nine five dot zero dot one dot two. So if you're interested in playing with this or looking at it, you'll have to go to the Postgres master or git head or whatever you wanna call it, repo to get all of these commands. So here's just another example of where, you know, if I change that policy now to say with check is true, meaning I can do anything, now I can insert data for another user even though I won't be able to see it after it's been inserted. This can get even more interesting when you consider what I'll call external policy systems, right? Because essentially these expressions in the using and with check clauses can be anything that can go into a where clause. That includes functions, right? An interesting function might be a call out to SE Linux to say I wanna check whether the SE Linux policy system which is being managed by the Linux kernel allows this particular piece of data to be visible to the user, right? Now a lot of times what you'll do there is you'll pass in some kind of tag column, right? If you have a, they call them context, right? In SE Linux. So what you'll have is a context column that'll define what the context of that particular row is or was when it was inserted and using something like this check SE Linux call we could pass in a column called the context column and then have that checked back out, right? By SE Linux, whenever we're trying to select data out and what SE Linux will do or could do in any case is say take that piece of data, that context on the row, take the context that it knows from its connection, right, cause SE Linux actually on a per connection basis maintains a context and say, okay, do these two match up, right? Or is this user allowed to see that context, right? And based off of that, it'll either return records or not return records. The point is that it's very, very expressive, right? You can do a lot of different things with this capability by using functions. So another quick example is a fun one I like to do which is the Unix password file, right? So these are all the standard columns that you'll get inside of a password file and I'm gonna have three different roles. I like the Alice Bob example from Kerberos, it's been around forever and it's good. So we have an administrator role, note that that's not a super user, which is important. And then we have two normal roles, all right? Now we're gonna say, okay, we're gonna populate the data, the table with a few bits of data, right? We're gonna populate it with three records, one record for each one of the users that we're talking about and then we're gonna make sure we enable role-level security on it, okay? Nothing too special here, it's just a Unix password file. Now what we're gonna do is create a few policies that go on this, okay? We're gonna say an admin can see all the rows, right? And it can also add any rows into the table. So it's all just true, right? That create policy command could actually be simplified to just say using true. This particular case I like to say both because it kind of makes it more explicit in my mind when I'm crafting it. In Postgres it doesn't matter. And I say, okay, normal users can see all the rows, right? But I don't want them to be able to update all of the rows. This is the exact same way as the Unix, regular Unix password file. You can cat at cpasswd and see everything that's in there because there's nothing particularly private inside of that file. But you can't modify things except your own. So I'm gonna create a policy that says, okay, for update, I say using, right? So I'm defining what records are visible to the update command that the user's running against this table. And that's where I say current user equal uname, right? Uname being the field inside of the table. And then I'm gonna say with check, okay? So now I'm gonna say when that record comes back from that update statement, I'm gonna check a few things. First, I'm gonna make sure they didn't try to change that username to somebody else's username. Because that could be a problem, right? Because then we could end up with duplicates and other things and that'd be bad. And I'm further gonna say, okay, if they do change their shell, they have to change it to one of these values. Now note that I'm specifying this list of values inside of an in clause. It doesn't have to do that. You could have it in a separate table that has a query against it as a sub select. That'll work just fine. Just like it doesn't wear clauses. Further, I'm only applying this policy for users, right? And so I'm saying, okay, users can only change it to this set of shells, but like an administrator could change it to any shell that they want, right? They could change it to dev zero or dev null, for example, if they wanted to prevent that user from being able to log in. That's something people do sometimes. So the point here is that it's not the same as a check constraint, right? A check constraint, yes, you can limit a check constraint and require that all columns or all entries in the table completely are in that set, but that would then preclude the administrator from being able to make a modification to this table to set it to dev null. It was kind of an interesting play out here with that. I don't know if we're ever gonna kind of get a more granular notion of check constraints that are at the table level that are applied based on roles or something like that. Might be an interesting idea, but at least if you wanna do this, you can do this with policies today, and that may be sufficient, yes. So policies can't conflict with each other, right? Pretty simply. The reason for that is that policies are always adding information, okay? So policy says what records are visible. So if there's two different policies on the table and policy A says that records one to 100 are visible and policy B says records 101 to 200 are visible, if both of those policies are applicable to your connection or your role, you'll be able to see records from one to 200, right? Because you're granting access to rows, right? And grants, in general, in the Postgres system, as well as with policies, are always about adding more privileges. They're not about taking it away. So policies, I mentioned that earlier, but whenever policies come into play, they are always orred together. So all the conditionals get orred in this big or tree, and that's where the performance problem can happen, is that big or trees can end up being not performant. So if you have lots of policies that apply for a particular user, it can end up being, may preclude us from being able to use indexes and do other things in a performant way, with maybe a reason why you wanna create policies on a per-role basis. So in this particular case, I'm just showing how we can grant privileges on this table to do, like, you know, the admin can basically do anything, select, interrupt, delete. However, users can only select, and there are only a certain set of fields that they're allowed to update. Right, so this is just showing how we can combine policies and the grant system to work together. So to show an example here, I can set the role to admin, and I can, boom, I see all records, right? Next, I'm gonna set the role to Alice. Alice is gonna say, well, I wanna see everything, and you get permission to night error, because Alice doesn't have select rights on all of the columns. Okay, fine, so Alice selects out a subset of columns. Great, fine, she gets to see those, no problem. All right, now we're gonna try to update and change the username. Oh, can't do that, right? Doesn't have update privileges on the username column. Right, so I had the with check clause in there as well to make sure that the record coming back in is set, but as long as Alice is not able to change that column, it ends up being a little bit redundant. Personally, I think it's a good thing to have anyway, because security is like an onion, right? You wanna have lots of layers. Now Alice is allowed to update the past file and set the real name to something else. Now this is interesting, right? Update one, we know there's more than one table or more than one row, but there's no conditional on this particular query. Well, that's because the policy is being applied, right? So the using policy of the update policy or the using expression of the update policy says that Alice can only see her record to update. Therefore, even if she wanted to add some kind of condition or where your name equals admin on that, all that's gonna do is further filter the records. And what that means is that you'll get this update zero and say, well, there weren't any records visible to be updated, so it's not relevant, right? We then have update past WD with set shell and we say, well, that doesn't pass the set of shells that we said you were allowed to update to and then you don't have permission to delete anything from the password file. You don't have to delete privileges at a grant level for the file at all, so you can't delete any records. And further, if you wanted to do an insert of a specific column, you're not allowed to insert new records into that table either again, that's a grant level privilege. However, Alice can change her own password using update past WD. I mentioned this before, but I wanna hit it on again. Leakproof functions is where a lot of this comes into play when it comes to performance, right? Only leakproof functions are allowed to be pushed down and that means that they are allowed to be executed prior to other qualifications either in the policy or on the, you know, or that the view, if there's a view over top of this table as well, or that the user's specified, all right? What that takes them to allow you to do is make things faster because it allows us to use indexes. Note that only super users can mark functions as leakproof and generally I would recommend that you not. It's very dangerous to mark things as leakproof because of their ability to have that be pushed down security barrier views and pushed down through world-level security policies because we took a lot of time to think about whether something is really leakproof or not. Is it ever gonna throw an error that comes back and says what the value in that column was, right? That's not leakproof, right? Even if it's in an error mode, it doesn't matter because that call can get pushed down to a level that you're not supposed to see any data had and therefore if it throws an error, it can be throwing an error about a record that you're not supposed to be seeing. That's a very important thing to think about and generally I would really recommend don't mess with it. A couple of quick things. So here's a quick explain, right? Just a very simple case, explain table. You can see how the filter gets added here. Further, when there's an index available, we can use that index for that. And then that tends to work out just fine for that particular query. So you can see that because we rewrote the query prior to actually running it, we're able to, prior to planning it or optimizing it, we're able to do this index usage. And then I just have this last slide which just is a more complex case where we have two different, two different conditionals that are being applied and you can see that in this particular case, we're able to do the index scan using the MyUserEqualCurrentUserIndexCondition and then we have the filter applied over top with the regular expression which is that little delay symbol. So I may have time for like maybe one question but I know we have lunch coming up so I wouldn't push your lock. You can always find me at lunch. Anybody have a last question or two? Fantastic, thank you very much.