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