Re: [HACKERS] [PATCHES] Users/Groups - Roles
Dear Tom, We probably need to think a bit harder about the meaning of CREATEROLE though. Right now it gives free license not only to create roles but to alter any property of existing roles. This seems appropriate if you think of it as a safer form of superuser, which is how I was thinking of it. It would be too powerful for Fabien's situation though. Yes. ISTM it would be a good thing to separate - sysadmin issues (create a user that can connect - pg_hba.conf) and - dbadmin issues (manage permission on data in a catalog). The first item is the current pg superuser. I envision the second item as being a property of the database (catalog) OWNER, which can do whatever he/she wants at home, but should not bother others. So there is a need for a limited 'createrole' ability (simple roles that cannot be users (?), and avoid name space collision). This is better for teaching, but also for any service provider which would host many users and databases, and would like to transfer them. If you want to delegate data access privileges to someone, it is better to have that restricted to the catalog to avoid name-space collision. The same issue arises when moving a database from one cluster to another where some role names may already be used. That is the kind of think I also need to do as a dbadmin. If one take a conceptual perspective, database access privilege management is a catalog-related task, so it seems better to have that in the catalog. So I think all points that roles are better (more useful) per-catalog than per-cluster. If you just want to claim that 'pg has roles', you can do the marketing with per-cluster roles;-) If you want them more useful, you need them per-catalog. -- Fabien. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Dear Stephen, I still think that removing groups and having per-cluster roles is not a good idea. The better way would be to keep user/group and add per-catalog roles. There is an opportunity which is being missed, and that won't show up later. I really disagree with you here. I feel it makes much more sense to do this in stages, first user/group - roles, then roles-per-catalog, which means you can then have both per-catalog 'users' and per-catalog 'groups', if you want to limit your view to that. I don't think that having two kinds of roles (per-cluster and per-catalog) would be a practical thing from the user perspective. From the implementation point of view, two tables will be needed. If you don't create roles directly in the right scope, it will create confusion later. The two concept need to have two different names so that they can be understood. Moreover, a working per-cluster grouping was already available. Changing the role scope will be much harder than creating a role directly in the good scope. From the implementation perspective, there is more work at adding per-cluster roles and removing per-cluster group and then later try to add per-catalog roles than adding per-catalog roles directly without touching the existing group stuff. So I'm afraid that the opportunity is missed and that per-catalog role will never get in. Well, at least you look more optimistic than me;-) -- Fabien. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Fabien, * Fabien COELHO ([EMAIL PROTECTED]) wrote: I really disagree with you here. I feel it makes much more sense to do this in stages, first user/group - roles, then roles-per-catalog, which means you can then have both per-catalog 'users' and per-catalog 'groups', if you want to limit your view to that. I don't think that having two kinds of roles (per-cluster and per-catalog) would be a practical thing from the user perspective. From the implementation point of view, two tables will be needed. If you don't create roles directly in the right scope, it will create confusion later. The two concept need to have two different names so that they can be understood. Moreover, a working per-cluster grouping was already available. Changing the role scope will be much harder than creating a role directly in the good scope. The two concepts certainly don't need different names to distinguish them. A simple distinction such as superusers are per-cluster and all other roles are not would be sufficient. I expect that's the kind of thing people would be looking for anyway. From the implementation perspective, there is more work at adding per-cluster roles and removing per-cluster group and then later try to add per-catalog roles than adding per-catalog roles directly without touching the existing group stuff. Having just spent a fair bit of time on the implementation, I have to disagree with you here. So I'm afraid that the opportunity is missed and that per-catalog role will never get in. Well, at least you look more optimistic than me;-) Honestly, this comes across to me the same as saying that because we have databases we'd never have schemas. Please outline exactly what you're really looking for. Let's drop the idea of per-cluster users/groups/roles/whatever and instead consider what specific capabilities you're looking for. We can then decide if those capabilities are best provided through per-catalog roles, if they're already covered with the existing framework, or if there's some other, better solution. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Fabien COELHO [EMAIL PROTECTED] writes: Moreover, a working per-cluster grouping was already available. Only for sufficiently small values of working. The lack of ability for groups to contain other groups and for groups to be the direct owners of objects were both pretty serious restrictions, which are now fixed. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Dear Stephen, Please outline exactly what you're really looking for. Let's drop the idea of per-cluster users/groups/roles/whatever and instead consider what specific capabilities you're looking for. I think from a conceptual point of view that the ability to manage permissions at the database level (per-catalog role) is a good thing (tm) for everybody. The privilege management is about a catalog, so it better to have it in the catalog. My personnal uses are two fold : - for teaching purposes, I can give every student his/her database and have her/him practice db privileges independently. They can create their own roles and do whatever with them... - for administration purposes, different databases have different requirements, and maybe different kind of role readonly, modifiable, fulladmin which could be attributed independently in each database. Basically, I want to be able to delegate to someone the right management for one database, including the creation of roles and so on, without interference from one database to another. So as to illustrate what I call an interference: say you have 2 databases which where on 2 clusters and you want to transfert them into the same cluster. If the same role name was used in both database, you may have interferences, people being given rights on one database and applying them to the other if they can connect to it. We can then decide if those capabilities are best provided through per-catalog roles, if they're already covered with the existing framework, or if there's some other, better solution. One inelegant solution is to prefix the role names with the database name, but that is just a discipline and cannot be inforced. I think we can do better. If you're right that having both per-catalog and per-cluster role with some flag would be accepted into postgresql, then that will be fine with me. I'll just argue for the per-catalog roles to be the default. Thanks for all your answers, have a nice day, -- Fabien. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Fabien, * Fabien COELHO ([EMAIL PROTECTED]) wrote: Please outline exactly what you're really looking for. Let's drop the idea of per-cluster users/groups/roles/whatever and instead consider what specific capabilities you're looking for. I think from a conceptual point of view that the ability to manage permissions at the database level (per-catalog role) is a good thing (tm) for everybody. The privilege management is about a catalog, so it better to have it in the catalog. Permissions are at a number of levels already: cluster, database, schema, table. Permissions at different levels hasn't got anything to do w/ per-catalog roles. My personnal uses are two fold : - for teaching purposes, I can give every student his/her database and have her/him practice db privileges independently. They can create their own roles and do whatever with them... Right, this can be done now. When you set up each student with his/her database create two roles: Role 1: Has createrole permissions and admin permissions on role 2. Role 2: Add into pg_hba.conf as a role with permission to access the db. When a student wants to create another role with access to the db they just have to log in as Role 1 and create the role and add that role to Role 2. That role can then log in to their database. - for administration purposes, different databases have different requirements, and maybe different kind of role readonly, modifiable, fulladmin which could be attributed independently in each database. I don't see how this has got anything to do w/ per-catalog roles either... Basically, I want to be able to delegate to someone the right management for one database, including the creation of roles and so on, without interference from one database to another. That's what createrole should let you do w/ current CVS HEAD. Don't thank me though, Tom did the heavy lifting wrt that. So as to illustrate what I call an interference: say you have 2 databases which where on 2 clusters and you want to transfert them into the same cluster. If the same role name was used in both database, you may have interferences, people being given rights on one database and applying them to the other if they can connect to it. Ah-hah, now here's something we can talk about: namespace collision. That's an issue which per-catalog roles would help with. I'm not so sure that'd make administration *easier* though, I'd think it'd make it harder, honestly, at least in the case where you've got multiple databases that you want to give a certain person access to. We can then decide if those capabilities are best provided through per-catalog roles, if they're already covered with the existing framework, or if there's some other, better solution. One inelegant solution is to prefix the role names with the database name, but that is just a discipline and cannot be inforced. I think we can do better. That's essentially all you're really asking for though, and is something which could be done in the current framework. It'd probably be more elegant to have a per-catalog pg_authid though. As long as we can identify the database trying to be connected to at the same time or before we get the username I don't think this would be too hard to support. Perhaps for 8.2 this could be done, though I'm still not convinced it's a definite win. If you're right that having both per-catalog and per-cluster role with some flag would be accepted into postgresql, then that will be fine with me. I'll just argue for the per-catalog roles to be the default. It'd really be a create-role option, 'create role abc GLOBAL' or some such. The resolution would then be check the per-catalog pg_authid first and if nothing is found there go to the system-wide pg_authid. That's kind of ugly, of course, and could slow things down for people who prefer to have global roles instead of per-catalog roles. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
The privilege management is about a catalog, so it better to have it in the catalog. Permissions are at a number of levels already: cluster, database, schema, table. Permissions at different levels hasn't got anything to do w/ per-catalog roles. Sorry for not being very clear. I see two main levels: (1) the connection which is managed in pg_hba.conf. It is a sysadmin concern, where you decide who will be able to get into your system. This issue is *not* addressed by the SQL specs. (2) once you're connected to a catalog, the ability to adjust/organize privileges for accessing data within this catalog. This is a dbadmin concern, where you decide for a given user which can access the database, what data should be readable. This issue is addressed by the SQL specs. If you think unix, root decides the first, and each user decides the second for the files it owns. - for teaching purposes [...] Right, this can be done now. There is the namespace collision issue, and although I might grant a student the privilege to create simple roles, I would not allow them to create new users for a basic practice;-) - for administration purposes, different databases have different requirements, and maybe different kind of role readonly, modifiable, fulladmin which could be attributed independently in each database. I don't see how this has got anything to do w/ per-catalog roles either... Because of namespace collision. That what I mean by independently above. So as to illustrate what I call an interference [...] Ah-hah, now here's something we can talk about: namespace collision. That is just what I meant in the last 10 mails when I mention per-catalog roles as better than per-cluster roles. I just did not put in the right keywords to make myself clear:-( Sigh. That's an issue which per-catalog roles would help with. Indeed. I'm not so sure that'd make administration *easier* though, I'd think it'd make it harder, honestly, at least in the case where you've got multiple databases that you want to give a certain person access to. Sure, grouping at the cluster level is also useful. [...] As long as we can identify the database trying to be connected to at the same time or before we get the username I don't think this would be too hard to support. Yet, but this is not what I'm looking for. I want the grouping per-catalog, but the user (or connectable-role now) is fine enough for me at the cluster level. Perhaps for 8.2 this could be done, though I'm still not convinced it's a definite win. For the user per-catalog part, I agree with you. If you're right that having both per-catalog and per-cluster role with some flag would be accepted into postgresql, then that will be fine with me. I'll just argue for the per-catalog roles to be the default. It'd really be a create-role option, 'create role abc GLOBAL' There is also a problem of namespace collision if you have both global and local roles. When I create a global role, pg cannot check that this name is not used in ANY of the databases. If you can have two admin roles, one global and one local... I doubt Tom will let pass such an improvement. Also, I don't feel the upward compatibility constraint well with per-catalog roles once per-cluster roles are in place. or some such. The resolution would then be check the per-catalog pg_authid first and if nothing is found there go to the system-wide pg_authid. That's kind of ugly, of course, and could slow things down for people who prefer to have global roles instead of per-catalog roles. If the per-catalog role is empty, I guess an obvious optimisation could be implemented;-) Good night, -- Fabien. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stupid question, but how do roles relate to our existing groups? --- Fabien COELHO wrote: The privilege management is about a catalog, so it better to have it in the catalog. Permissions are at a number of levels already: cluster, database, schema, table. Permissions at different levels hasn't got anything to do w/ per-catalog roles. Sorry for not being very clear. I see two main levels: (1) the connection which is managed in pg_hba.conf. It is a sysadmin concern, where you decide who will be able to get into your system. This issue is *not* addressed by the SQL specs. (2) once you're connected to a catalog, the ability to adjust/organize privileges for accessing data within this catalog. This is a dbadmin concern, where you decide for a given user which can access the database, what data should be readable. This issue is addressed by the SQL specs. If you think unix, root decides the first, and each user decides the second for the files it owns. - for teaching purposes [...] Right, this can be done now. There is the namespace collision issue, and although I might grant a student the privilege to create simple roles, I would not allow them to create new users for a basic practice;-) - for administration purposes, different databases have different requirements, and maybe different kind of role readonly, modifiable, fulladmin which could be attributed independently in each database. I don't see how this has got anything to do w/ per-catalog roles either... Because of namespace collision. That what I mean by independently above. So as to illustrate what I call an interference [...] Ah-hah, now here's something we can talk about: namespace collision. That is just what I meant in the last 10 mails when I mention per-catalog roles as better than per-cluster roles. I just did not put in the right keywords to make myself clear:-( Sigh. That's an issue which per-catalog roles would help with. Indeed. I'm not so sure that'd make administration *easier* though, I'd think it'd make it harder, honestly, at least in the case where you've got multiple databases that you want to give a certain person access to. Sure, grouping at the cluster level is also useful. [...] As long as we can identify the database trying to be connected to at the same time or before we get the username I don't think this would be too hard to support. Yet, but this is not what I'm looking for. I want the grouping per-catalog, but the user (or connectable-role now) is fine enough for me at the cluster level. Perhaps for 8.2 this could be done, though I'm still not convinced it's a definite win. For the user per-catalog part, I agree with you. If you're right that having both per-catalog and per-cluster role with some flag would be accepted into postgresql, then that will be fine with me. I'll just argue for the per-catalog roles to be the default. It'd really be a create-role option, 'create role abc GLOBAL' There is also a problem of namespace collision if you have both global and local roles. When I create a global role, pg cannot check that this name is not used in ANY of the databases. If you can have two admin roles, one global and one local... I doubt Tom will let pass such an improvement. Also, I don't feel the upward compatibility constraint well with per-catalog roles once per-cluster roles are in place. or some such. The resolution would then be check the per-catalog pg_authid first and if nothing is found there go to the system-wide pg_authid. That's kind of ugly, of course, and could slow things down for people who prefer to have global roles instead of per-catalog roles. If the per-catalog role is empty, I guess an obvious optimisation could be implemented;-) Good night, -- Fabien. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Bruce Momjian (pgman@candle.pha.pa.us) wrote: Stupid question, but how do roles relate to our existing groups? Uhhh. There are no longer groups, they've been replaced with roles (which can have members). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Thanks, TODO updated. We still support CREATE GROUP? It translates to roles? --- Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Stupid question, but how do roles relate to our existing groups? As committed, roles subsume both users and groups: a role that permits login (rolcanlogin) acts as a user, and a role that has members is a group. It is possible for the same role to do both things, though I'm not sure that it's good security policy to set up a role that way. The advantage over what we had is exactly that there isn't any distinction, and thus groups can do everything users can and vice versa: * groups can own objects * groups can contain other groups (we forbid loops though) Also there is a notion of admin option for groups, which is like grant option for privileges: you can designate certain members of a group as being able to grant ownership in that group to others, without having to make them superusers. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Bruce Momjian pgman@candle.pha.pa.us writes: Stupid question, but how do roles relate to our existing groups? As committed, roles subsume both users and groups: a role that permits login (rolcanlogin) acts as a user, and a role that has members is a group. It is possible for the same role to do both things, though I'm not sure that it's good security policy to set up a role that way. The advantage over what we had is exactly that there isn't any distinction, and thus groups can do everything users can and vice versa: * groups can own objects * groups can contain other groups (we forbid loops though) Also there is a notion of admin option for groups, which is like grant option for privileges: you can designate certain members of a group as being able to grant ownership in that group to others, without having to make them superusers. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Bruce Momjian (pgman@candle.pha.pa.us) wrote: Thanks, TODO updated. We still support CREATE GROUP? It translates to roles? Yes, CREATE USER too. Stephen Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Stupid question, but how do roles relate to our existing groups? As committed, roles subsume both users and groups: a role that permits login (rolcanlogin) acts as a user, and a role that has members is a group. It is possible for the same role to do both things, though I'm not sure that it's good security policy to set up a role that way. The advantage over what we had is exactly that there isn't any distinction, and thus groups can do everything users can and vice versa: * groups can own objects * groups can contain other groups (we forbid loops though) Also there is a notion of admin option for groups, which is like grant option for privileges: you can designate certain members of a group as being able to grant ownership in that group to others, without having to make them superusers. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
On Friday 01 July 2005 13:07, Stephen Frost wrote: * Bruce Momjian (pgman@candle.pha.pa.us) wrote: Thanks, TODO updated. We still support CREATE GROUP? It translates to roles? Yes, snip However On Friday 01 July 2005 13:02, Stephen Frost wrote: * Bruce Momjian (pgman@candle.pha.pa.us) wrote: Stupid question, but how do roles relate to our existing groups? Uhhh. There are no longer groups, they've been replaced with roles (which can have members). Was following this conversation up till now, because these two statement seem to contradict each other. Do we really support groups still, are is CREATE GROUP now syntactical sugar for some for of CREATE ROLE. -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Robert Treat [EMAIL PROTECTED] writes: Was following this conversation up till now, because these two statement seem to contradict each other. Do we really support groups still, are is CREATE GROUP now syntactical sugar for some for of CREATE ROLE. CREATE GROUP and CREATE USER are both now syntactic sugar for CREATE ROLE. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Robert Treat ([EMAIL PROTECTED]) wrote: On Friday 01 July 2005 13:07, Stephen Frost wrote: However On Friday 01 July 2005 13:02, Stephen Frost wrote: * Bruce Momjian (pgman@candle.pha.pa.us) wrote: Stupid question, but how do roles relate to our existing groups? Uhhh. There are no longer groups, they've been replaced with roles (which can have members). Was following this conversation up till now, because these two statement seem to contradict each other. Do we really support groups still, are is CREATE GROUP now syntactical sugar for some for of CREATE ROLE. CREATE GROUP just does a CREATE ROLE now, yeah. You can check gram.y for the details if you'd like. We do still support \du and \dg (pg_users and pg_groups respectively, iirc) for backwards compat. and to help folks get used to the new stuff. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Fabien COELHO [EMAIL PROTECTED] writes: Right, this can be done now. There is the namespace collision issue, and although I might grant a student the privilege to create simple roles, I would not allow them to create new users for a basic practice;-) Why not? With the setup Stephen suggests, they could create only new users that could only get into their own database (since they'd not be able to grant connect rights to other databases). We probably need to think a bit harder about the meaning of CREATEROLE though. Right now it gives free license not only to create roles but to alter any property of existing roles. This seems appropriate if you think of it as a safer form of superuser, which is how I was thinking of it. It would be too powerful for Fabien's situation though. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: Fabien COELHO [EMAIL PROTECTED] writes: Right, this can be done now. There is the namespace collision issue, and although I might grant a student the privilege to create simple roles, I would not allow them to create new users for a basic practice;-) Why not? With the setup Stephen suggests, they could create only new users that could only get into their own database (since they'd not be able to grant connect rights to other databases). I'm curious why not too... One issue I can think of is that alter role ... rename, etc could be a problem. We probably need to think a bit harder about the meaning of CREATEROLE though. Right now it gives free license not only to create roles but to alter any property of existing roles. This seems appropriate if you think of it as a safer form of superuser, which is how I was thinking of it. It would be too powerful for Fabien's situation though. Well, what about it makes it 'too powerful'? I think that's pretty much the same question you're asking Fabien above. I agree that only certain properties should probably be modifiable though, one inparticular that comes to mind is 'LOGIN'; I can see why you might want to allow only roles which can't log in to be creatable by a given role. It strikes me that it'd make some sense to have independent grant control over each of the fields in pg_authid. It would also make sense to limit the power of alter role to certain roles based on who they were created by (superuser vs. createrole). It seems we probably need at least an association to either catalog or creator for each role which could then be used to limit alter role commands. catalog probably makes more sense in the long run, creator would be easier in the short-term. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: We probably need to think a bit harder about the meaning of CREATEROLE though. Right now it gives free license not only to create roles but to alter any property of existing roles. This seems appropriate if you think of it as a safer form of superuser, which is how I was thinking of it. It would be too powerful for Fabien's situation though. Well, what about it makes it 'too powerful'? The fact that you could alter roles that (in some sense) don't belong to you. In particular you could allow yourself into other people's databases, if you can alter the roles that they are using to define who can connect to those databases. Or cause denials of service by revoking other people's memberships in those roles. On the other hand, CREATEROLE as-is does exactly what it was intended to do, namely let the DBA do all normal admin maintenance of users/groups without taking the risks involved in doing stuff as superuser. If we restrict it more, then we'll be back to the situation where there are routine manual admin tasks that require superuserdom. So on reflection I don't think we should restrict it. If we need a more restrictive feature, then we need a different feature. I'm of the opinion that it's too late to do much about Fabien's use-case in this devel cycle. We could possibly have designed something rational if it had been brought up earlier in the discussion of roles (which I remind you has been going on for months). But now it's too late to do anything that wouldn't be a kluge, and probably a kluge we'd regret later. Possibly for 8.2 we could invent a notion of roles having owners. Offhand I don't see any harm in letting non-CREATEROLE users create non-login roles, and manipulate the membership of roles they have created (or that have been assigned to them by a superuser). On the other hand, it could be that the WITH ADMIN OPTION feature is already sufficient for this. This really needs some thought ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Users/Groups - Roles
[sorry, resent because stalled] Dear Stephen, Right, it's a beginning to proper 'Role' support as defined by the SQL specification. Ok. AFAIC remember, the specification is pretty subtle and fuzzy enough so that there is room for little design options. I understand your concerns here and while I agree with the basic idea that per-catalog role sets would be nice it wasn't what I had set out to do with this patch. Perhaps what you're asking for will be added later on. Some things this patch does do though are: [...] Ok. I think I understand but I'm not sure it is done the right way. Let me explain my (possibly wrong) point of view: The standard talks about 2 distinct concepts: USER and ROLE (4.34). I'm not sure it is a good idea to drop the user concept to replace it by role. If you do so, you may miss something about what roles are about. The SESSION_USER/CURRENT_USER has a CURRENT_ROLE which defines the rights at a given time. This role can be changed by the user, based on user/role membership, so the user can change its 'effective' rights. Roles are potential privileges that a user can set himself in if he/she desires so. example: sh psql -u calvin mydb [EMAIL PROTECTED] -- I'm user calvin with no role or a default role on mydb. -- I can do all which is allowed to 'calvin' as a user. [EMAIL PROTECTED] SET ROLE admin; calvin/[EMAIL PROTECTED] ... -- I'm allowed to do that if the role 'admin' is granted to 'calvin' -- now I can do whatever is allowed to role 'admin'. calvin/[EMAIL PROTECTED] SET ROLE basic; calvin/[EMAIL PROTECTED] ... -- now I can do what is allowed to role 'basic' and the roles 'basic' are in. -- things that where allowed to admin may *not* be accessible now. This is a very useful feature, and a key idea of the specs IMVVHO. ISTM that the way user and role are fused misses that important point, as I have not seen a set role in the grammar file. Although in the spec role rights are transitive in the role realm, it should *stop* at the user. If you drop the user concept, you just have a group with automatically provided rights. The fact that the spec does not specify the USER stuff and specifies the ROLE stuff does not mean that having only roles is the good way to go. So for me we should have per-cluser users as they where up to now, per-catalog roles with the properties I described, and possibly per-cluster group just for the sake of compatibility/simplicity of the access control and managing group of users as a whole. ROLE should not replace USER/GROUP. It should be added next to it. Maybe I'm wrong at my reading of the spec and its intent, and at my quick check through the status of the cvs head, but that's my current understanding, and I think it should be checked seriously. Have a nice day, -- Fabien ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Users/Groups - Roles
On Thu, Jun 30, 2005 at 13:39:09 +0200, Fabien COELHO [EMAIL PROTECTED] wrote: The standard talks about 2 distinct concepts: USER and ROLE (4.34). I'm not sure it is a good idea to drop the user concept to replace it by role. If you do so, you may miss something about what roles are about. I think it is a good idea to make users synonymous with roles with regard to privileges. This will make checking for access simpler and mistakes less likely. The special part of being a user in addition to a role is that being a user allows for authentication. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Fabien, * Fabien COELHO ([EMAIL PROTECTED]) wrote: This is a very useful feature, and a key idea of the specs IMVVHO. ISTM that the way fuse user and role misses that important point, as I have not seen a set role in the grammar file. 'set role' is coming, sorry it wasn't in my initial patch. We are looking to support pretty much all of the SQL features 'Basic roles' and 'Extended roles'. I think 'set role' and maybe some cleanup of information_schema is all we need to claim 'Basic roles' support. For 'Extended roles' I think we need revoke role cascade support. Interestingly, the SQL2003 draft I'm looking at doesn't list 'drop role role name cascade' as being valid, yet other places in the spec specify 'drop role role name cascade' usage, so I think we should support that. Although in the spec role rights are transitive in the role realm, it should *stop* at the user. If you drop the user concept, you just have a group with automatically provided rights. I'm not quite sure what you mean here. Role right resolution starts from the user and then works backwards up the tree, with multi-level resolution. It wouldn't go past the logged in user since that's really where it starts. The fact that the spec does not specify the USER stuff and specifies the ROLE stuff does not mean that having only roles is the good way to go. I'm pretty sure we'll be able to match the SQL spec and support at least everything we did before with users/groups... So for me we should have per-cluser users as they where up to now, per-catalog roles with the properties I described, and possibly per-cluster group just for the sake of compatibility/simplicity of the access control and managing group of users as a whole. ROLE should not replace USER/GROUP. It should be added next to it. I don't see much point in having USER or GROUP when we have roles. Is there something specific that you feel can't be done with roles that could be done w/ USER/GROUP? Per-catalog roles is an interesting idea, but I'd tend to think that if you want per-catalog roles, you'd want per-catalog users too. I don't have any problem with that, but I don't see how not being per-catalog indicates we should have USER/GROUP instead of roles. Maybe I'm wrong at my reading of the spec and its intent, and at my quick check through the status of the cvs head, but that's my current understanding, and I think it should be checked seriously. I just went through the spec yesterday, check -hackers for my email about what CVS head supports vs. what's in the SQL spec. I don't see any particular reason why we wouldn't be able to fully support 'Basic roles' and 'Extended roles' in 8.1, I think we're quite close now... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Dear Stephen, Right, it's a beginning to proper 'Role' support as defined by the SQL specification. Ok. AFAIC remember, the specification is pretty subtle and fuzzy enough so that there is room for little design options. I understand your concerns here and while I agree with the basic idea that per-catalog role sets would be nice it wasn't what I had set out to do with this patch. Perhaps what you're asking for will be added later on. Some things this patch does do though are: [...] Ok. I think I understand but I'm not sure it is done the right way. Let me explain my (possibly wrong) point of view: The standard talks about 2 distinct concepts: USER and ROLE (4.34). I'm not sure it is a good idea to drop the user concept to replace it by role. If you do so, you may miss something about what roles are about. The SESSION_USER/CURRENT_USER has a CURRENT_ROLE which defines the rights at a given time. This role can be changed by the user, based on user/role membership, so the user can change its 'effective' rights. Roles are potential privileges that a user can set himself in if he/she desires so. example: sh psql -u calvin mydb [EMAIL PROTECTED] -- I'm user calvin with no role or a default role on mydb. -- I can do all which is allowed to 'calvin' as a user. [EMAIL PROTECTED] SET ROLE admin; calvin/[EMAIL PROTECTED] ... -- I'm allowed to do that if the role 'admin' is granted to 'calvin' -- now I can do whatever is allowed to role 'admin'. calvin/[EMAIL PROTECTED] SET ROLE basic; calvin/[EMAIL PROTECTED] ... -- now I can do what is allowed to role 'basic' and the roles 'basic' are in. -- things that where allowed to admin may *not* be accessible now. This is a very useful feature, and a key idea of the specs IMVVHO. ISTM that the way fuse user and role misses that important point, as I have not seen a set role in the grammar file. Although in the spec role rights are transitive in the role realm, it should *stop* at the user. If you drop the user concept, you just have a group with automatically provided rights. The fact that the spec does not specify the USER stuff and specifies the ROLE stuff does not mean that having only roles is the good way to go. So for me we should have per-cluser users as they where up to now, per-catalog roles with the properties I described, and possibly per-cluster group just for the sake of compatibility/simplicity of the access control and managing group of users as a whole. ROLE should not replace USER/GROUP. It should be added next to it. Maybe I'm wrong at my reading of the spec and its intent, and at my quick check through the status of the cvs head, but that's my current understanding, and I think it should be checked seriously. Have a nice day, -- Fabien ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Dear Stephen, Thanks again on working on this feature. Role right resolution starts from the user and then works backwards up the tree, with multi-level resolution. It wouldn't go past the logged in user since that's really where it starts. ISTM that the starting point should *not* be the user, but the CURRENT_ROLE, which must be something distinct: Even if I'm root, if a 'SET ROLE very_limited_privileges' is performed, then the privileges in effect are those of the chosen role. That is what is told by section 4.34.1.1 SQL-session authorization identifiers of the SQL 2003 specs as I understand it. If the user is kind of a role, then I'm afraid the whole point may be missed. But maybe not, it would depend on the implementation details. So for me we should have per-cluser users as they where up to now, per-catalog roles with the properties I described, and possibly per-cluster group just for the sake of compatibility/simplicity of the access control and managing group of users as a whole. ROLE should not replace USER/GROUP. It should be added next to it. I don't see much point in having USER or GROUP when we have roles. Indeed, if you have per-cluster ROLE, you don't need GROUP anymore. If USER is per-cluster for connection management and ROLE per-catalog for database access management, then you will need a per-cluster grouping (say for pg_hba.conf...) which is just the current GROUP. Is there something specific that you feel can't be done with roles that could be done w/ USER/GROUP? No, it is the reverse: I'm afraid that the way it seems to be heading, no more will be done with role than with group before. Per-catalog roles is an interesting idea, but I'd tend to think that if you want per-catalog roles, you'd want per-catalog users too. I'm fine with per-cluster users. I just went through the spec yesterday, check -hackers for my email Ok, I'm going to look into that. about what CVS head supports vs. what's in the SQL spec. I don't see any particular reason why we wouldn't be able to fully support 'Basic roles' and 'Extended roles' in 8.1, I think we're quite close now... I'm looking forward to the 'SET ROLE' implementation. If the interpretation of the privileges is restricted to the current role, then I'll be happy. I still think that removing groups and having per-cluster roles is not a good idea. The better way would be to keep user/group and add per-catalog roles. There is an opportunity which is being missed, and that won't show up later. Well, I can see that I'm pretty alone to think that;-) Thanks for your answer, have a nice day, -- Fabien. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Dear Bruno, The standard talks about 2 distinct concepts: USER and ROLE (4.34). I'm not sure it is a good idea to drop the user concept to replace it by role. If you do so, you may miss something about what roles are about. I think it is a good idea to make users synonymous with roles with regard to privileges. This will make checking for access simpler and mistakes less likely. The special part of being a user in addition to a role is that being a user allows for authentication. There are two distinct issues : implementation and design/feature. I'm arguing on the feature, as I wish per-catalog ROLEs, which cannot be fused with per-cluster USERs. I agree with you that the current implementation for a per-cluster role is very reasonnable or even witty. It just does not provide the feature I'm looking for, namely managing privileges locally to a database, without interference from one database to another. -- Fabien. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Fabien Tom (if you're watching), * Fabien COELHO ([EMAIL PROTECTED]) wrote: Role right resolution starts from the user and then works backwards up the tree, with multi-level resolution. It wouldn't go past the logged in user since that's really where it starts. ISTM that the starting point should *not* be the user, but the CURRENT_ROLE, which must be something distinct: Even if I'm root, if a Err, yes, from the CURRENT_ROLE, which is what we get back from 'GetUserId()', technically I think. I'm not sure the stack has really been implemented yet (I'd expect it to be done correctly w/ the SET ROLE things). 'SET ROLE very_limited_privileges' is performed, then the privileges in effect are those of the chosen role. That is what is told by section 4.34.1.1 SQL-session authorization identifiers of the SQL 2003 specs as I understand it. Right, that's what the behavior should be. If the user is kind of a role, then I'm afraid the whole point may be missed. But maybe not, it would depend on the implementation details. No, I don't think the point will be missed at all. I certainly understand that privileges are dropped when doing SET ROLE. Really, I think SET ROLE and the associated SESSION_USER/CURRENT_USER/CURRENT_ROLE need to be implemented/looked at carefully to make sure the right things happen. Tom, if you're watching, are you working on this? I can probably spend some time today on it, if that'd be helpful. So for me we should have per-cluser users as they where up to now, per-catalog roles with the properties I described, and possibly per-cluster group just for the sake of compatibility/simplicity of the access control and managing group of users as a whole. ROLE should not replace USER/GROUP. It should be added next to it. I don't see much point in having USER or GROUP when we have roles. Indeed, if you have per-cluster ROLE, you don't need GROUP anymore. If USER is per-cluster for connection management and ROLE per-catalog for database access management, then you will need a per-cluster grouping (say for pg_hba.conf...) which is just the current GROUP. Is there something specific that you feel can't be done with roles that could be done w/ USER/GROUP? No, it is the reverse: I'm afraid that the way it seems to be heading, no more will be done with role than with group before. Already at least some of the things I was looking for with Roles can be done, such as a role with members having ownership of an object- this allows me to create 'admin' roles for a given area without having to give them superuser(). It's not perfect yet, but it's getting closer. Per-catalog roles is an interesting idea, but I'd tend to think that if you want per-catalog roles, you'd want per-catalog users too. I'm fine with per-cluster users. I'm pretty sure others have been asking about per-catalog users and if we're going to accept that per-catalog roles makes sense I'd really think per-catalog users would too. about what CVS head supports vs. what's in the SQL spec. I don't see any particular reason why we wouldn't be able to fully support 'Basic roles' and 'Extended roles' in 8.1, I think we're quite close now... I'm looking forward to the 'SET ROLE' implementation. If the interpretation of the privileges is restricted to the current role, then I'll be happy. Right, according to the SQL spec it's a 'stack', where generally the only thing visible, and what's used for permissions checking, etc, is whatever is on the top of the stack, so after a 'SET ROLE' you only have the permissions of that 'SET ROLE'. The only concern I can see here is that I'm pretty sure the SQL spec allows you to go back (Using 'SET ROLE' with no argument, or maybe 'SET ROLE NONE', I'd have to double-check). That makes sense in some instances, but not in others. There might be room to consider something like 'SET ROLE role FINAL' or some such which disallows going back, though that'd be a PG extension beyond the SQL spec I'm pretty sure. I still think that removing groups and having per-cluster roles is not a good idea. The better way would be to keep user/group and add per-catalog roles. There is an opportunity which is being missed, and that won't show up later. Well, I can see that I'm pretty alone to think that;-) I really disagree with you here. I feel it makes much more sense to do this in stages, first user/group - roles, then roles-per-catalog, which means you can then have both per-catalog 'users' and per-catalog 'groups', if you want to limit your view to that. Thanks for your answer, have a nice day, Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost [EMAIL PROTECTED] writes: Tom, if you're watching, are you working on this? I can probably spend some time today on it, if that'd be helpful. I am not; I was hoping you'd deal with SET ROLE. Is it really much different from SET SESSION AUTHORIZATION? I'm pretty sure others have been asking about per-catalog users and if we're going to accept that per-catalog roles makes sense I'd really think per-catalog users would too. We really can't do this. Especially not 3 days before feature freeze. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Tom, if you're watching, are you working on this? I can probably spend some time today on it, if that'd be helpful. I am not; I was hoping you'd deal with SET ROLE. Is it really much different from SET SESSION AUTHORIZATION? No, it's not, but it's going to need to be done carefully to make sure GetUserId() returns the correct thing at the correct time and that the other GetSessionUserId() calls are only used where they should be and that they return the correct information too. I'll work on SET ROLE and the associated CURRENT_* functions and information_schema today and tommorow. I'm pretty sure others have been asking about per-catalog users and if we're going to accept that per-catalog roles makes sense I'd really think per-catalog users would too. We really can't do this. Especially not 3 days before feature freeze. Right, I wasn't expecting that to be done in this round. It's something people have asked for though and so might be something to consider for 8.2. I'm hoping your work on CREATEROLE will stem some of that demand for per-catalog users/roles actually. I've been trying to think what else per-catalog users/roles would get us besides a segmented namespace. I think one big issue is that we don't have a 'usage' database check beyond pg_hba and so any user could get the schema definitions for any database, which kind of sucks. Is that maybe something we could try to address for 8.1? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost [EMAIL PROTECTED] writes: I think one big issue is that we don't have a 'usage' database check beyond pg_hba and so any user could get the schema definitions for any database, which kind of sucks. Not unless he can connect to it. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: I think one big issue is that we don't have a 'usage' database check beyond pg_hba and so any user could get the schema definitions for any database, which kind of sucks. Not unless he can connect to it. That's controlled by pg_hba.conf though, isn't it? The idea being that you'd like to give some people the ability to create users/roles, but to limit the databases those created users/roles could connect to by, say, requiring they have 'usage' or 'connect' permissions to that database, which could be set by the database owner; without the database owner having write permissions to the pg_hba.conf. The scenario is one of an ISP who wants to give people Postgres access but doesn't want to have to manage all the users. So, the ISP creates a database, an 'admin' role for a given customer and gives 'createrole' permissions to that admin role. The admin role can then create new roles but can only give them access to connect to their database (since that's the only one the admin either 'owns' or has 'create', etc, privileges on). I *think* (perhaps I'm wrong..) that the only thing we lack to make this work is a permissions check on the connect to a given database which can be managed by a user of the database (ie: not pg_hba.conf). Thinking about this a bit more I guess this would probably involve basically moving pg_hba.conf into the database catalogs and then having pg_hba.conf generated similar to how pg_authid is generated. That's probably too much to do for 8.1 then, I had been hoping there was a way to do it which would be a smaller change than that. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: That's controlled by pg_hba.conf though, isn't it? The idea being that you'd like to give some people the ability to create users/roles, but to limit the databases those created users/roles could connect to by, say, requiring they have 'usage' or 'connect' permissions to that database, which could be set by the database owner; without the database owner having write permissions to the pg_hba.conf. You can do that today by putting a group name in pg_hba.conf. Roles will make it more flexible; I don't see that we need anything more. For instance, if pg_hba.conf says samegroup then you could manage everything by associating a group with each database. Ahh, ok, good point. Sorry, I'd forgotten about that flexibility of pg_hba.conf. Well, hopefully this will make some ISPs happy then. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost [EMAIL PROTECTED] writes: That's controlled by pg_hba.conf though, isn't it? The idea being that you'd like to give some people the ability to create users/roles, but to limit the databases those created users/roles could connect to by, say, requiring they have 'usage' or 'connect' permissions to that database, which could be set by the database owner; without the database owner having write permissions to the pg_hba.conf. You can do that today by putting a group name in pg_hba.conf. Roles will make it more flexible; I don't see that we need anything more. For instance, if pg_hba.conf says samegroup then you could manage everything by associating a group with each database. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Users/Groups - Roles
I notice that AddRoleMems/DelRoleMems assume that ADMIN OPTION is not inherited indirectly; that is it must be granted directly to you. This seems wrong; SQL99 has under privileges 19) B has the WITH ADMIN OPTION on a role if a role authorization descriptor identifies the role as granted to B WITH ADMIN OPTION or a role authorization descriptor identifies it as granted WITH ADMIN OPTION to another applicable role for B. and in the Access Rules for grant role statement 1) Every role identified by role granted shall be contained in the applicable roles for A and the corresponding role authorization descriptors shall specify WITH ADMIN OPTION. I can't see any support in the spec for the idea that WITH ADMIN OPTION doesn't flow through role memberships in the same way as ordinary membership; can you quote someplace that implies this? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: I notice that AddRoleMems/DelRoleMems assume that ADMIN OPTION is not inherited indirectly; that is it must be granted directly to you. This seems wrong; SQL99 has under privileges 19) B has the WITH ADMIN OPTION on a role if a role authorization descriptor identifies the role as granted to B WITH ADMIN OPTION or a role authorization descriptor identifies it as granted WITH ADMIN OPTION to another applicable role for B. and in the Access Rules for grant role statement 1) Every role identified by role granted shall be contained in the applicable roles for A and the corresponding role authorization descriptors shall specify WITH ADMIN OPTION. I can't see any support in the spec for the idea that WITH ADMIN OPTION doesn't flow through role memberships in the same way as ordinary membership; can you quote someplace that implies this? Hrm, no, sorry, I just interpreted the 'Access Rules' line for grant role statement differently. That is to say: 1) Every role identified by role granted shall be contained (Alright, all the roles which you're granting, right) in the applicable roles for A and the corresponding role (A must be in all the roles which are being granted) authorization descriptors shall specify WITH ADMIN OPTION. (the grants to A for those rules specify ADMIN OPTION) This came across to me as meaning there must exist an authorization descriptor such that the granted-role equals role granted, the grantee is A and WITH ADMIN OPTION is set. That could only be true if the grant was done explicitly. Reading from 19 above (which I don't recall seeing before, or at least not reading very carefully) I think you're right. Either way is fine with me. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Attached please find files and patches associated with moving from the User/Group system currently in place to Roles, as discussed previously. I have cleaned this up a bit and committed it. I normally wouldn't commit an incomplete patch, but this change is blocking Alvaro's work on dependencies for shared objects, so I felt it was best to get the catalog changes in now. That will let Alvaro work on dependencies while I sort out the unfinished bits of roles, which I intend to do over the next day or so. Great, glad to hear it. I hope you got a chance to look over the open items in the 'milestones' file. I'd really like to see the grammar be fixed to match SQL spec for GRANT ROLE/REVOKE ROLE. I think an approach to take there might be to try and get GrantRoleStmt and GrantStmt to use the same productions at the end of the line if possible or something along those lines. Also, I've been looking through the diff between my tree and what you committed to CVS and had a couple comments (just my 2c: I think it would have been alot easier using SVN to see exaclty what was different from my patch vs. other changes since my last CVS up): First, sorry about the gratuitous name changes, it helped me find every place I needed to look at the code and think about if it needed to be changed in some way (ie: Int32GetDatum - ObjectIdGetDatum, etc). I had planned on changing some of them back to minimize the patch but kind of ran out of time. Second, looks like I missed fixing an owner check in pg_proc.c Current CVS has, line 269: if (GetUserId() != oldproc-proowner !superuser()) Which is not a sufficient owner check. This should by fixed by doing a proper pg_proc_ownercheck, ie: if (!pg_proc_ownercheck(HeapTupleGetOid(oldtup), GetUserId())) Third, I feel it's incorrect to only allow superuser() to change ownership of objects under a role-based system. Users must be able to create objects owned by a role they're in (as opposed to owned only by themselves). Without this there is no way for a given role to allow other roles to perform owner-level actions on objects which they create. The point of adding roles was to allow owner-level actions on objects to more than a single user or the superuser. Requiring the superuser to get involved with every table creation defeats much of the point. This should really be possible either by explicitly changing the ownership of an object using ALTER ... OWNER, or by a SET ROLE followed by CREATE TABLE, etc. SET ROLE is defined by the SQL specification, though we don't support it specifically yet (shouldn't be too difficult to add now though). Certainly if we accept that SET ROLE should be supported and that objects then created should be owned by the role set in SET ROLE we should be willing to support non-superusers doing ALTER ... OWNER given that they could effectively do the same thing via SET ROLE (though with much more difficulty, which has no appreciable gain). Fourth, not that I use it, but, it looks like my changes to src/interfaces/ecpg/preproc/preproc.y were lost. Not sure if that was intentional or not (I wouldn't think so... I do wish ecpg could just be the differences necessary for ecpg and be based off the main parser somehow, but that'd be a rather large change). Oh, and in that same boat, src/tools/pgindent/pgindent also appears to not have gotten the changes that I made. Many thanks for your work on this! Happy to have helped though frustrated that you seem to have removed the part that I was originally looking for. I don't feel that's justification for having it (I feel I've addressed that above) but it certainly would have been nice to be aware of that earlier and perhaps to have discussed the issues around it a bit more before being so close to the feature freeze (I know, alot my fault, but there it is). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Hi Fabien, * Fabien COELHO ([EMAIL PROTECTED]) wrote: I've looked very quickly at the patch. ISTM that the proposed patch is a reworking of the user/group stuff, which are both unified for a new role concept where a user is a kind of role and a role can be a member of another role. Well, why not. Right, it's a beginning to proper 'Role' support as defined by the SQL specification. Some added files seems not to be provided in the patch : pg_authid.h and pg_auth_members.h were attached to the email. They're also available at http://kenobi.snowman.net/~sfrost/pg_role/ ; but the patch has already been applied by Tom to CVS HEAD (well, with lots of modifications and whatnot), so you probably should just take a look at that. Anyway, from what I can see in the patch it seems that the roles are per cluster, and not per catalog. So this is not so conceptually different from user/group as already provided in pg. It's conceptually different from users/groups in that it's roles, which aren't the same thing. You're right, it's still per-cluster though. What would have been much more interesting for me would be a per catalog role, so that rights could be administrated locally in each database. I'm not sure how to provide such a feature, AFAICS the current version does not give me new abilities wrt right management. I understand your concerns here and while I agree with the basic idea that per-catalog role sets would be nice it wasn't what I had set out to do with this patch. Perhaps what you're asking for will be added later on. Some things this patch does do though are: Allow role ownership. This role can also have members, and doesn't necessairly have to be allowed to log in. Members of a role which owns an object have owner-level rights on that object (so, fe: roles user1, user2 and group1 where user1 and user2 are members of group1, a table owned by group1 can be vacuumed, have columns added/removed, have indexes create on it, etc, by user1 or user2). Allow granting roles to other roles based on the 'with admin option'. This means you don't have to be a superuser to add a member to a role which you have the 'admin option' on. There's other things (startup may be a bit faster since the pg_auth file is sorted by the backend instead of during each startup, etc) but the above were the types of things that I was looking to do mainly. I'd like to see it possible to distinguish between 'superuser' and 'createrole' permissions, but I didn't get to that point with the roles support (it's really a seperate issue anyway). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost [EMAIL PROTECTED] writes: Also, I've been looking through the diff between my tree and what you committed to CVS and had a couple comments First, sorry about the gratuitous name changes, it helped me find every place I needed to look at the code and think about if it needed to be changed in some way (ie: Int32GetDatum - ObjectIdGetDatum, etc). I had planned on changing some of them back to minimize the patch but kind of ran out of time. No problem, I figured that was why you'd done it, but changing them back helped me to understand the patch also ;-) Second, looks like I missed fixing an owner check in pg_proc.c Got it. I was wondering if there were more --- might be worth checking all the superuser() calls. Third, I feel it's incorrect to only allow superuser() to change ownership of objects under a role-based system. I took that out because it struck me as a likely security hole; we don't allow non-superuser users to give away objects now, and we shouldn't allow non-superuser roles to do so either. Moreover the tests you had were inconsistent (not same test everyplace). Users must be able to create objects owned by a role they're in (as opposed to owned only by themselves). This is what SET SESSION AUTHORIZATION/SET ROLE is for, no? You set the auth to a role you are allowed to be in, then create the object. I do notice that we don't have this yet, but it's surely a required piece of the puzzle. Fourth, not that I use it, but, it looks like my changes to src/interfaces/ecpg/preproc/preproc.y were lost. Not sure if that was intentional or not Yeah, it was. I leave it to Michael Meskes to sync ecpg with the main parser; on the occasions where I've tried to do it for him, things didn't work out well. I do wish ecpg could just be the differences necessary for ecpg and be based off the main parser somehow, Me too, but I haven't seen a way yet. src/tools/pgindent/pgindent also appears to not have gotten the changes that I made. That's an automatically generated list; there's no need to edit it. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost [EMAIL PROTECTED] writes: The code I had for this was: if (!pg_class_ownercheck(tuple,GetUserId()) || !is_role_member(newowner,GetUserId())) That needs a check for superuser though because while the test will pass on the 'pg_class_ownercheck' side, it won't on the 'is_role_member' side Um, right, that was another problem I had with it --- at one point the regression tests were failing because the superuser wasn't allowed to reassign object ownership ... I'm still fairly concerned about the security implications of letting ordinary users reassign object ownership. The fact that SET ROLE would let you *create* an object with ownership X is a long way away from saying that you should be allowed to change an *existing* object to have ownership X. This is particularly so if you are a member of a couple of different roles with different memberships: you will be able to cause objects to become effectively owned by certain other people, or make them stop being effectively owned by those people. I don't have a clear trouble case in mind at the moment, but this sure sounds like the stuff of routine security-hole reports. (Altering the ownership of a SECURITY DEFINER function, in particular, sounds like a great path for a cracker to pursue.) One place I recall seeing one and not being sure if it should be a new *_ownercheck() function or not was in the 2PC patch- twophase.c, line 380: This one I think we can leave... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Second, looks like I missed fixing an owner check in pg_proc.c Got it. I was wondering if there were more --- might be worth checking all the superuser() calls. Yeah, let's come up with a decision about what exactly we should allow and then perhaps I can go through all of the superuser() calls and see what needs fixing up. Third, I feel it's incorrect to only allow superuser() to change ownership of objects under a role-based system. I took that out because it struck me as a likely security hole; we don't allow non-superuser users to give away objects now, and we shouldn't allow non-superuser roles to do so either. Moreover the tests you had were inconsistent (not same test everyplace). Sorry about them being inconsistent, I didn't intend for them to be. I went through a couple of iterations of them trying to do the check the 'right' way. Thinking back on it, even the checks I ended up with were wrong (in the superuser case), though I think they were closer. Basically my thought was to allow the same thing you could do w/ SET ROLE, etc: If you are the owner of the object to be changed (following the normal owner checking rules) AND would still be considered the owner of the object *after* the change, then you can change the ownership. The code I had for this was: if (!pg_class_ownercheck(tuple,GetUserId()) || !is_role_member(newowner,GetUserId())) That needs a check for superuser though because while the test will pass on the 'pg_class_ownercheck' side, it won't on the 'is_role_member' side (currently anyway, I suppose a superuser could be considered to be in any role, so we could change is_role_member to always return true for superusers, that'd probably make pg_group look ugly though, either way): if (!superuser() !(pg_class_ownercheck(tupe,GetUserId()) is_role_member(newowner,GetUserId( I think that's the correct check and can be done the same way for pretty much all of the objects. Were there other security concerns you had? I'd be happy to look through the superuser() checks in commands/ and develop a patch following what I described above, as well as looking for other cases where we should be using the *_ownercheck() functions. One place I recall seeing one and not being sure if it should be a new *_ownercheck() function or not was in the 2PC patch- twophase.c, line 380: if (user != gxact-owner !superuser_arg(user)) Wasn't sure if that made sense to have *_ownercheck, or, even if we added one for it, if it made sense to check is_member_of_role() for prepared transactions. I don't think SQL has anything to say about it, anyone know what other DBs do here? Users must be able to create objects owned by a role they're in (as opposed to owned only by themselves). This is what SET SESSION AUTHORIZATION/SET ROLE is for, no? You set the auth to a role you are allowed to be in, then create the object. I do notice that we don't have this yet, but it's surely a required piece of the puzzle. (Technically I think SET SESSION AUTHORIZATION is different from SET ROLE, but anyway) Right, that's another way to do it (as I mentioned), and that lets you do ownership changes, but they're much more painful: CONNECT AS joe; CREATE TABLE abc as SELECT name,avg(a),sum(b) FROM reallybigtable; -- Whoops, I meant for abc to be owned by role C so sally can add her -- column to it later, or vacuum/analyze it, whatever GRANT SELECT ON abc TO C; -- Might not be necessary ALTER TABLE abc RENAME TO abc_temp; SET ROLE C; CREATE TABLE abc AS SELECT * FROM abc_temp; -- Could be big :( SET ROLE NONE; -- Might be just 'SET ROLE;'? Gotta check the spec DROP TABLE abc_temp; I don't really see the point in making users go through all of these hoops to do an ownership change. In the end, it's the same result near as I can tell... Yeah, it was. I leave it to Michael Meskes to sync ecpg with the main parser; on the occasions where I've tried to do it for him, things didn't work out well. Ah, ok. src/tools/pgindent/pgindent also appears to not have gotten the changes that I made. That's an automatically generated list; there's no need to edit it. Hah, silly me. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: That needs a check for superuser though because while the test will pass on the 'pg_class_ownercheck' side, it won't on the 'is_role_member' side Um, right, that was another problem I had with it --- at one point the regression tests were failing because the superuser wasn't allowed to reassign object ownership ... Yeah, sorry about that. I'm still fairly concerned about the security implications of letting ordinary users reassign object ownership. The fact that SET ROLE would let you *create* an object with ownership X is a long way away from saying that you should be allowed to change an *existing* object to have ownership X. This is particularly so if you are a member of a couple of different roles with different memberships: you will be able to cause objects to become effectively owned by certain other people, or make them stop being effectively owned by those people. I don't have a clear trouble case in mind at the moment, but this sure sounds like the stuff of routine security-hole reports. (Altering the ownership of a SECURITY DEFINER function, in particular, sounds like a great path for a cracker to pursue.) SET ROLE also lets you *drop* an object owned by that role. Or alter it. Or CREATE OR REPLACE FUNCTION ... I can understand your concern. The specific use case I'm thinking about is where a user creates an object, does some work on it, and then wants to change its ownership to be owned by a role which that user is in. I find myself doing that a fair bit (as superuser atm). One thing I don't like about limiting it to that is that you then can't go back without the whole drop/create business or getting an admin. This also isn't stuff that couldn't be done through other means, even in the SECURITY DEFINER function case, you just need to drop, set role, create. Having a role with members be able to own objects isn't meant to replace the privileges system and I don't expect people to try to use it to. I can perhaps see a special case for SECURITY DEFINER functions but if we're going to special case them I'd think we'd need to make them only be creatable/modifiable at all by superusers or add another flag to the role to allow that. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
On Tue, Jun 28, 2005 at 14:45:06 -0400, Stephen Frost [EMAIL PROTECTED] wrote: If you are the owner of the object to be changed (following the normal owner checking rules) AND would still be considered the owner of the object *after* the change, then you can change the ownership. That still isn't a good idea, because the new owner may not have had access to create the object you just gave to them. Or you may not have had access to drop the object you just gave away. That is going to be a security hole. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Users/Groups - Roles
On Tue, Jun 28, 2005 at 14:52:07 -0500, Bruno Wolff III [EMAIL PROTECTED] wrote: On Tue, Jun 28, 2005 at 14:45:06 -0400, Stephen Frost [EMAIL PROTECTED] wrote: If you are the owner of the object to be changed (following the normal owner checking rules) AND would still be considered the owner of the object *after* the change, then you can change the ownership. That still isn't a good idea, because the new owner may not have had access to create the object you just gave to them. Or you may not have had access to drop the object you just gave away. That is going to be a security hole. Thinking about it some more, drops wouldn't be an issue since the owner can always drop objects. Creating objects in particular schemas or databases is not something that all roles may be able to do. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost wrote: I can perhaps see a special case for SECURITY DEFINER functions but if we're going to special case them I'd think we'd need to make them only be creatable/modifiable at all by superusers or add another flag to the role to allow that. I agree that owner changes of SECURITY DEFINER functions seem dangerous. I would follow Stephen's idea that SECURITY DEFINER functions should only be creatable/modifiable by superusers. This would be similar to unix, where setting the suid/sgid bits is usually only allowed to root. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Bruno Wolff III ([EMAIL PROTECTED]) wrote: On Tue, Jun 28, 2005 at 14:45:06 -0400, Stephen Frost [EMAIL PROTECTED] wrote: If you are the owner of the object to be changed (following the normal owner checking rules) AND would still be considered the owner of the object *after* the change, then you can change the ownership. That still isn't a good idea, because the new owner may not have had access to create the object you just gave to them. Or you may not have had access to drop the object you just gave away. That is going to be a security hole. If you're considered the owner of an object then you have access to drop it already. You have to be a member of the role to which you're changing the ownership. That role not having permission to create the object in place is an interesting question. That's an issue for SET ROLE too, to some extent I think, do you still have your role's permissions after you've SET ROLE to another role? If not then you'd have to grant CREATE on the schema to the role in order to create objects owned by that role, and I don't think that's necessairly something you'd want to do. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost wrote: If you're considered the owner of an object then you have access to drop it already. You have to be a member of the role to which you're changing the ownership. That role not having permission to create the object in place is an interesting question. That's an issue for SET ROLE too, to some extent I think, do you still have your role's permissions after you've SET ROLE to another role? For me this would be the natural way how SET ROLE would behave. This is unix'ism again, but using setuid to become another user, you loose the privileges of the old user context. Therefore SET ROLE should not inherit privileges from the other role. This seems to be the safes approach. Nevertheless, what does the standard say? If not then you'd have to grant CREATE on the schema to the role in order to create objects owned by that role, and I don't think that's necessairly something you'd want to do. Right, that's an issue. But since the new role will be the *owner* of the object, it *should* really have create-privileges in that schema. So the above way seems to be correct anyway. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Bruno Wolff III ([EMAIL PROTECTED]) wrote: Thinking about it some more, drops wouldn't be an issue since the owner can always drop objects. Right. Creating objects in particular schemas or databases is not something that all roles may be able to do. Yeah, I'm not entirely sure what I think about this issue. If you're not allowed to change ownership of objects and SET ROLE drops your regular ROLE's privileges then the role which owns the object originally (and which you're required to be in) must have had create access to that schema at some point. I can see requiring the role that's changing the ownership to have create access to the schema in which the object that's being changed is in. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Michael Paesold ([EMAIL PROTECTED]) wrote: Stephen Frost wrote: If you're considered the owner of an object then you have access to drop it already. You have to be a member of the role to which you're changing the ownership. That role not having permission to create the object in place is an interesting question. That's an issue for SET ROLE too, to some extent I think, do you still have your role's permissions after you've SET ROLE to another role? For me this would be the natural way how SET ROLE would behave. This is unix'ism again, but using setuid to become another user, you loose the privileges of the old user context. Therefore SET ROLE should not inherit privileges from the other role. This seems to be the safes approach. Nevertheless, what does the standard say? Hmm, it says there's a stack and that the thing on top is what's currently used, so it sounds like it would drop the privs too, but imv it's not entirely clear. If not then you'd have to grant CREATE on the schema to the role in order to create objects owned by that role, and I don't think that's necessairly something you'd want to do. Right, that's an issue. But since the new role will be the *owner* of the object, it *should* really have create-privileges in that schema. So the above way seems to be correct anyway. I'm not entirely sure that you'd necessairly want the role to have create privileges on the schema even when it owns things in the schema but the more I think about it that doesn't seem all that unreasonable either. I don't think it'd be very difficult to add such a check to the ALTER OWNER code too though. In general, and perhaps as a unix'ism to some extent, I don't particularly like having to su to people. To get all the other permissions which the role has you don't have to 'su' currently, and personally I like that and think that's correct for a role-based environment (unlike unix where you have users and groups). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost [EMAIL PROTECTED] writes: * Bruno Wolff III ([EMAIL PROTECTED]) wrote: Creating objects in particular schemas or databases is not something that all roles may be able to do. Yeah, I'm not entirely sure what I think about this issue. We have a precedent, which is that RENAME checks for create rights. If you want to lean on the argument that this is just a shortcut for dropping the object and then recreating it somewhere else, then you need (a) the right to drop the object --- which is inherent in being the old owner, and (b) the right to create the new object, which means that (b1) you can become the role you wish to have owning the object, and (b2) *as that role* you would have the rights needed to create the object. Stephen's original analysis covers (a) and (b1) but not (b2). With (b2) I'd agree that it's just a useful shortcut. I don't see a need to treat SECURITY DEFINER functions as superuser-only. We've had that facility since 7.3 or so and no one has complained that it's too dangerous. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: * Bruno Wolff III ([EMAIL PROTECTED]) wrote: Creating objects in particular schemas or databases is not something that all roles may be able to do. Yeah, I'm not entirely sure what I think about this issue. We have a precedent, which is that RENAME checks for create rights. Ah, ok. Precedent is good. If you want to lean on the argument that this is just a shortcut for dropping the object and then recreating it somewhere else, then you need (a) the right to drop the object --- which is inherent in being the old owner, and (b) the right to create the new object, which means that (b1) you can become the role you wish to have owning the object, and (b2) *as that role* you would have the rights needed to create the object. Stephen's original analysis covers (a) and (b1) but not (b2). With (b2) I'd agree that it's just a useful shortcut. Right. Ok, I'll develop a patch which covers (a), (b1) and (b2). I'll also go through all of the superuser() calls in src/backend/commands/ and check for other places we may need *_ownercheck calls. I expect to have the patch done either tonight or tommorow. Thanks, Stephen signature.asc Description: Digital signature