Row Level Security
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 29 | |
| Author | ||
| Contributors | ||
| License | CC Attribution - ShareAlike 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this | |
| Identifiers | 10.5446/19141 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language | ||
| Production Place | Ottawa, Canada |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
PGCon 201518 / 29
1
2
3
6
10
11
12
13
14
15
17
18
21
22
23
25
26
29
00:00
Limit (category theory)Table (information)Online chatConsistencyRelational databasePhysical systemSet (mathematics)Internet service providerCommodore VIC-20Computer configurationIndependence (probability theory)Digital filterHyperbolic functionDensity of statesAsynchronous Transfer ModeForceEuler anglesMathematical singularityRegulärer Ausdruck <Textverarbeitung>ExpressionPort scannerDrop (liquid)MathematicsDrum memoryLevel (video gaming)Urinary bladderSystem programmingFunction (mathematics)Extension (kinesiology)Real numberGastropod shellSystem administratorKey (cryptography)Computer filePasswordMetropolitan area networkBinary fileInsertion lossData Encryption StandardNormal (geometry)Finitary relationTotal S.A.Data acquisitionPrice indexDemosceneGamma functionHand fanDampingMathematical optimizationInheritance (object-oriented programming)Default (computer science)CASE <Informatik>Electronic mailing listBitObject (grammar)Functional (mathematics)Computer configurationSystem callMultiplication signError messageOcean currentRight angleExistenceQuery languageDifferent (Kate Ryan album)View (database)Proxy serverVapor barrierCore dumpExpected valueFocus (optics)ResultantInsertion lossLine (geometry)Subject indexingData storage deviceAbsolute valueSequencePoint (geometry)Exception handlingTable (information)DatabaseLibrary catalog1 (number)MultilaterationNP-hardCondition numberRegular graphComplex (psychology)Basis <Mathematik>Standard deviationCovering spaceImplementationStatement (computer science)NamespaceSelectivity (electronic)Row (database)Revision controlPhysical systemEqualiser (mathematics)Descriptive statisticsCodeSet (mathematics)Module (mathematics)AliasingSystem administratorGastropod shellPasswordReal numberInformation securityHash functionPort scannerQueue (abstract data type)Operator (mathematics)MassRoutingEndliche ModelltheorieMereologyEvent horizonDatabase transactionPlanningConsistencyMathematicsType theoryLatent heatMechanism designKey (cryptography)Perspective (visual)Normal (geometry)Set (mathematics)Drop (liquid)Forcing (mathematics)AntiderivativeExpressionMetropolitan area networkBit rateFresnel integralWaveQuantum stateBackupComputer fileCausalityMoment (mathematics)Maxima and minimaOrder (biology)Tape driveSolomon (pianist)Group actionExecution unitAreaAdditionSequelVirtual machineGoodness of fitMusical ensembleNumberStudent's t-testRule of inferenceFactory (trading post)Message passingProduct (business)Game controllerArmMetric systemComplete metric spaceUniverse (mathematics)Reverse engineeringGraph (mathematics)Volume (thermodynamics)Context awarenessCommitment schemeQuicksortBefehlsprozessorPrimitive (album)Casting (performing arts)FreezingSoftware testingArithmetic meanWordMathematical analysisLogic gateDirectory serviceProcess (computing)Internet service providerForestProof theoryVideo gameWeightIntegerTheoryGraph (mathematics)Address spaceFilm editingBuildingReading (process)SoftwareCellular automatonComputer animation
Transcript: English(auto-generated)
00:00
with 9.5 and things like Create Policy. So the idea with ROS is to have a view that's different for different users, views of the data that are different. Now, you can think of this as a filtering mechanism. That's one approach. But if you filter things in the right way, you can actually end up having two different sets
00:21
of data for the different users. You could have a set of data that says, so-and-so is a really nice guy. And you can have a set of data that says, he's a really bad guy. And depending on who the person is that's logged in at the time, they may get one or they may get the other. There's all kinds of interesting use cases around that. So the whole idea here is that you're
00:42
filtering and limiting, but you're providing a different view for each individual that is connected. And we're going to do all of this while maintaining the relational consistency and all the normalization and everything that you normally get. You still get all of your primary keys. You still get all of your indexes, your foreign key
01:01
relationships, all of that we still have while we have this RLS capability. The other really important thing from a security perspective was to make sure that we always require policies to be applied. So this is, you know, it is and it isn't, right, in the way it works inside of Postgres.
01:20
And I'll get to that a little bit later. But you want this default deny approach. You want to have and make sure that policies are actually getting applied. Because otherwise, you know, you run the risk of people being able to circumvent the system and we don't want that. So the first way that people had done this before was with security barrier views. Maybe not the first, but it's certainly a very common use case.
01:40
A lot of people have already done this. They've implemented it this way. And the way they do that is to build a table or build a view over whatever table it is. Postgres had some problems in older versions prior to having a security barrier as an option on a view because what could happen is that a given conditional could end up getting pushed down below the quals that were in the view, right?
02:05
So I could have my really, really cheap function that gets called and that happens to be called prior to any of the quals inside of the view get called and that's really bad because that little function
02:21
might just spit the data back out, right, and you get that it's being passed. And if that's happening first, that's a problem. Security barrier views fix that, right? With security barrier views, what you can do is you can say, okay, these quals must be applied prior to any functions being run. We actually changed that a little bit on 9.5,
02:41
which I think is really interesting, but what we did was, it was work done by Dean Rashid, what he did was he was saying, okay, we're gonna change this so that functions can actually be pushed down if they don't receive any data from the view, from the table that's being referenced, right? So this could be from the table that's in the view
03:01
or from the table that's in the RLS policy, right, that has the RLS policy applied to it because if the function isn't getting any data from the table, then it's okay for it to be pushed down to the same level and be used in optimizations. Where this becomes really, really handy is when you're doing filtering on things like current time or date or any date, right,
03:21
because previously you couldn't push that down because it was a qual with a function that was not considered leak-proof, and I'll talk about leak-proof a little bit more later but the whole idea here is that security barrier views were one way of getting the same kind of functionality and RLS is actually built on top of security barrier views. So we talked a lot about the kind of select side
03:41
of getting data out of the security barrier view. One of the things that we added in 9.4 was this with check option, right? So the with check option allows you to have views that are automatically updatable but, well, we already have views that are automatically updatable but the with check option says only allow a record to be added through this view
04:04
either as an insert or an update if the result of that row could be visible through that view. Everybody kinda understand that, right? This means that you can't insert data through the view that you don't get to see through the view, right?
04:21
It's actually a SQL standard capability and we've implemented it in the SQL standard way, right? And what we did was we actually combined this with, this was one of the other basis for the implementation of RLS because with RLS we needed the same thing, right? We needed a way to say, okay, we need to have policies that say, okay,
04:41
you cannot insert data into this table that you are not allowed to see, right? So it's the exact same kind of capability and what we did was build on top of that with RLS. Cascaded versus local is essentially you can have sets of views that are on top of each other, right? And with check cascaded basically says
05:01
that you have to be able to see that data through all of those views. Whereas with local you can say, okay, maybe this other view makes it so that you can't see it but that's okay. As long as the top level view says you can then we're gonna go ahead and let it go through. Again, this is all SQL standard stuff. This doesn't really apply on the RLS side of things
05:21
because we're only dealing with one table in RLS world that we're dealing at a time anyway and so it doesn't entirely apply but the way that you get this capability is just simply add the with check option onto your simple automatically updatable view. So one of the other things, just let everybody know if you have any questions,
05:41
please, I am more than happy to take questions or comments during the talk just in general. So please shut out if you have anything to say. All right, the next is the multi-schema approach. A lot of people have used this approach and I'm sure a lot of people have hated it because it's ridiculous. You have to have all of these independent objects in all of these different schemas
06:00
and they all have to be the same. It makes it very difficult to maintain consistency across all of them. Updating a table definition is just a nightmare and so there's a lot of problems with this and you also end up having to put like massive views or inheritance across all of these because maybe you want to be able to do reporting across. So I've seen a lot of cases where people are like,
06:22
well, I'm gonna have a table per customer or a schema per customer. And that just, it gets to be terrible. So my two senses don't do that but now RLS provides a way of getting the same kind of functionality without having to go down that route which is the idea behind it.
06:41
All right, so the next thing to discuss is, let's talk about what we have in Postgres now in 9.5. So the first big thing, and I want to point this out first, I feel like a lot of people would jump right in to creating policies and whatnot but it's really important to realize you actually have to enable, you have to turn on RLS on a per table basis right now.
07:02
Ways that you can address this in a security conscious system might be to have event triggers which are now in 9.5 and this is something I'm planning on looking at, require that every table have RLS enabled when at the end of the transaction or something along those lines. That's the kind of thing that event triggers are gonna allow us to do now that we have them.
07:23
So those plus D parts which we might have to have are things that we would need to do but the whole point here is you have to enable RLS for every table that you want to have real level filtering applied to. And what that does for starters is this is all access to that table must now go through a policy, all right?
07:42
So this is getting back to that default deny approach, right? And if there are no policies on the table, we create one, right? We don't store it anywhere. We create it in line during the actual query rewriting and we just say false, right, essentially, right?
08:00
So there's just an automatic default deny policy. One of the things that's really key to understand here though is that table owners who are the ones who control the policies being applied to the table in Postgres because table owners control everything with objects in the database today. Believe me, I wanna change that someday too. But right now, they control the policies and therefore they are automatically excluded
08:22
from real level security even when it's turned on, right? So if you alter table on and you're still and you're the owner and then you clear the table, you are gonna see the rows, right? This also means that things like pgdump work, right? It's really important that pgdump works for regular users as well as for super users.
08:41
That's the other thing. Real level security is not applied at all for super users by default, right? I'm gonna talk a little bit about ways you can make it happen but just realize that that's the case. So the other thing is that when you disable RLS for the table, the policies are no longer used but they're still in place, right?
09:00
The policies still exist on the table. So you can just disable and re-enable RLS on a given table whenever you want. Of course, realize that if you disable policies, you know, disable RLS on the table, anybody querying that table is then gonna be able to see stuff in it, right? So just be aware of that. That's, you know, or at least if you do that and commit, right, then other people will see that.
09:22
So it's not like a per session thing. Instead, what we have is something called row security. So row security is a new guck that got added along with RLS which allows you to control this behavior principally around table owners and super users. So what you can do is you can set row security to on
09:42
which is normal and we do the normal RLS stuff that I've been talking to. You can set it to off which says never ever apply RLS. However, what that means is that you're gonna get an error back instead, right? If you don't have rights to bypass RLS on that table, you're gonna get an error thrown back at you
10:01
because we don't want you to just be able to say I'm gonna set row security off and then no policies apply to me, I can see all the data. No, that doesn't work, right? So this is something also that pgdump does by default. Right, pgdump by default sets it to off and the reason for that is because when, and our expectation is that when you're using pgdump,
10:22
you want to be able to see all the data, right? You're doing a backup, you shouldn't, you know, you probably wanna be able to see all the data. Now that said, there is an option to say pgdump, you know, set row security to on instead so that you can get a dump of just what is visible to you in the system. The last option, yes?
10:43
Anyone can change this option. Yes, because if it's set to off and you try to run a query where RLS would have been applied and you are not either the table owner, the super user, or you have the bypass RLS, guck, which I'll talk about, then we throw an error.
11:00
You don't get any data. You don't get, we just throw an error. If you ran pgdump with this set, well, by default, yeah. Yeah, if you run pgdump by default, or with, yeah, exactly, it will throw an error, yes. Yeah, if it comes across a table
11:20
that it can't read the data in because of the policy, yeah, it'll throw an error today, which is why there's an option to say, go ahead and allow it to do that and allow me to get filtered results. But this was principally because a lot of people felt that pgdump's primary focus was doing backups. I'm kind of, I realize that, yeah, that's certainly a huge use case for it.
11:40
I actually think it's more generalized than that, but whatever, that's fine. The other option is force. So what force does with ROS is it says, even if I'm the table owner or I'm the super user, I can force role level security to be enabled for me so that I can see what happens, right? This is kind of a debugging kind of tool, right? I want to force ROS to be enabled
12:02
when it would apply to me, and I'll talk a little bit because different policies can be applied for different people, I'll talk about that in a minute. But the whole point here is that I want to be able to do kind of like debugging and whatnot, so I can set it to force, and then policies get applied for me just as if I wasn't the table owner or I wasn't the super user.
12:21
Any other questions about ROS security, guck? All right. So let's get into actually talking about creating policies. Okay, so policies are table specific, right? And their namespace is also per table, right? And the way that it works is that you create a policy
12:41
and you have a whole bunch of different options for creating that policy, okay? The different options that are available here are four, and then we have a whole list of commands here, right? So this says that for a particular kind of command, a particular kind of option, like a select or an insert, you know, we can apply this policy only in that case, right?
13:03
So we don't all, you know, if you use all, then it'll apply for all cases, okay? And we'll talk a little bit more about why that matters, but to role name says I want this policy to be for this role, right? Default is public, but you can set it to a specific role name, all right?
13:21
And then we have using and with check expressions, okay? The using expression is the expression that we use to filter rows, right? This is kind of your notion of what's visible, okay, to this particular, through this particular policy, okay? With check is just like it is for security barrier views.
13:43
The idea of with check is that this controls what's allowed to go into the table, right? So you have to pass the with check option to have this data be allowed to be added to the table, right, and that's true for either inserts or updates. The reason that we have both of these
14:02
is that for some of these commands, for actually for most of them, you only get one or the other, right? So select only takes a using expression, okay? Of course, if you use all, then you can specify both because it'll just do what's right. However, for insert, only with check applies, right?
14:22
It only makes sense to have a with check policy for an insert. Update, however, gets both, right? Because update, the using piece is what rows is that update allowed to operate on that exists in the table today, and with check is what rows are allowed to go back in
14:42
as the result of the update, right? Yes, yes, yes. So this expression is a generalized expression and works basically the same way a where clause does, and that under the covers is what we actually just end up doing.
15:01
It would just shove it into the where clause. So you can put pretty much anything that you can put into a where clause there. Now, of course, what that means is you can't put like aggregates in there. You can do a subquery, right, and get the results of that and stick it in there, but you cannot use an aggregate function directly inside of a policy. Yes, sir.
15:29
So, okay, yeah, yeah, yeah. Any columns that are used in here are expected to be references to the table. They are references to the table. So you can just say, you know, using, I have some examples that'll probably help,
15:42
but the short answer is, I think, yes. So we'll get to that in just a minute. Let me make sure I covered everything else on this. Yeah, I think I did, okay. So here's an example policy, right, and what I'm gonna do is I'm gonna use the same policy implement, you know, the same effective policy for a whole bunch of different ways of creating policies, right?
16:03
So I'm gonna start very simple and get more and more complex. So this is a very simple policy, right? We're creating a policy named p1 on a table named mytable. Now, again, p1 is a name that's inside of the table's actual namespace for policies
16:21
on that table. What this means is that you can have the same name policy for a whole slew of tables, right, which makes things a little bit more convenient for scripting and whatnot, right? The next piece is this using clause, right? So using myuser, so myuser, in this case, is a column inside of the table, right? I think that answers your question about the alias.
16:48
You just reference the columns like you would otherwise. I mean, you might, that's an interesting question about if there is a risk there that you can't do it because of visibility concerns.
17:06
Yeah, that's probably a good idea, yeah. Well, okay, I think I get what you're saying. I mean, what you can do is you can say myuser equal and then have a subquery here. Yeah, yeah, that works, right?
17:21
The issue, though, is that if you wanted to reference this column inside the subquery where you're doing a join to some table where you're doing a lookup in some other table that has the same column name, it might end up being ambiguous. I'd have to look. If it ends, yeah, if it does, you know, Postgres will spit it back out at you as an error, right, which is obviously not terribly helpful.
17:43
It might be a way of dealing with that, but it's a good thought that I should probably, that might be something I can get in for 9.5 is to make it so that we can have an alias to this, like current dot whatever. But then, of course, we gotta look for, you know, what if you have a table called current? So I'll have to think about that because we wouldn't want it to break if you wanted to actually use a table called current.
18:07
Actually, that might just work. You know what, I'd have to look. Yeah, it might, hmm? You can alias the insert table? Okay.
18:22
Yeah, actually, you know what, my table, actually, I think that might just work already. I'd have to look. I can't remember offhand, sorry. It's been a little while. Right, right, right, yeah. No, you could do something like that, yeah. But that's a good point. We could just use this table, man. You know what, I think it actually probably would just work. I'd have to look.
18:40
I'd have to go back and look. Sorry, I don't have an answer for you right now about that. It's a great question, though. I've given this talk like four times. It's the first person, the first time I've ever had that question come up. That's fantastic. Anyway, so moving on beyond this simple one, right? So this, again, is the same policy that we had before. Now I'm adding more things here. So these two are equivalent, right? They're exactly the same.
19:01
When I specify using, if there is not already, or if you haven't specified a with check clause, I will just, we take the using clause and apply it for both sides, right? Just kind of like a with check is done for the view by default, right? Same kind of thing. And so these two end up being exactly equivalent. So moving on, now I've added this for all here, right?
19:24
So all is the default, all types of commands. But you can change that to be select, insert, update, delete, you know, whatever you want to do, right? Now I'm saying to public. So to, you know, again, this is for specific roles. By default, it goes to public, which means all roles.
19:42
One of the big reasons we did that, we added the ability to specify roles was because of performance considerations, right? You may want to be able to have a bunch of different policies on a table that are for specific roles. And to have to do that with just the policy by itself would mean that you'd have to do extra lookups and other work.
20:01
And so it seemed like a common enough case that we wanted to add explicit syntax and capability for it. So that's what it means now. So moving on, we're now gonna do specific policies, right? For specific commands in particular. So this is a select policy. And again, this is a case where you can only say using,
20:20
you can't say with check. But this means that it's only gonna apply for queries that are doing selects against the table, right? So essentially, queries that are getting data out through a select command from the table have this policy applied to them. So insert, again, only works with with check. You can see that, you know, this is how you would specify a policy
20:41
for an insert-based approach. And then this is the one case where you can have update policies, right? So this is kind of an interesting use case. And a lot of people, we've been kind of mulling over this question about if it makes sense to have these kinds of capabilities. So this one allows you to give away rows, right?
21:04
So you can only operate on your own rows, right? Through the using clause. But when you have with check equals true, that means that I can update that to have some other name inside of my user as the result of the update. And so I can give my rows to someone else, right?
21:23
I actually think that that is a pretty viable, you know, pretty reasonable use case in a lot of scenarios. Maybe you're in a hospital, right? And doctors are only allowed to see their own stuff, but they can give stuff to other people, right? Other doctors, you know, you'd probably have some other policies and other restrictions around it beyond just this simple one. But the idea stands, right?
21:41
Then you could also be very interesting for queuing systems, right? You're passing things between different queues. You could do that with ROS, yes. Old or new annotation?
22:01
No, I don't, I don't think so. The using, well, basically no, but. But using is only gonna be for rows that exist in the table. And with check is only gonna be for rows that are being added. So you kind of implicitly have that distinction.
22:20
But you cannot, you can't have a with check policy defined against what the old rows in the table were, if that's what you're asking. But I don't think, I mean, that's what using is for. You filter those rows if you don't want them to be able to do it. Now the interesting thing here is that you can do this in reverse, too, right? You could have using be true
22:41
and then have some clause on with check. Now that is kind of a more interesting question of is that useful? I have a hard time seeing a use case for being allowed to update records which you don't have access to, right? These would be blind updates. Now technically speaking,
23:01
we allow that through the grant system today, right? The grant system, you can grant update without granting select on a table. However, because conditionals, you know, inside of a where clause, if you're referencing any of the columns in the table, if you don't have access to those columns, you can't use that conditional. So the only updates you can do through the grant system today,
23:21
if all you have is update, is the whole table, right? I mean, that's just how it works today. So it's kind of an interesting twist, if you will. So there's still very much an open question about whether it's a useful use case, but for now it's there.
23:44
Well, it does. However, you'd have to configure it that way to have that problem, right? So this is a, it's really a question about whether the flexibility is useful to have. You know, right now we have the flexibility that you can put anything in these things. What we could potentially have done instead was say,
24:02
here's a select policy. You only get to have one using policy for all operations on that table, right? But I actually kind of like the flexibility because I think that that would, you know, then we'd probably want to do the same thing on with check, or if we didn't do the same thing on with check, then that would be weird, right? And so I actually feel like this is a very,
24:22
it's very simple and easy to explain, which I think is, you know, is perhaps good enough reason just to keep it the way it is now. Absolutely, I think it's possible. Maybe you're, in your queuing system, you're allowed to acquire rows from another queue, right?
24:43
And then make them visible to yourself to operate on. Right, maybe that makes them invisible to the other guy. You know, you could do something like that, rather than, it's more like a pull model instead of a push model, right? I could see somebody building a system like that, potentially using these interesting capabilities.
25:05
All right, moving on. So here's a delete policy, pretty standard stock. So actually, yeah, so let me touch on two things, right? So the delete policy here only has a using clause because you only are looking at rows
25:21
that exist in the table. Well, you're not adding any rows back, right? The with check is not about modifying existing stuff really, it's about what rows are allowed to go back into the table. That's why delete is a using clause. What this also points out though, is that update takes a using clause, and that shows, that allows what the visibility is for the rows that you're updating.
25:41
Right, and that allows visibility for the rows that are in the table to the update command. So some of you may realize that delete and update and insert all have returning clauses, right? So if you did set up a system, for whatever reason,
26:01
where you are allowed to update rows that you're not allowed to see, right, through the select policy, you can still see them because you can have a returning clause that returns those rows back to you. So again, this might actually be a good thing, might be a bad thing, it's definitely a thing, and it's something important to understand, right? These using policies are what is this command
26:22
allowed to operate on? And anything that that command is allowed to operate on is also something that that command could potentially return back to the user, right, through a returning clause. So if you had a delete policy that said that they were allowed to delete rows that they can't see through the select policy,
26:41
they could use a returning clause and still get them back. I don't really see too much of an issue with this, but I'm certainly curious if anybody has any feeling about it one way or the other. So certainly something that we can consider, but it's a thing. Yes, yeah, absolutely.
27:17
Yes, but they would actually be able to see how many rows got deleted, right?
27:22
And if only one row got deleted because they're using a primary key, then they would know, based on the where clause, that that was there, you know what I mean? You can find out all kinds of wacky ways to deal with that, to have that kind of a problem, but personally, blind updates and blind deletes are not things that, I think if you create your policies
27:41
that way, you either did it for a really good reason and you understand the results, or you screwed up. You know, which I think is far more likely. Was there another comment? Sorry, a whole bunch, yeah.
28:25
I think if I understood the comment correctly, what you were saying is that if you needed to be able to do something like that, you could go use one of the other approaches. Yeah, okay, all right, fair enough. Yes, if you're allowed to, yeah.
28:48
That's why we have column-based permissions. I implemented that before. So you can just, you can, on a per column basis, deny access, right, and that's completely independent and will still work just fine, but it's completely independent of our OS.
29:08
Actually, you need to have permission to see that key. Okay, well, all the columns. You might have permission to see, right? Yes. So you can see all the columns of the rows
29:21
that you're allowed to see, some column, but I can return all the columns when I do that, so therefore I can see data that you never wanted me to see. Right, if you set your policies up, Ron. I mean, I think the biggest question there is, you know, blind deletes, are they a thing
29:42
that we should be trying to encourage people to use, and if so, should we try to put in protections to protect them from having that happen? And I'm like, eh, I really, you know, if you want to do a blind delete, if you want to allow that through your policies, that's your prerogative, in my opinion, but don't come crying when the returning clause works
30:00
the way it's supposed to. Yes, yeah. That's kind of an important consideration. Yeah, absolutely. You really have to go out of your way to do that. Yeah, yeah, absolutely. It's not trivial to make that happen, except in stupid simple cases where you're just doing true, but then, you know, it can get complex, 9-6.
30:37
I mean, but yeah, no, I 100% agree that we need auditing policies and other things,
30:42
and the other half of this is actually something I'm gonna reference at the very end of my talk, which is SELinux support, right? If you go to SELinux support, then you can have all very straightforward policies across all of your tables, and let SELinux handle the hard question of who's allowed to actually get access to what,
31:01
right, through labels, right? And it actually works already. Yeah, I hope we have it done, but not in 9-5, but we'll be talking about that later. Anyway, all right, well, I'm gonna move, yeah, last thing before I move on, but yeah.
31:23
That's correct. Yeah, yeah, yeah, there was a lot of discussion about it. I mean, this is how we ended up. Yeah, there was a lot of discussion about it. You know, it's not what we have today.
31:41
I'm not sure I wanna go back to that discussion. What I think I'd rather do is have something like default policies that get applied, they get created automatically on tables as you create them, you know. The problem with it, though, is that, you know, different tables have different columns.
32:02
For the trigger function, when you actually go and define the trigger, that's on a per table basis. Yeah, yeah, yeah, yeah, I mean, that's, yeah, so.
32:27
But you can do the same thing here. You can have a function, no, no, no, no. You can have a function, and you call a function from inside of the policy definition. So all of your policy definitions are very simple, like creating a trigger, and you have all the complexity
32:42
in the function. It's exactly how the SELinux stuff works. We have a function call inside of the policy. Yep, all right, moving on. So alter policy, relatively straightforward, right? So the alter policy allows you to change the definition of a policy, big surprise. So here are the options for alter policy,
33:01
pretty straightforward stuff. You have the ability to rename them. Obviously, you always have to specify the table name because the objects are, you know, the policies are on a per table basis, not that interesting. One of the things that you can't do is you can't change the command of a policy, right? And that's because of the whole update with check, what's allowed to be applied to what stuff, right?
33:22
So you can't change, that's why you don't see command in here as an option. Obviously, you can simply drop the policy and create it again if you really want that policy to be, you know, the P1 policy to be for updates instead of inserts. You just have to drop it and recreate it. It's really, it's pretty straightforward, I suppose.
33:44
Yes, setting it, yeah, setting a policy, setting the using it with check policies to true would effectively be the same as dropping it, except that you probably wanna actually drop it. Yes, you can do that too. Here are some examples of alter policy. Again, they're pretty straightforward.
34:02
You can change the using clause or the with check clause or, you know, whatever. I mean, they're pretty straightforward. This is for roles, right? You can change the set of roles that are, you know, a certain policy applies to, and you can have a list of roles too. I probably forgot to mention that earlier. When you create policies, you can list multiple roles
34:21
for that policy to be applied to. Just like any other DDL in Postgres is transactional, yeah. Okay, not everything, but it's not a vacuum, right? All right, drop policy. Obviously, remember that if you end up dropping all of the policies off of a table, we will implement that default deny policy, right?
34:43
So, you know, dropping all the policies off a table is not the way to get, to see all the rows in the table, right? The way to see all the rows in the table is to turn RLS off for the table. But this is how you drop a policy, and we do have the if exists support. We don't really have a, yeah,
35:02
create or replace kind of thing, but we do have drop if exists. All right, moving on. I got a couple slides here, and I think we've talked through a lot of the different things here, but I just wanted to describe a little bit about how the grant system and the policy system are somewhat different, right?
35:20
So, grant is all about table-level actions, and it's also all checks that happen at the beginning of a query, right? The beginning of a query execution is where we figure out, okay, are you allowed to actually run this query at all, right? And the answer is yes or no, right? You either can see every, you either can run the whole query to completion,
35:40
or you can't see diddly-squat, right? So, traditionally, that's why we've gone to using views to limit data, because that way, you can actually have a conditional on it, right? So, what policy gives you is this table-level row, sorry, row-level capability, right? And it simply, the conditional gets added,
36:03
as I described before. So, the one case where we will error with policies, though, is when you do an update or an insert, basically, when the with-check clause fires, right? If the with-check clause fires, and you tried to do something that wasn't valid, we will throw an error back in. We will throw an error back, no matter how many rows you were trying
36:21
to insert at that time, right? So, if you're inserting 5,000 rows through values, and one of them violates the with-check policy, we're gonna blow the whole thing out, right? There's a little bit different from how select works, where you can only see certain rows. So, it's just something to keep in mind. Of course, you can look at what the policy is,
36:40
and use the conditional from the policy in your insert statement, or make the insert statement in insert select, that then has a conditional on it to address that concern if you need to. But just be aware that we will error the whole thing back if the with-check policy fires, and you are trying to do something that you're not allowed to do through the policy.
37:01
So, here's a couple of examples. This is what it looks like when it comes out, when you do a backslash D on a table. So, if there are any policies on it, this is what they're gonna show up like, right? And here you can see, I have this policy P1, my user equal current user, and then I'm doing this table T1. This is what the current data is in the table, right? I'm doing this as a super user.
37:21
You can see from the hash mark. So, you can tell that this is a case where we're doing, you know, I'm just showing off what's in the data. So, now I wanna actually set role to a different role, and see that the results are filtered as I expect, right? So, again, if there's no with-check clause, because I didn't specify, go back one. There's no with-check here on this,
37:42
but this policy applies to all commands. So, we're gonna take this using clause and apply it to both cases. So, here you can see, if I try to insert data for a different user other than my own, I'm gonna get this error, right? This policy error. This is actually slightly different now. I did this against an older version of Postgres,
38:02
older commit, and I didn't update it. This actually now says something about row-level policy, right, instead of saying with-check. So, it's a little bit cleaner now, thanks to Dean Rashid, some of the work that he did refactoring some of the code allowed us to have a more descriptive error message there for RLS. So, here we can talk about data modification policies.
38:23
So, here I'm gonna set this policy, my user equal current user, again, with-check true. This is that case I was talking about before. I can do blind inserts, if you will. So, here you can see, I can insert data. It does get inserted in the table, but then I can't see it. So, again, just something to be aware of. We talked through a lot of the considerations around that,
38:42
so I'm not gonna go into too much more here. So, this is something that I've been talking about a lot already in this, but this idea that we can have this check SE Linux, and this could be my data, or it might actually be like, I don't know, my label, or, you know, just label. So, everything in SE Linux are labels.
39:01
So, you can actually, you know, if you spend the effort, you can integrate this with the SEPG SQL module, which we've done, and I hope to be able to publish some of that very soon, to actually, hmm? Is it homework for you? No, no, no. Well, we're actually gonna publish it.
39:21
We've actually, because we've been able to get it to work, so, okay. Oh, yeah, yeah, no, the prior implementation was different. What this implementation does is essentially give you kind of all of the primitive functions that you need to be able to write policies that call out the functions.
39:45
Yeah, yeah, yeah, absolutely. And we've done that, and like I said, we're gonna be pushing that out soon. It won't make 9.5, obviously, because we're far past feature freeze, but the hope, anyway, is to update the SEPG SQL module for 9.6 to include these primitives, which will then allow you to create policies
40:01
that can do that, right? So, I'm looking forward to that. I think it'll be really neat. Here's just kind of a final, you know, kind of big example, a bit more, you know, rather than those little, tiny contrived ones, this is a real, more real, if you will. So, the idea here is it's a Unix password file, which hopefully most people are familiar with.
40:21
Username, password, UID, GID, blah, blah, blah, blah. Right, and then we have these three different roles in the system. We have the administrator, who's allowed to do a lot more stuff, and then we have just normal Bob and Alice stuff, right? So, here we're gonna first populate the tables, right? This is done as the administrator. We're just kind of setting things up. This is how we build the table. We have these set of values, right?
40:42
So, we have the user name, and then we have, you know, the actual name, phone number, some directories and stuff, right? And then we have this enable row security, right? So, we're actually enabling the policies, or row security on the tables so policies will be applied. Then we go through and create our policies. So, the policies that we're gonna create
41:00
are this admin all, which says, okay, the admin's allowed to do anything. Obviously, this could actually be simpler. We could just say using true rather than using and with check, but in some cases, maybe you wanna do both to make it explicit, right? Normal users can see all of the rows, right? And they're allowed to update their own record, but they can only do certain updates
41:20
with regard to the shell, all right? This is pretty typical, right? There's commands out there, CHSH, which allow a user to change their shell, for example. This is the same kind of idea here. So, this is our using policy that says, okay, the current user, whoever it is, can see their, you know, if their new name is there, then they're allowed to see that,
41:41
or they're allowed to update that record, right? But when they update it, the current user has to stay the same, they can't change that, and their shell must be in this list, okay? What this allows us to do is says, okay, the administrator can set the shell to whatever they want, but the user can only set it to this set, and obviously, this could be a select statement instead,
42:03
right, you could go out to another table and select what shells people are allowed to use kind of thing. So, then we have the grant system. So, we set all the grants up. The admin gets to do everything. Users only get select access on what I'll call public columns, right? So, they don't have select access
42:21
on the password column in particular. That's the one that you see that's not listed here, right? And then users are only allowed to update certain columns. So, they're allowed to update their password, their real name, home, phone, and shell. So, when I said that they weren't able to have the result be different from this, I think it's a good idea to have that in the policy,
42:42
although it's not strictly required, because they're not allowed to update that column anyway. So, they shouldn't actually be able to create a row where that's not the case, but for my two cents, if I was creating policies along these lines, I'd do it anyway just to make sure. So, anyway, so they're allowed to update these columns
43:01
on the path, and moving on, okay. So, now we're gonna set role to admin, and show, okay, admin can see everything. Yeah, yeah, yeah. Now we're gonna see what Alice can do. So, we set role to Alice, and we do this table path wd, and you get a permission denied, right? Alice doesn't have access to all of the columns in the table. That's why you get a permission denied.
43:20
So, Alice has to specify what columns she wants. So, here we are, we can specify these columns, and then we can see all the data for all those columns. If I wanna do an update password to change my username, as I said before, there were certain things that they're not allowed to do, right? So, one of the things that regular users are not allowed to do is to change this column, so we get a permission denied.
43:41
But, Alice is allowed to change her real name. So, she has to change it to Alice Joe, works just fine. Interesting thing, though, if she tries to update where it's somebody else, she gets an update zero, right? She doesn't get an error, because the visibility policy says that update is not allowed to see those rows. So, she simply doesn't, you know,
44:02
her update runs, but nothing happens. That's actually why this update only returns one, too. If you'll notice, there's no conditional here, right? So, this is updating all rows that Alice is allowed to update. Here is the update password. If you try to set this shell to something different, you get a violation of the policy. Like I said, that now says row level policy.
44:22
Delete, not allowed to delete, so we get an error back that's from the grant system. This is also from the grant system if she tried to insert into a passwd some new row, but Alice is allowed to change her own password, and that works just fine. I'm unfortunately running out of time here.
44:40
This is actually an hour-long talk, but we only have 45 minutes here, but I did want to mention again about leak-proof functions. We talked about them a little bit before. Leak-proof functions are functions that can get pushed down through a policy, same way they can get pushed down through a security barrier view. These are functions that Postgres, we have decided that these functions can never, ever return data back to the user, right?
45:02
Even in an error condition. Those are allowed to get pushed down, and that means a lot of optimizations happen that couldn't otherwise, which is great. We can certainly talk about some of the other things about it, but there's a lot of, only super users can make them leak-proof, of course, and they must actually be leak-proof if you make a non-leak-proof function.
45:21
You say it's leak-proof and it's not actually leak-proof, sorry. Don't do that. Here's just a quick explain example, sequence scan on that. This was applied from the policy. Here you can see that we are able to use indexes with policies, which is great. Here's something slightly more complicated.
45:41
You can see here, we first apply the policy, then we apply the filter, that's required, and that's the last I had, about 47 minutes, so I'm happy to talk further for a few minutes, but I just wanna let you know we do have to get out, and I don't wanna, I wanna get the next person. Wait. I'm done, but we do have the closing session. Anyway, yeah, questions.
46:06
That's correct, yes, policies are kept. Policies are kept even if you, yeah, yes, the answer is yes. Yes, what's that? So there was some discussion about potentially allowing them to be applied to views
46:22
and being applied to foreign tables and other things, and the answer to that is right now, no. It's something I definitely wanna look at, but currently you can't. So it's an interesting idea, but obviously you can put the conditionals inside the view if you want, or you can create another view, or whatever you wanna do there, so there's other ways of getting at whatever the problem is that you're trying to address through that,
46:41
but currently no. Other questions? Yeah, in the back. I thought I showed that. So the grant system is independent and gets applied, so yeah, you can't do like a select star and if you don't have access to a column, not see it. We don't do that.
47:01
If you select star, you have to have rights across all of them. There's no way to do per column. You can't hide columns and stuff, no. Yes. So if you use a function inside of your query,
47:27
yes, you can have whatever functions you want inside the policy. Same way as you can have whatever query, whatever thing you want inside of a security barrier view. Same thing. And we don't worry about pushing that down because you defined it, right?
47:42
Policy administrator defined it. We're gonna use it. If the malicious user is allowed to change
48:01
the function defined in the policy, you're screwed. Yes. Okay, yeah. So I don't think actually, I don't think create policy supports if not exist. I could be wrong though. I don't recall offhand.
48:20
Where was the syntax for that? I mean, obviously you can go look it up, but I don't think we have that. I don't show it here anyway. I don't think we do today. We could add it. Yeah. Yep, yeah, there's a view. There's a system catalog where everything is
48:41
and a view that you can go look at, which tries to make it nicer. Anything, what? You should never edit the system. Don't do that. Next. Ha ha ha ha ha ha ha ha ha ha.
49:03
Don't get me wrong. I do updates to pg database all the time, but yeah, it's not good. Yeah, okay. That's something we should probably add. Yeah, yes.
49:31
Wait, okay. pgdump has the ability to allow policies to be implemented while you're running, yeah. What is it you need though? Yeah?
49:43
Oh, don't dump the policies. Do we have it? I'd have to go look if we have it. Yeah, I don't think we did that, but yeah, it seems like it would probably be a good idea too. We can certainly look at adding that. Again, probably 9.6 material, but yeah, sure.
50:02
Any other questions? We only have a minute left. All right, thank you.