On Tue, Jun 17, 2014 at 10:25 PM, Stephen Frost <sfr...@snowman.net> wrote: >> Yeah, if we have to ask an external security module a question for >> each row, there's little hope of any real optimization. However, I >> think there will be a significant number of cases where people will >> want filtering clauses that can be realized by doing an index scan >> instead of a sequential scan, and if we end up forcing a sequential >> scan anyway, the feature will be useless to those people. > > I agree that we want to support that, if we can do so reasonably. What > I was trying to get at is simply this- don't we provide that already > with the leakproof attribute and functions? If we don't have enough > there to allow index scans then we should be looking to add more, I'm > thinking.
So the reason why we got onto this particular topic was because of the issue of multiple security policies for a single table. Of course, multiple security policies can always be merged into a single more-complex policy, but the resulting policy may be so complex that the query-planner is no longer capable of doing a good job optimizing it. I won't mention here exactly what a certain large commercial database vendor has implemented here; suffice it to say, however, that their design avoids this pitfall, and ours currently does not. > I agree on this point, but I'm still hopeful that we'll be able to get a > good feature into 9.5. There are quite a few resources available for > the 'just programming' part, so the long pole in the tent here is > absolutely hashing out what we want and how it should function. Agreed. > I'd be happy to host or participate in a conference call or similar if > that would be useful to move this along- or we can continue to > communicate via email. There's a bit of a lull in conferences to which > I'm going to right now, so in person is unlikely, unless folks want to > get together somewhere on the east coast (I'd be happy to travel to > Philly, Pittsburgh, NYC, etc, if it'd help..). For me, email is easiest; but there are other options, too. >> > What solution did you come up with for this case, which performed well >> > and was also secure..? >> >> I put the logic in the client. :-( > > Well, that's not helpful here. ;) Sure. The reason I brought it up is to say - hey, look, I had this come up in the real world. What would it take to be able to do actually do it in the database server? And the answer is - something that will handle multiple security policies cleanly. >> But I'm not sure; that >> feels like it's giving something up that might be important. And I >> think that the kinds of syntax we're discussing won't support leaving >> that out of the initial version and adding it later, so if we commit >> to this syntax, we're stuck with that behavior. To avoid that, we'd >> need something like this: >> >> ALTER TABLE tab ADD POLICY polname WHERE quals; >> GRANT SELECT (polname) ON TABLE tab TO role; > > Right, if we were to support multiple policies on a given table then we > would have to support adding and removing them individually, as well as > specify when they are to be applied- and what if that "when" overlaps? > Do we apply both and only a row which passed them all gets sent to the > user? Essentially we'd be defining the RLS policies to be AND'd > together, right? Would we want to support both AND-based and OR-based, > and allow users to pick what set of conditionals they want applied to > their various overlapping RLS policies? AND is not a sensible policy; it would need to be OR. If you grant someone access to two different subsets of the rows in a table, it stands to reason that they will expect to have access to all of the rows that are in at least one of those subsets. If you give someone your car key and your house key, that means they can operate your car or enter your house; it does not mean that they can operate your car but only when it's inside your garage. Alternatively, we could: - Require the user to specify in some way which of the available policies they want applied, and then apply only that one. or - Decide that such scenarios constitute misconfiguration. Throw an error and make the table owner or other relevant local authority fix it. > Sounds all rather painful and much better done programatically by the > user in a language which is suited to that task- eg: pl/pgsql, perl, C, > or something besides our ALTER syntax + catalog representation. I think exactly the opposite, for the query planning reasons previously stated. I think the policies will quickly get so complicated that they're no longer optimizable. Here's a simple example: - Policy 1 allows the user to access rows for which complexfunc() returns true. - Policy 2 allows the user to access rows for which a = 1. Most users have access only through policy 2, but some have access through policy 1. Users who have access through policy 1 will always get a sequential scan, but users who have access through policy 2 have an excellent chance of getting an index scan if the selectivity of a = 1 is high. When you merge those two things into a single policy, no matter how you do it, everyone gets sequential scans all the time. That sucks. >> Hmm, I think some users do want to do things like this. There are >> previous discussions of wanting to fuzz a set of coordinates, for >> example, or blank out a certain list of columns. > > Absolutely they'll want to be able to do this- but that's going to be a > case which I (and others, I think) feel comfortable going back and > saying "use views for that". I'm trying to draw that line in the ground > between what is RLS and what are views and keeping RLS to the WHERE > clause strikes me as a good line to draw (and one which matches up with > existing expectations in this space). Fair. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
