Re: [HACKERS] pre-proposal: permissions made easier
and...@dunslane.net (Andrew Dunstan) writes: Jeff Davis wrote: On Mon, 2009-06-29 at 12:55 -0400, Tom Lane wrote: I think it has to be looked at in comparison to more general prospective-permissions schemes; When I searched google for prospective permissions, all I found were links to messages in this thread ;) Can you refer me to a general prospective-permissions scheme that is more widely accepted? Being more widely accepted also has the benefit that users will feel more comfortable with the behavior. Think of MySQL's wildcard permissions. They apply to any object whether that object is created before or after the rule is set, AIUI. That means the wildcard pattern is applied at the time the permission rule is referenced, rather than when the rule is created, thus applying it prospectively. It's a feature many users would like to have, although, as Tom rightly points out, it can be a bit of a footgun if used carelessly. I'll point out, for posterity, that way back in yesteryear, TOPS-10 (introduced in 1967) had a declarative permissioning system for file access that resembles this. The best description I'm aware of is the following: http://lkml.org/lkml/1999/2/5/2 A FILDAE config file for a particular user might look like the following: # anything in a directory named private is off limits */private/*:*:*:*: # people in group foo get full (create, delete, read, write, # execute) access to everything in the foo project directory ~/projects/foo/*:*:foo:*:cdrwx # people playing mygame can update the high score file ~/mygame/score.dat:*:*: ~/mygame/bin/mygame:rw # some friends have access to the RCS files for mygame ~/mygame/src/RCS/*:dennis,kevin,josh:*: /usr/bin/ci:rw ~/mygame/src/RCS/*:dennis,kevin,josh:*: /usr/bin/co:rw # I'll put stuff I want everyone to read in my ~/public directory # I'll make the public directory 744, so no one will actually have # to check .access_list, but I'll still put in this entry for completeness ~/public/*:*:*:*:r# anything left over gets no access*:*:*:*: This obviously isn't notably SQL-like, but that's not the point :-). -- cbbrowne,@,cbbrowne.com http://www3.sympatico.ca/cbbrowne/lisp.html Ubuntu is an ancient African word, meaning can't configure Debian -- 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] pre-proposal: permissions made easier
* Greg Stark gsst...@mit.edu [090630 00:18]: Perhaps tieing it to the schema is wrong and we should actually require the user to specify the template they want explicitly which would be even better for that. So it would be something like WITH GRANTS LIKE sensitive_table. And, not having any experience with the current permissions code, or the code required to do that (;-]), I would *love* something like that... *especially* if those grants remain by reference, i.e. If I change the GRANTS/REVOKES on sensitive_table, those are automatically apply to all tables created with the WITH GRANTS LIKE sensitive_table... It would simplify all the work I have to do in: make_$PERMISSION_ROLE(table) and make it much more elegant, and save me having to re-run them all if I want to change some permissions. But I realize that since I'm as anal about my database schemas as I am about my code, I'm probably not your typical DB dev shop people like Josh are used to dealing with... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] pre-proposal: permissions made easier
Aidan Van Dyk wrote: *especially* if those grants remain by reference, i.e. If I change the GRANTS/REVOKES on sensitive_table, those are automatically apply to all tables created with the WITH GRANTS LIKE sensitive_table... Isn't that exactly what Tom is objecting to, namely that the permissions of an object would not be contained entirely in catalog entry for the object itself? cheers andrew -- 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] pre-proposal: permissions made easier
* Andrew Dunstan and...@dunslane.net [090630 09:08]: Aidan Van Dyk wrote: *especially* if those grants remain by reference, i.e. If I change the GRANTS/REVOKES on sensitive_table, those are automatically apply to all tables created with the WITH GRANTS LIKE sensitive_table... Isn't that exactly what Tom is objecting to, namely that the permissions of an object would not be contained entirely in catalog entry for the object itself? Well, it depends on how it's done... If one of the permissions on an object you can assign is look at $X, the you don't get the hidden permissions problem. The object itself still contains everything you need to trace the permissions of an object... I have no idea if it's something that even half-aligns with the internal permission model/code... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] pre-proposal: permissions made easier
Andrew Dunstan and...@dunslane.net writes: David Fetter wrote: There have been previous discussions of prospective permissions changes. Are we restarting them here? It's not on the TODO list. I recall it being raised from time to time but I certainly don't recall a consensus that it should be done, nor how, so if you're implying that such a thing is a settled decision I suspect you're not entirely correct. Of course, my memory has been known to have errors ... I think there's widespread agreement that SQL permissions are a pain in the neck to manage. We haven't got a consensus on a solution to that, but looking at possibilities is certainly reasonable. Jeff's idea does amount to granting prospective permissions in one sense. If you (in the future) grant some permissions to role foo, then role foo_ro would automatically get some of those permissions too. I think it has to be looked at in comparison to more general prospective-permissions schemes; it clearly doesn't do everything you could wish for in that line, and so we have to ask whether there'd be much use-case left for it if we do implement something more general. It also seems to me that a lot of the potential objections are shared with more general schemes --- in particular, ooops, I forgot this was in place and indirectly granted some permissions I shouldn't have... 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] pre-proposal: permissions made easier
On Mon, 2009-06-29 at 12:55 -0400, Tom Lane wrote: I think it has to be looked at in comparison to more general prospective-permissions schemes; When I searched google for prospective permissions, all I found were links to messages in this thread ;) Can you refer me to a general prospective-permissions scheme that is more widely accepted? Being more widely accepted also has the benefit that users will feel more comfortable with the behavior. Regards, Jeff Davis -- 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] pre-proposal: permissions made easier
Jeff Davis pg...@j-davis.com writes: Can you refer me to a general prospective-permissions scheme that is more widely accepted? Well, the point of my post was that nothing's gotten to the point of being widely accepted. But there are people working on a default ACLs scheme that would cover some of that territory. http://wiki.postgresql.org/wiki/DefaultACL 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] pre-proposal: permissions made easier
Jeff Davis wrote: On Mon, 2009-06-29 at 12:55 -0400, Tom Lane wrote: I think it has to be looked at in comparison to more general prospective-permissions schemes; When I searched google for prospective permissions, all I found were links to messages in this thread ;) Can you refer me to a general prospective-permissions scheme that is more widely accepted? Being more widely accepted also has the benefit that users will feel more comfortable with the behavior. Think of MySQL's wildcard permissions. They apply to any object whether that object is created before or after the rule is set, AIUI. That means the wildcard pattern is applied at the time the permission rule is referenced, rather than when the rule is created, thus applying it prospectively. It's a feature many users would like to have, although, as Tom rightly points out, it can be a bit of a footgun if used carelessly. cheers andrew -- 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] pre-proposal: permissions made easier
All, First, let me talk about the problem: it's been my observation that the majority of users, including public commercial web sites, which I run into in the field do not employ permissions in any useful way to protect their data. An awful lot of these applications are running as the superuser or the database owner, partly because the company can't deal with object permissions management, especially when the application is under continuous development. The pgAdmin widget doesn't really help much in this respect. I want to avoid anything which requires an additional permissions check or any other check at runtime. Instead, we need two tools: 1) ALTER SCHEMA SET DEFAULT PRIVILEGES statements which sets default permissions, by ROLE and object type, on new objects. 2) a statement to set privs on all existing objects by type and role within a schema. These two tools would make it vastly easier for admins to manage ROLE privileges without needing any additional runtime checks or limiting flexibility in object permissions assignment. Further, they would make it very simple to build the kind of very simple ROLE-based permissions management Jeff is talking about on top of it (which is, BTW, very popular; it's one of MSSQL's major selling points to small businesses). -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- 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] pre-proposal: permissions made easier
On Mon, 2009-06-29 at 10:52 -0700, Josh Berkus wrote: 1) ALTER SCHEMA SET DEFAULT PRIVILEGES statements which sets default permissions, by ROLE and object type, on new objects. 2) a statement to set privs on all existing objects by type and role within a schema. I don't see why either of these things should be properties of the schema. It seems to make much more sense for these defaults to be a property of the user who creates the objects. If #1 and #2 are both implemented as properties of the user, I think that solves the use case I brought up. It would still be difficult to see the overall scheme at a glance, but I don't think that's a major problem. Regards, Jeff Davis -- 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] pre-proposal: permissions made easier
Jeff Davis pg...@j-davis.com writes: On Mon, 2009-06-29 at 10:52 -0700, Josh Berkus wrote: 1) ALTER SCHEMA SET DEFAULT PRIVILEGES statements which sets default permissions, by ROLE and object type, on new objects. 2) a statement to set privs on all existing objects by type and role within a schema. I don't see why either of these things should be properties of the schema. It seems to make much more sense for these defaults to be a property of the user who creates the objects. That seems fairly backwards to me. I agree that tying it to schemas is a bit less flexible than one could wish, but that doesn't make attaching it to the user the right thing. 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] pre-proposal: permissions made easier
Jeff, I don't see why either of these things should be properties of the schema. It seems to make much more sense for these defaults to be a property of the user who creates the objects. The main reason is existing practice. Currently, most applications I see in the field which bother with having several ROLES have all database objects belonging to one ROLE (db_owner). So for most people setting permissions for all objects belonging to a specific user would amount to setting permissions for all objects of that type in a given database. There's also the fact that SCHEMAs currently have their own visibility rules and permissions, which seems to me to dovetail nicely with the ACLs. This is, of course, assuming that we are talking about setting permissions in saved objects, that is, all the object belonging to a particular user. The approach I could see as valuable in vastly simplyfying things would be to set the permission on the user regardless of object properties; that is, the user is defined as WITH SELECT, INSERT, UPDATE ON ALL TABLES. These user permissions would supercede any object permissions for that role. This would make DBA's lives vastly simpler and make them more likely to use permissions. But would it actually benefit security? The problem I see with this approach is that in 95% of the applications I run across there are a few tables which really need to be locked down and restricted from most user access (maybe accessed only by an SRF). In large development shops where more than one person has their hands on the DB, I can easily see one developer accidentally bypassing object-level security set up by another DBA through this mechanism. The second, and bigger problem I can see is that this opens a whole new set of security holes by allowing an end-run around the existing access control structure with attackers can try to exploit. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- 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] pre-proposal: permissions made easier
Josh Berkus j...@agliodbs.com writes: The second, and bigger problem I can see is that this opens a whole new set of security holes by allowing an end-run around the existing access control structure with attackers can try to exploit. Yeah. I'm very concerned about any scheme that invents additional sources of permissions that aren't visible in the object's own ACL list. Even if it's secure in its own terms, it'll blindside people and programs who are used to the existing ways of doing things. From what I recall of prior discussions, there is rough consensus that the two types of facilities you mentioned (setting up default ACLs to be applied at creation of objects created later, and providing a way to change multiple objects' permissions with one GRANT) are desirable, though there is plenty of argument about the details. Neither of these result in creating any new sources of permissions --- a given object's ACL is still the whole truth. 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] pre-proposal: permissions made easier
Tom, From what I recall of prior discussions, there is rough consensus that the two types of facilities you mentioned (setting up default ACLs to be applied at creation of objects created later, and providing a way to change multiple objects' permissions with one GRANT) are desirable, though there is plenty of argument about the details. Neither of these result in creating any new sources of permissions --- a given object's ACL is still the whole truth. yeah, that's why I've been working on that approach. It doesn't simplify things as much as some DBAs might want, but it's the most side-effect-free approach. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- 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] pre-proposal: permissions made easier
On Mon, Jun 29, 2009 at 7:41 PM, Josh Berkusj...@agliodbs.com wrote: The main reason is existing practice. I haven't followed the entire conversation so i'm not sure who I'm going to be disagreeing with or agreeing with here. But I wanted to mention that existing practice may not be a very useful place to start here. Whatever mechanism we invent is going to change the calculus of people deciding how to set up their schemas and roles since they'll want to arrange things to take advantage of this new mechanism. In particular, one early question was whether to use wildcard patterns or schema names. People were saying wildcard patterns would be more flexible because people don't always set up their objects in different schemas. But if we had a mechanism someone wanted to use which depended on schemas they would be far more likely to choose to set up schemas for objects which belong in different security classes. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] pre-proposal: permissions made easier
Greg, In particular, one early question was whether to use wildcard patterns or schema names. People were saying wildcard patterns would be more flexible because people don't always set up their objects in different schemas. But if we had a mechanism someone wanted to use which depended on schemas they would be far more likely to choose to set up schemas for objects which belong in different security classes. What I'm saying is that there are many users currently using schema for security classes. I personally haven't ever encountered a DBA who used role ownership of objects as a mechanism for security context. There's nothing conceptually invalid about the latter approach, but it would be hard for DBAs to grasp, and as a result less of them would use it. Mainly that's because the concept of schema easily maps (even if inaccurately) to the concept of directories, whose permissions IT staff are used to managing. So it's more intuitive for a DBA to say This is sensitive data so I'm going to put it in the SENSITIVE schema than to say this is sensitive data so I'm going to have the table belong to the SENSITIVE role. Further, it's common practice on other DBMSes to have a database owner role which owns all database objects. So DBA who learn Postgres second are going to do that. If we were going for a theoretically pure approach, we'd actually have a security context concept which would include a bundle of permissions and each object would belong to one. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- 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] pre-proposal: permissions made easier
On Mon, Jun 29, 2009 at 9:02 PM, Josh Berkusj...@agliodbs.com wrote: What I'm saying is that there are many users currently using schema for security classes. I personally haven't ever encountered a DBA who used role ownership of objects as a mechanism for security context. There's nothing conceptually invalid about the latter approach, but it would be hard for DBAs to grasp, and as a result less of them would use it. Mainly that's because the concept of schema easily maps (even if inaccurately) to the concept of directories, whose permissions IT staff are used to managing. So it's more intuitive for a DBA to say This is sensitive data so I'm going to put it in the SENSITIVE schema than to say this is sensitive data so I'm going to have the table belong to the SENSITIVE role. Further, it's common practice on other DBMSes to have a database owner role which owns all database objects. So DBA who learn Postgres second are going to do that. It sounds to me like they *are* using the owner to provide security then. If we were going for a theoretically pure approach, we'd actually have a security context concept which would include a bundle of permissions and each object would belong to one. It sounds like you're confounding the the owner of the objects and roles that users have. In the databases I've used we had a dba user which owned all the objects. Then we had a www user which had DML access to most objects, though lacked update and delete access to others. We actually didn't need any other users but if we had a more extensive backend interface we would have had a www-backend and reports and so on. So I'm not sure what problem we're trying to solve here. There's I just created a new www-backend role which I want to have access to everything www has and then I'll go add a few additional privileges. We can do that already by having www-backend belong to the www role and then add the extra privileges manually. And there's I just created a new table, I want www and www-backend to get their usual privileges without thinking about it. You want to be able to specify default grants that an object gets based on the schema? That seems mostly reasonable though it might be a good idea to have a WITH DEFAULT GRANTS or something like that on the CREATE statement so that the dba has to make it explicit. It does seems slightly silly since surely anyone creating a new object would just paste in their grants from another object or some common source anyways, but I suppose that's the way with convenience features. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] pre-proposal: permissions made easier
Greg, And there's I just created a new table, I want www and www-backend to get their usual privileges without thinking about it. You want to be able to specify default grants that an object gets based on the schema? That seems mostly reasonable though it might be a good idea to have a WITH DEFAULT GRANTS or something like that on the CREATE statement so that the dba has to make it explicit. Well, the idea is *user and schema*, not schema alone. I think Jeff's proposal for users was user alone, unmodified by schema. I'd prefer to reverse the switch (i.e. NO DEFAULT GRANTS) just because I'd like default grants to work with ORMs and similar. In other words, my/stephen's proposal amounts to the idea that objects in a schema should, by default, be able to inherit permissions from their schema at creation time. It does seems slightly silly since surely anyone creating a new object would just paste in their grants from another object or some common source anyways, but I suppose that's the way with convenience features. That works fine until you have 6 (or more) defined roles and a couple hundred objects, and are in a agile environment where the dev team is constantly adding objects which have the wrong permissions. That's whose problem I'm trying to solve (because they're my clients). -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- 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] pre-proposal: permissions made easier
On Tue, Jun 30, 2009 at 1:51 AM, Josh Berkusj...@agliodbs.com wrote: It does seems slightly silly since surely anyone creating a new object would just paste in their grants from another object or some common source anyways, but I suppose that's the way with convenience features. That works fine until you have 6 (or more) defined roles and a couple hundred objects, and are in a agile environment where the dev team is constantly adding objects which have the wrong permissions. That's whose problem I'm trying to solve (because they're my clients). Well I don't understand how you get them wrong if you're just pasting them from a file. I mean, sure you can pick the wrong template but nothing can help you there. You could just as easily pick the wrong template if it's a database feature instead of a text file. Agile doesn't mean doing things without thinking about them :) -- greg http://mit.edu/~gsstark/resume.pdf -- 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] pre-proposal: permissions made easier
Greg, Well I don't understand how you get them wrong if you're just pasting them from a file. I mean, sure you can pick the wrong template but nothing can help you there. You could just as easily pick the wrong template if it's a database feature instead of a text file. I really have to wonder if you've ever managed a production database project. As someone who has managed quite a few, my idea of the feature is designed to make my life (and my clients') easier. It's *vastly* easier to tell developers don't touch the permissions, it will take care of itself and set it in a central location than to expect them to remember to apply a set of permissions each time, or follow them around playing catch-up on the objects they add and modify. Oracle, MSSQL, etc. all have management solutions for this. For a good reason. Agile doesn't mean doing things without thinking about them :) In many companies it does. :-( -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- 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] pre-proposal: permissions made easier
On Tue, Jun 30, 2009 at 04:24:40AM +0100, Greg Stark wrote: On Tue, Jun 30, 2009 at 1:51 AM, Josh Berkusj...@agliodbs.com wrote: It does seems slightly silly since surely anyone creating a new object would just paste in their grants from another object or some common source anyways, but I suppose that's the way with convenience features. That works fine until you have 6 (or more) defined roles and a couple hundred objects, and are in a agile environment where the dev team is constantly adding objects which have the wrong permissions. That's whose problem I'm trying to solve (because they're my clients). Well I don't understand how you get them wrong if you're just pasting them from a file. I mean, sure you can pick the wrong template but nothing can help you there. You could just as easily pick the wrong template if it's a database feature instead of a text file. Agile doesn't mean doing things without thinking about them :) It does in a lot of shops ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] pre-proposal: permissions made easier
On Tue, Jun 30, 2009 at 4:39 AM, Josh Berkusj...@agliodbs.com wrote: Well I don't understand how you get them wrong if you're just pasting them from a file. I mean, sure you can pick the wrong template but nothing can help you there. You could just as easily pick the wrong template if it's a database feature instead of a text file. I really have to wonder if you've ever managed a production database project. As someone who has managed quite a few, my idea of the feature is designed to make my life (and my clients') easier. It's *vastly* easier to tell developers don't touch the permissions, it will take care of itself and set it in a central location than to expect them to remember to apply a set of permissions each time, or follow them around playing catch-up on the objects they add and modify. But that's not what we're talking about and there's no way they can just take care of themselves. The database isn't a mind-reader and can't know whether this new table is supposed to have the public web data permission template or the sensitive data permission template. You can put it in the wrong schema and get the wrong default permission just as easily as you can choose the wrong text template to paste into your database creation script. I'm not saying it's a bad idea to have some sort of short cut for the default permissions. Actually it sounds like it would lend itself to the good code practice of being self-documenting which makes it easier to see that which template's being used which is sounds like quite a good thing. But you do still have to think carefully about that choice. Perhaps tieing it to the schema is wrong and we should actually require the user to specify the template they want explicitly which would be even better for that. So it would be something like WITH GRANTS LIKE sensitive_table. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pre-proposal: permissions made easier
I'd like some brief feedback on this idea before I try to make a real proposal. The use case is this: You have an application with several roles: * admin user - owns all the objects related to that application * normal user - INSERT/UPDATE/DELETE plus sequence usage * read-only user - for reporting The problem is managing all the permissions requires a lot of care, and it's difficult to easily verify that all the permissions are set as you expect on all of the objects. Because it's more difficult to manage, I think many people just have a single user for all of these things. My idea is to have a GRANT mask: CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo; [syntax not meant as a real proposal yet, just for illustration] And foo_ro would get the SELECT ON TABLE and USAGE ON SCHEMA privileges from foo, but not any INSERT privileges. That way, you can add roles without having to do a GRANT on each object separately. And, more importantly, you can pretty easily observe that the privileges are what you expect without inspecting the objects individually. This idea is meant to be a better solution than the GRANT ... * that MySQL offers. Questions: 1. Is this a reasonable approach from a security standpoint, or is it fundamentally flawed? 2. Does it violate the SQL standard? 3. Is it completely orthogonal to IN ROLE and INHERITS, or should they be made to work together somehow? Regards, Jeff Davis -- 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] pre-proposal: permissions made easier
Jeff Davis pg...@j-davis.com wrote: I'd like some brief feedback on this idea before I try to make a real proposal. The use case is this: You have an application with several roles: * admin user - owns all the objects related to that application * normal user - INSERT/UPDATE/DELETE plus sequence usage * read-only user - for reporting The problem is managing all the permissions requires a lot of care, and it's difficult to easily verify that all the permissions are set as you expect on all of the objects. Because it's more difficult to manage, I think many people just have a single user for all of these things. My idea is to have a GRANT mask: CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo; [syntax not meant as a real proposal yet, just for illustration] And foo_ro would get the SELECT ON TABLE and USAGE ON SCHEMA privileges from foo, but not any INSERT privileges. That way, you can add roles without having to do a GRANT on each object separately. And, more importantly, you can pretty easily observe that the privileges are what you expect without inspecting the objects individually. This idea is meant to be a better solution than the GRANT ... * that MySQL offers. Questions: 1. Is this a reasonable approach from a security standpoint, or is it fundamentally flawed? It seems to me that you're duplicating the functionality that is already possible by using groups. i.e. grant the permissions to the group and add users to the group as appropriate. -- Bill Moran http://www.potentialtech.com -- 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] pre-proposal: permissions made easier
On Sun, 2009-06-28 at 14:12 -0400, Bill Moran wrote: It seems to me that you're duplicating the functionality that is already possible by using groups. i.e. grant the permissions to the group and add users to the group as appropriate. Take the use case in my email. You would have to grant a specific set of permissions to each of 3 groups for every object created. The problem is not having many users that all share the exact same permissions; the problem is having to assign separate permissions on a per-object basis. Regards, Jeff Davis -- 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] pre-proposal: permissions made easier
Jeff Davis pg...@j-davis.com writes: My idea is to have a GRANT mask: CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo; You haven't really explained what foo is here. If it's a single object then I don't think this offers any leverage. If it's a placeholder or class representative of some kind, then maybe, but in that case you've entirely failed to convey the point ... 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] pre-proposal: permissions made easier
On Sun, 2009-06-28 at 14:32 -0400, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: My idea is to have a GRANT mask: CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo; You haven't really explained what foo is here. If it's a single object then I don't think this offers any leverage. If it's a placeholder or class representative of some kind, then maybe, but in that case you've entirely failed to convey the point ... I meant for foo to be a user. foo_ro would be the read-only version, who has a strict subset of foo's permissions. Regards, Jeff Davis -- 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] pre-proposal: permissions made easier
Jeff Davis pg...@j-davis.com writes: On Sun, 2009-06-28 at 14:32 -0400, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: My idea is to have a GRANT mask: CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo; You haven't really explained what foo is here. I meant for foo to be a user. foo_ro would be the read-only version, who has a strict subset of foo's permissions. I see. It seems like rather a complicated (and expensive) mechanism for a pretty narrow use-case. It'd only help for the cases where you could define your permissions requirements that way. I agree that there are some such cases, but I think real-world problems tend to be a bit more complicated than that. I fear people would soon want exceptions to the strict subset rule; and once you put that in, the conceptual simplicity disappears, as does the ability to easily verify what the set of GRANTs is doing. 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] pre-proposal: permissions made easier
On Sun, 2009-06-28 at 14:56 -0400, Tom Lane wrote: I meant for foo to be a user. foo_ro would be the read-only version, who has a strict subset of foo's permissions. I see. It seems like rather a complicated (and expensive) mechanism for a pretty narrow use-case. It'd only help for the cases where you could define your permissions requirements that way. I agree that there are some such cases, but I think real-world problems tend to be a bit more complicated than that. I fear people would soon want exceptions to the strict subset rule; and once you put that in, the conceptual simplicity disappears, as does the ability to easily verify what the set of GRANTs is doing. As soon as the permissions scheme gets more complicated than what I suggest, I agree that the user is better off just using GRANTs on a per-object basis. You could still GRANT directly to the user foo_ro -- for instance if your reporting user needs to join against some other table -- but that could get complicated if you take it too far. The users I'm targeting with my idea are: * Users who have a fairly simple set of users and permissions, and who want a simple picture of the permissions in their system for reassurance/verification. * Users who come from MySQL every once in a while, annoyed that we don't support GRANT ... * syntax. * Users who are savvy enough to use access control, but don't have rigorous procedures for making DDL changes. Some of these users depend on an ORM or similar to make DDL changes for them, and this idea gives them a workaround. * Users who don't currently use separate permissions, but might start if it's simpler to do simple things. Maybe I should shop this idea on -general and see how many people's problems would actually be solved? The performance issue is something to consider, but I think it would just be an extra catalog lookup (for each level), and the users of this feature would probably be willing to pay that cost. Regards, Jeff Davis -- 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] pre-proposal: permissions made easier
On Sun, Jun 28, 2009 at 12:52:54PM -0700, Jeff Davis wrote: On Sun, 2009-06-28 at 14:56 -0400, Tom Lane wrote: I meant for foo to be a user. foo_ro would be the read-only version, who has a strict subset of foo's permissions. I see. It seems like rather a complicated (and expensive) mechanism for a pretty narrow use-case. It'd only help for the cases where you could define your permissions requirements that way. I agree that there are some such cases, but I think real-world problems tend to be a bit more complicated than that. I fear people would soon want exceptions to the strict subset rule; and once you put that in, the conceptual simplicity disappears, as does the ability to easily verify what the set of GRANTs is doing. As soon as the permissions scheme gets more complicated than what I suggest, I agree that the user is better off just using GRANTs on a per-object basis. You could still GRANT directly to the user foo_ro -- for instance if your reporting user needs to join against some other table -- but that could get complicated if you take it too far. The users I'm targeting with my idea are: * Users who have a fairly simple set of users and permissions, and who want a simple picture of the permissions in their system for reassurance/verification. I don't know of a case that started simple and stayed there without a lot of design up front. In other words, those who'd benefit by such a thing are generally not those who'd want a shortcut. * Users who come from MySQL every once in a while, annoyed that we don't support GRANT ... * syntax. I'm missing what's wrong with a wild-card GRANT syntax for this case. * Users who are savvy enough to use access control, but don't have rigorous procedures for making DDL changes. Some of these users depend on an ORM or similar to make DDL changes for them, and this idea gives them a workaround. Such ORMs are a problem, and accommodating them only aggravates it. :) * Users who don't currently use separate permissions, but might start if it's simpler to do simple things. This is a matter of education, not tools. The problem here is not that permissions are unavailable, but that people are failing to use them. Maybe I should shop this idea on -general and see how many people's problems would actually be solved? There are a few issues at hand here, some of which could get addressed by polling on -general: * SQL standards compliance (clearly not a -general issue) * Utility to current users (might be addressable on -general) * Utility to future users (not on -general) * Trade-offs such a solution would impose (possibly on -general and the ever-popular * Stuff I didn't think of ;) The performance issue is something to consider, but I think it would just be an extra catalog lookup (for each level), and the users of this feature would probably be willing to pay that cost. Where did this come up? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] pre-proposal: permissions made easier
On Sun, Jun 28, 2009 at 2:07 PM, Jeff Davispg...@j-davis.com wrote: I'd like some brief feedback on this idea before I try to make a real proposal. The use case is this: You have an application with several roles: * admin user - owns all the objects related to that application * normal user - INSERT/UPDATE/DELETE plus sequence usage * read-only user - for reporting The problem is managing all the permissions requires a lot of care, and it's difficult to easily verify that all the permissions are set as you expect on all of the objects. Because it's more difficult to manage, I think many people just have a single user for all of these things. My idea is to have a GRANT mask: CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo; [syntax not meant as a real proposal yet, just for illustration] And foo_ro would get the SELECT ON TABLE and USAGE ON SCHEMA privileges from foo, but not any INSERT privileges. That way, you can add roles without having to do a GRANT on each object separately. And, more importantly, you can pretty easily observe that the privileges are what you expect without inspecting the objects individually. This idea is meant to be a better solution than the GRANT ... * that MySQL offers. Questions: 1. Is this a reasonable approach from a security standpoint, or is it fundamentally flawed? Reasonable. 2. Does it violate the SQL standard? Don't know. 3. Is it completely orthogonal to IN ROLE and INHERITS, or should they be made to work together somehow? Maybe GRANT foo (SELECT, USAGE) TO foo_ro, meaning grant restricted membership in role foo to foo_ro, encompassing only the SELECT and USAGE privileges of foo? I do to some degree share Tom's worry that this is an idiosyncratic solution to a tiny subset of the problem space. On the other hand, I have to admit I've needed to do this exact thing, so I wrote a script to issue the necessary grant statements. Then I discovered that whenever I created any new objects (most frequently drop and recreate of any of the relevant views) the permissions got screwed up, so I crontabbed the script to run every 20 minutes. This works, but it doesn't bleed elegance. I'm not sure what the right things to do in this area are, but I'm glad that you (and others, like Stephen Frost) are thinking about it... ...Robert -- 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] pre-proposal: permissions made easier
David Fetter wrote: * Users who come from MySQL every once in a while, annoyed that we don't support GRANT ... * syntax. I'm missing what's wrong with a wild-card GRANT syntax for this case. Without a major change in the way we do permissions, it will not work prospectively. We have no way ATM to store permissions for an object that does not currently exist. cheers andrew -- 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] pre-proposal: permissions made easier
On Sun, Jun 28, 2009 at 05:27:19PM -0400, Andrew Dunstan wrote: David Fetter wrote: * Users who come from MySQL every once in a while, annoyed that we don't support GRANT ... * syntax. I'm missing what's wrong with a wild-card GRANT syntax for this case. Without a major change in the way we do permissions, it will not work prospectively. We have no way ATM to store permissions for an object that does not currently exist. There have been previous discussions of prospective permissions changes. Are we restarting them here? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] pre-proposal: permissions made easier
On Sun, 2009-06-28 at 17:23 -0400, Robert Haas wrote: I do to some degree share Tom's worry that this is an idiosyncratic solution to a tiny subset of the problem space. I share the concern. However, I don't know if it's a tiny subset or not; I think we'll have to get some feedback from users to really know. On the other hand, I have to admit I've needed to do this exact thing, so I wrote a script to issue the necessary grant statements. Then I discovered that whenever I created any new objects (most frequently drop and recreate of any of the relevant views) the permissions got screwed up, so I crontabbed the script to run every 20 minutes. This works, but it doesn't bleed elegance. I have written scripts to handle some of this in the past, and it's always awkward. That's what made me start thinking about alternatives. Regards, Jeff Davis -- 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] pre-proposal: permissions made easier
* David Fetter (da...@fetter.org) wrote: On Sun, Jun 28, 2009 at 05:27:19PM -0400, Andrew Dunstan wrote: Without a major change in the way we do permissions, it will not work prospectively. We have no way ATM to store permissions for an object that does not currently exist. There have been previous discussions of prospective permissions changes. Are we restarting them here? Having default permissions for new objects (something a couple of us are working towards) would help with this situation some. I don't think the ground Jeff's proposal would cover is entirely covered by just having default permissions though. Stephen signature.asc Description: Digital signature
Re: [HACKERS] pre-proposal: permissions made easier
On Sun, 2009-06-28 at 14:16 -0700, David Fetter wrote: The users I'm targeting with my idea are: * Users who have a fairly simple set of users and permissions, and who want a simple picture of the permissions in their system for reassurance/verification. I don't know of a case that started simple and stayed there without a lot of design up front. In other words, those who'd benefit by such a thing are generally not those who'd want a shortcut. I think that the 3 user types I outlined are a fairly reasonable permissions scheme for a significant set of applications. I have used that in the past, and generally speaking, I didn't need to make lots of strange exceptions. * Users who don't currently use separate permissions, but might start if it's simpler to do simple things. This is a matter of education, not tools. The problem here is not that permissions are unavailable, but that people are failing to use them. I don't think education is the answer. These users aren't necessarily ignorant, but just don't want to hack up scripts to manage permissions for what they perceive are simple schemes. If the user imagines a well-defined but simple scheme, and it takes a lot of awkward scripts to accomplish it, I think we've missed something. A reporting user seems like a perfectly normal kind of user to create, and yet it's very awkward to do. The performance issue is something to consider, but I think it would just be an extra catalog lookup (for each level), and the users of this feature would probably be willing to pay that cost. Where did this come up? Tom mentioned that it might be expensive to check permissions, which I assume was due to the extra catalog lookups required. I don't think it's a major concern, nor would it affect normal permissions checks, unless I missed something. Regards, Jeff Davis -- 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] pre-proposal: permissions made easier
On Sun, 2009-06-28 at 14:38 -0700, David Fetter wrote: There have been previous discussions of prospective permissions changes. Are we restarting them here? I don't remember seeing anything in those discussions that really materialized. Can you point me to something that you think is a better alternative than my idea? Regards, Jeff Davis -- 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] pre-proposal: permissions made easier
On Sun, 2009-06-28 at 18:03 -0400, Stephen Frost wrote: * David Fetter (da...@fetter.org) wrote: On Sun, Jun 28, 2009 at 05:27:19PM -0400, Andrew Dunstan wrote: Without a major change in the way we do permissions, it will not work prospectively. We have no way ATM to store permissions for an object that does not currently exist. There have been previous discussions of prospective permissions changes. Are we restarting them here? Having default permissions for new objects (something a couple of us are working towards) would help with this situation some. I don't think the ground Jeff's proposal would cover is entirely covered by just having default permissions though. One case that it would not cover is creating new roles that you would like to have access to existing objects. Defaults may be useful independently, though, so I think the proposals are overlapping, but generally different. Regards, Jeff Davis -- 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] pre-proposal: permissions made easier
David Fetter wrote: On Sun, Jun 28, 2009 at 05:27:19PM -0400, Andrew Dunstan wrote: David Fetter wrote: * Users who come from MySQL every once in a while, annoyed that we don't support GRANT ... * syntax. I'm missing what's wrong with a wild-card GRANT syntax for this case. Without a major change in the way we do permissions, it will not work prospectively. We have no way ATM to store permissions for an object that does not currently exist. There have been previous discussions of prospective permissions changes. Are we restarting them here? *shrug* It's not on the TODO list. I recall it being raised from time to time but I certainly don't recall a consensus that it should be done, nor how, so if you're implying that such a thing is a settled decision I suspect you're not entirely correct. Of course, my memory has been known to have errors ... cheers andrew -- 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] pre-proposal: permissions made easier
On Sun, Jun 28, 2009 at 06:28:32PM -0400, Andrew Dunstan wrote: David Fetter wrote: On Sun, Jun 28, 2009 at 05:27:19PM -0400, Andrew Dunstan wrote: David Fetter wrote: * Users who come from MySQL every once in a while, annoyed that we don't support GRANT ... * syntax. I'm missing what's wrong with a wild-card GRANT syntax for this case. Without a major change in the way we do permissions, it will not work prospectively. We have no way ATM to store permissions for an object that does not currently exist. There have been previous discussions of prospective permissions changes. Are we restarting them here? *shrug* It's not on the TODO list. I recall it being raised from time to time but I certainly don't recall a consensus that it should be done, nor how, That was my recollection, too. so if you're implying that such a thing is a settled decision I suspect you're not entirely correct. By no means. Of course, my memory has been known to have errors ... Same with mine ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
