Re: [HACKERS] row security roadmap proposal
On Wed, Dec 18, 2013 at 10:21 PM, Craig Ringer cr...@2ndquadrant.com wrote: My main worry was that it requires the user to build everything manually, and is potentially error prone as a result. To address that we can build convenience features (label security, ACL types and operators, etc) on top of the same infrastructure later - it doesn't have to go in right away. So putting that side, the concerns I have are: - The current RLS design is restricted to one predicate per table with no contextual control over when that predicate applies. That means we can't implement anything like policy groups or overlapping sets of predicates, anything like that has to be built by the user. We don't need multiple predicates to start with but I want to make sure there's room for them later, particularly so that different apps / extensions that use row-security don't have to fight with each other. - You can't declare a predicate then apply it to a bunch of tables with consistent security columns. Updating/changing predicates will be a pain. We might be able to get around that by recommending that people use an inlineable SQL function to declare their predicates, but inlineable can be hard to pin down sometimes. If not, we need something akin to GRANT ... ALL TABLES IN SCHEMA ... for row security, and ALTER DEFAULT PRIVILEGES ... too. - It's too hard to see what tables have row-security and what impact it has. Needs psql improvements. - There's no way to control whether or not a client can see the row-security policy definition. The other one I want to deal with is secure session variables, but that's mostly a performance optimisation we can add later. What's your list? I don't have a lot of specific concerns apart from what I mentioned here: http://www.postgresql.org/message-id/ca+tgmoyc37qwnqkkqx3p3gu3psfh3tcox8ue06nnuiqn_4r...@mail.gmail.com One thing we do need to think about is our good friend search_path, and making sure that it's relatively easy to implement RLS in a way that's secure against malicious manipulation thereof. I do also agree with some of your concerns, particularly the first two (is it too manual? and insufficient contextual control). -- 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
Re: [HACKERS] row security roadmap proposal
On 12/18/13 10:21 PM, Craig Ringer wrote: In the end, sometimes I guess there's no replacement for WHERE call_some_procedure() That's where I keep ending up at. The next round of examples I'm reviewing this week plug pl/pgsql code into that model. And the one after that actually references locally cached data that starts stored in LDAP on another machine altogether. That one I haven't even asked for permission to share with the community because of my long standing LDAP allergy, but the whole thing plugs into the already submitted patch just fine. (Shrug) I started calling all of the things that generate data for RLS to filter on label providers. You've been using SELinux as an example future label provider. Things like this LDAP originated bit are another provider. Making the database itself a richer label provider one day is an interesting usability improvement to map out. But on the proof of concept things I've been getting passed I haven't seen an example where I'd use that yet anyway. The real world label providers are too complicated. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] row security roadmap proposal
On 12/18/2013 02:21 AM, Simon Riggs wrote: On 16 December 2013 14:36, Craig Ringer cr...@2ndquadrant.com wrote: - Decide on and implement a structure for row-security functionality its self. I'm persuaded by Robert's comments here, that whatever we expose must be significantly more usable than a DIY on top of views (with the fix for updatable security barrier views to make that possible). I outlined the skeleton of a possible design in my earlier post, with the heirachical and non-heirachical access labels. Should be implemented using the same API we expose for extensions (like SEPostgresql RLS). That part isn't clear why we must do better than that. Having declarative security is a huge step forward, in just the same way that updateable views were. They save the need for writing scripts to implement things, rather than just having a useful default. In my view the proposed patch doesn't offer a significant improvement in declarative security, beyond what we can get by just adding update support to s.b. views and using search_path to control whether a user sees the view or the base table. It's a lot like Oracle Virtual Private Database (VPD): A set of low level building blocks you can build your own flexible row security model with. One where you have to very carefully write security-sensitive predicates and define all your security model tables, etc yourself. That's why I'm now of the opinion that we should make it possible to achieve the same thing with s.b. views and the search_path (by adding update support)... then build a declarative row-security system that doesn't require the user fiddling with delicate queries and sensitive scripts on top of that. Personally, I see no reason not to commit the syntax we have now. So people can see what we'll be supporting, whenever that is. Do you mean commit the syntax without the supporting functionality, so it doesn't operate / just ERROR's initially? Or are you suggesting that the project commit the current patch with the known issues with user ID changes and the concerns about its internal structure and operation, then clean it up down the track? If there is a vision for that, lets see the vision. And then decide whether its worth waiting for. OK, I'll put an example together as a follow-up to this post - the how it should look part. Below I explain the *why* part. What the current patch does is add something a bit like a simplified and cut-down version of Oracle VPD. Oracle's VPD predicates are generated by a PL/SQL procedure that emits the SQL for a predicate. It has varying levels of caching to mitigate performance effects. The proposed patch for Pg doesn't do that. We'll have the predicate fixed and stored in the catalogs. It'll be like VPD with policy level SHARED_STATIC locked in as the only option. See http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#DBSEG98250 for details on Oracle VPD. It's essentially a few tools you can build your own row security model on, making the job a little easier. The DB designer / DBA still does a lot of work to define security on each table and do a bunch of procedure writing and setup to make it work. They have to get potentially security sensitive queries just right for each table. IMO it really isn't much different to just having an updatable security barrier view. Differences arise when you look at VPD-exempt user acess rights, at policy groups, etc, but we don't have any of that anyway. The only big differences are that you can apply a VPD policy to an existing table w/o having to refresh all views/functions that point to it, and that some users can be made exempt. (Note: I have not used VPD or Label Security myself; my comments are intentionally based only on public documentation and on discussions with others). Oracle then built Oracle Label Security (their row-security solution) *on* *top* of VPD. It's much more of a ready-to-go, configure it and use it product, where you don't have to write procedures and define security tables and generally build most of it yourself. See: http://docs.oracle.com/cd/B14117_01/network.101/b10774.pdf The Teradata implementation Robert pointed out skips the VPD-like layer entirely. It just implements a fairly simple and flexible label security model directly. You don't have to muck about with the details, you don't have to deal with the guts of the row security implementation, you just say here's what I want. I'd prefer to avoid adding syntax and interfaces for a feature that'll likely become mostly implementation detail for the feature users actually want - declarative security. That's part of why I've been focused on upd. s. b. views. It'll provide a way for users to do flexible DIY row-security without introducing a big chunk of new syntax and functionality we'll be stuck with supporting. It'll also be a useful building block for declarative row security. -- Craig Ringer http://www.2ndQuadrant.com/
Re: [HACKERS] row security roadmap proposal
On Tue, Dec 17, 2013 at 1:27 PM, Simon Riggs si...@2ndquadrant.com wrote: Not sure I'd say required, but its certainly desirable to have updateable security barrier views in themselves. And it comes across to me as a cleaner and potentially more performant way of doing the security checks for RLS. Yes, that's how I'm thinking of it. It's required in the sense that if we don't do it as a separate patch, we'll need to fold many of changes into the RLS patch, which IMV is not desirable. We'd end up with more complexity and less functionality with no real upside that I can see. But I think we are basically saying the same thing. -- 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
Re: [HACKERS] row security roadmap proposal
On Wed, Dec 18, 2013 at 3:30 AM, Craig Ringer cr...@2ndquadrant.com wrote: In my view the proposed patch doesn't offer a significant improvement in declarative security, beyond what we can get by just adding update support to s.b. views and using search_path to control whether a user sees the view or the base table. It's a lot like Oracle Virtual Private Database (VPD): A set of low level building blocks you can build your own flexible row security model with. One where you have to very carefully write security-sensitive predicates and define all your security model tables, etc yourself. That's why I'm now of the opinion that we should make it possible to achieve the same thing with s.b. views and the search_path (by adding update support)... then build a declarative row-security system that doesn't require the user fiddling with delicate queries and sensitive scripts on top of that. To be clear, I wasn't advocating for a declarative approach; I think predicates are fine. There are usability issues to worry about either way, and my concern is that we address those. A declarative approach would certainly be valuable in that, for those people for whom it is sufficiently flexible, it's probably quite a lot easier than writing predicates. But I fear that some people will want a lot more generality than a label-based system can accommodate. -- 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
Re: [HACKERS] row security roadmap proposal
On 12/18/2013 11:14 PM, Robert Haas wrote: To be clear, I wasn't advocating for a declarative approach; I think predicates are fine. There are usability issues to worry about either way, and my concern is that we address those. A declarative approach would certainly be valuable in that, for those people for whom it is sufficiently flexible, it's probably quite a lot easier than writing predicates. But I fear that some people will want a lot more generality than a label-based system can accommodate. Having spent some time reading the HL7 spec, I now tend to agree that labels alone are not going to be sufficient. There are security decisions made based on: - Security labels - User/group/role memberships, both for accessor and target entity owner (down to the row level) - Black/white list ACLs, with inheritance and deny rules. - One-off or narrow authorizations. I permit my GP to examine my mental health record details, but only over the last year, and the authorization stands only for today. - Authorization assertions. I declare that the patient told me it is OK to access these, let me see them. - Break glass access. This is an emergency. Give me access and I'll deal with the consequences later. So while security labels are an important part of the picture I'm forced to agree that they are not sufficient, and that a generalized row security mechanism actually is necessary. We don't have the time or resources to build all these sorts of things individually, and if we did it'd still be too inflexible for somebody. In the end, sometimes I guess there's no replacement for WHERE call_some_procedure() In particular, labels are totally orthogonal to entity identity in the data model, and being able to make row access decisions based on information already in the data model is important. FK relationships to owning entities and relationships between entities must be usable for security access policy decisions. So: I'm withdrawing the proposal to go straight to label security; I concede that it's insufficiently expressive to meet all possible needs, and we don't have the time to build anything declarative and user-friendly that would be. I do want to make sure that whatever we include this time around does not create problems for a future label security approach, but that's kind of required by the desire to add SEPostgreSQL RLS down the track anyway. Given that: What are your specific usability concerns about the current approach proposed in KaiGai's patch? My main worry was that it requires the user to build everything manually, and is potentially error prone as a result. To address that we can build convenience features (label security, ACL types and operators, etc) on top of the same infrastructure later - it doesn't have to go in right away. So putting that side, the concerns I have are: - The current RLS design is restricted to one predicate per table with no contextual control over when that predicate applies. That means we can't implement anything like policy groups or overlapping sets of predicates, anything like that has to be built by the user. We don't need multiple predicates to start with but I want to make sure there's room for them later, particularly so that different apps / extensions that use row-security don't have to fight with each other. - You can't declare a predicate then apply it to a bunch of tables with consistent security columns. Updating/changing predicates will be a pain. We might be able to get around that by recommending that people use an inlineable SQL function to declare their predicates, but inlineable can be hard to pin down sometimes. If not, we need something akin to GRANT ... ALL TABLES IN SCHEMA ... for row security, and ALTER DEFAULT PRIVILEGES ... too. - It's too hard to see what tables have row-security and what impact it has. Needs psql improvements. - There's no way to control whether or not a client can see the row-security policy definition. The other one I want to deal with is secure session variables, but that's mostly a performance optimisation we can add later. What's your list? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] row security roadmap proposal
On Mon, Dec 16, 2013 at 3:12 PM, Gregory Smith gregsmithpg...@gmail.com wrote: On 12/16/13 9:36 AM, Craig Ringer wrote: - Finish and commit updatable security barrier views. I've still got a lot of straightening out to do there. I don't follow why you've put this part first. It has a lot of new development and the risks that go along with that, but the POC projects I've been testing are more interested in the view side issues. I don't really see a way that any of this can work without that. To be clear, that work is required even just for read-side security. -- 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
Re: [HACKERS] row security roadmap proposal
On 16 December 2013 14:36, Craig Ringer cr...@2ndquadrant.com wrote: - Decide on and implement a structure for row-security functionality its self. I'm persuaded by Robert's comments here, that whatever we expose must be significantly more usable than a DIY on top of views (with the fix for updatable security barrier views to make that possible). I outlined the skeleton of a possible design in my earlier post, with the heirachical and non-heirachical access labels. Should be implemented using the same API we expose for extensions (like SEPostgresql RLS). That part isn't clear why we must do better than that. Having declarative security is a huge step forward, in just the same way that updateable views were. They save the need for writing scripts to implement things, rather than just having a useful default. If there is a vision for that, lets see the vision. And then decide whether its worth waiting for. Personally, I see no reason not to commit the syntax we have now. So people can see what we'll be supporting, whenever that is. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] row security roadmap proposal
On 17 December 2013 17:03, Robert Haas robertmh...@gmail.com wrote: On Mon, Dec 16, 2013 at 3:12 PM, Gregory Smith gregsmithpg...@gmail.com wrote: On 12/16/13 9:36 AM, Craig Ringer wrote: - Finish and commit updatable security barrier views. I've still got a lot of straightening out to do there. I don't follow why you've put this part first. It has a lot of new development and the risks that go along with that, but the POC projects I've been testing are more interested in the view side issues. I don't really see a way that any of this can work without that. To be clear, that work is required even just for read-side security. Not sure I'd say required, but its certainly desirable to have updateable security barrier views in themselves. And it comes across to me as a cleaner and potentially more performant way of doing the security checks for RLS. So I think its the right thing to do to wait for this, even if we can't do that for 9.4 Realistically, we have a significant amount of work before we're ready to pass a high security audit based around these features. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] row security roadmap proposal
On 12/18/2013 01:03 AM, Robert Haas wrote: On Mon, Dec 16, 2013 at 3:12 PM, Gregory Smith gregsmithpg...@gmail.com wrote: On 12/16/13 9:36 AM, Craig Ringer wrote: - Finish and commit updatable security barrier views. I've still got a lot of straightening out to do there. I don't follow why you've put this part first. It has a lot of new development and the risks that go along with that, but the POC projects I've been testing are more interested in the view side issues. I don't really see a way that any of this can work without that. To be clear, that work is required even just for read-side security. It's possible to build limited read-side-only security on top of the existing s.b. views as they stand, with no update support. You can grant write-only access to the base relations, and require people to use a different relation name / schema when they want to access a relation for write vs for read. You can't use RETURNING, and you can still learn from result rowcounts etc. It's clumsy but usable-ish. So it works - as long as you're using absolutely 100% read-only access for users you need to constrain, or you don't mind explicitly referring to the base table for write operations and not being able to use RETURNING. I've been looking at write support primarily because I was under the impression from prior discussion I read that the feature wasn't considered committable as a read-only feature. If a consensus can be built that read-only RLS would be acceptable after all, then I'll happily defer that in favour of the other work items. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] row security roadmap proposal
Hi all I'd like to outline a path toward getting row-security into core. Comments / discussion appreciated. I think I/we need to: - Finish and commit updatable security barrier views. I've still got a lot of straightening out to do there. - Add an attribute to portals that stores the user ID at the time the portal was planned. Possibly extensibly; I'd be surprised if we won't need to associate other local info with a portal later. - Allow storage of the pre-rewrite query plan and let saved plans be marked as needing rewrite when replanned. We'll need this to permit some users (initially just a hardcoded superuser; later by posession of a certain right) to be totally exempt from row-security policy. (Alternative: store separate with- and without-row-security plan trees and pick which we use). - Decide on and implement a structure for row-security functionality its self. I'm persuaded by Robert's comments here, that whatever we expose must be significantly more usable than a DIY on top of views (with the fix for updatable security barrier views to make that possible). I outlined the skeleton of a possible design in my earlier post, with the heirachical and non-heirachical access labels. Should be implemented using the same API we expose for extensions (like SEPostgresql RLS). - Produce and commit a patch that adds the C API for row-security, including calls to make it easy to wrap any relation in a dynamically created or stored updatable security barrier subquery during rewrite. - Produce and commit a patch series implementing the syntax, catalog tables / catalog changes, documentation, etc for row-security that uses this C API and commit it to core. SEPostgreSQL RLS can then be built on top of the same API, using the same core support. Thoughts? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] row security roadmap proposal
Craig Ringer cr...@2ndquadrant.com writes: - Add an attribute to portals that stores the user ID at the time the portal was planned. Possibly extensibly; I'd be surprised if we won't need to associate other local info with a portal later. This bit seems rather confused. A portal is a runnable query; we do not support replanning midstream, and I don't think we support changes of UID either. I agree that the plan cache needs to support treating change of UID as a reason to discard a cached plan, but that's nothing to do with portals. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] row security roadmap proposal
On 12/16/2013 10:43 PM, Tom Lane wrote: Craig Ringer cr...@2ndquadrant.com writes: - Add an attribute to portals that stores the user ID at the time the portal was planned. Possibly extensibly; I'd be surprised if we won't need to associate other local info with a portal later. This bit seems rather confused. A portal is a runnable query; we do not support replanning midstream, and I don't think we support changes of UID either. We _do_ support changes of UID, or rather, current_user returns the session user ID at the point in time it runs in the portal. This can be observed with SECURITY DEFINER pl/pgsql functions returning refcursor, and with cursors that're retained across a SET SESSION AUTHORIZATION. They don't even need to be WITH HOLD, and s.s.a. can occur within a transaction. The point is to return the user ID at the time the portal was created, rather than whatever the session now is. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] row security roadmap proposal
On 12/16/13 9:36 AM, Craig Ringer wrote: - Finish and commit updatable security barrier views. I've still got a lot of straightening out to do there. I don't follow why you've put this part first. It has a lot of new development and the risks that go along with that, but the POC projects I've been testing are more interested in the view side issues. - Decide on and implement a structure for row-security functionality its self. I'm persuaded by Robert's comments here, that whatever we expose must be significantly more usable than a DIY on top of views (with the fix for updatable security barrier views to make that possible). Can't say i agree we should be getting tangled into making this slick on the user side right now. If you open a new can of worms like heirachical access labels, this goes back into basic design, and we'll spend months on that before returning to exactly the same implementation details. I tried to make a case for how having a really generic mechanism that's doesn't presume to know how labels will be assigned can be a good thing. Given the state this is all in right now, I'd much rather publish a hard to use but powerful API than to presume you're going to get an easier to use design right. The place I'm at here is trying to figure out the simplest useful thing that could be committed and then hammer on the details. (Not the first time I've beat that drum on a feature) Your roadmap goes way past that, which is great to avoid being painted into a corner, but I'm thinking more about what a useful feature freeze point would look like given it's December 16 now. -- Greg Smith greg.sm...@crunchydatasolutions.com Chief PostgreSQL Evangelist - http://crunchydatasolutions.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
