Thread: Database Users Management and Privileges
Comming from Oracle, I was disapointed that the users were not "per individual database". I had to promote my database owner to superuser to make it able to create users (I didn't want to su to postgres for user creation). Is there any chance that this will change in the future ? Regarding privileges, I was also disapointed to see that the object owner rights (ALL) had to be stored when grants where made on those objects to other users. I rememeber reading something about changes in privileges storing. Is there a change regarding this in the TODO list ? Best regards, Jean-Francois Leveque ______________________________________________________________________ Sur WebMailS.com, mon adresse de courrier �lectronique gratuite. Service multilingue, s�r, et permanent. http://www.webmails.com/
Jean-Francois Leveque writes: > Comming from Oracle, I was disapointed that > the users were not "per individual database". > Is there any chance that this will change in > the future ? Most likely not. For one thing, it would be a problem to assign owners to databases. > Regarding privileges, I was also disapointed to > see that the object owner rights (ALL) had to > be stored when grants where made on those objects > to other users. I rememeber reading something > about changes in privileges storing. This has been corrected in 7.1. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
* Peter Eisentraut <peter_e@gmx.net> wrote:
|
| Jean-Francois Leveque writes:
|
| > Comming from Oracle, I was disapointed that
| > the users were not "per individual database".
|
| > Is there any chance that this will change in
| > the future ?
|
| Most likely not. For one thing, it would be a problem to assign owners to
| databases.
|
Why ? Better user management and policy delegations would be important
postgresql to succeed in enterprise environments. Maybe one should
start distinguishing logins from users like Sybase does. Logins are global
to all databases, and you can create a user for a given database and assign
it to a login. It would also be nice to be able to assign users to
groups(which in turn define access rights within the database).
regards,
Gunnar
--
Gunnar Rønning - gunnar@polygnosis.com
Senior Consultant, Polygnosis AS, http://www.polygnosis.com/
Gunnar Rønning writes: > Better user management and policy delegations would be important > postgresql to succeed in enterprise environments. Keeping compatibility is also important. > Maybe one should > start distinguishing logins from users like Sybase does. Logins are global > to all databases, and you can create a user for a given database and assign > it to a login. That doesn't strike me as terribly better. Operating system administrators tend to unify user management across the whole network. You're essentially suggesting making separate users per file system. Ugh. > It would also be nice to be able to assign users to > groups(which in turn define access rights within the database). That would indeed be nice. That's why we have already implemented it. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Gunnar R�nning wrote: > > * Peter Eisentraut <peter_e@gmx.net> wrote: > | > | Jean-Francois Leveque writes: > | > | > Comming from Oracle, I was disapointed that > | > the users were not "per individual database". > | > | > Is there any chance that this will change in > | > the future ? > | > | Most likely not. For one thing, it would be a problem to assign owners to > | databases. Why can't database owners be referenced in one table and database users (not owners) be referenced in another table with the corresponding database referenced ? They're not the same kind of users, are they ? Maybe I used Oracle too much in the past. > Why ? Better user management and policy delegations would be important > postgresql to succeed in enterprise environments. Maybe one should > start distinguishing logins from users like Sybase does. Logins are global > to all databases, and you can create a user for a given database and assign > it to a login. It would also be nice to be able to assign users to > groups(which in turn define access rights within the database). I created database user groups and I'm satisfied about users assignment to groups (See CREATE GROUP and ALTER GROUP). Regarding Privileges, I was thinking about the content of \z "Access permissions for database" results. We have a lot of "=arwR" for the object owner when we granted permissions to others. The owner obviously has all rights on his objects and I see no reason to revoke those rights. So, I think they don't have to be stored in access permissions if the PostgreSQL code can check if it's the owner asking. We wouldn't then need the '"="' anymore for not granting anything to PUBLIC. We then wouldn't need to have : "REVOKE ALL on <object> from PUBLIC;" "GRANT ALL on <object> to <owner>;" in pg_dump output. I'm not able to help on this because I'm no pgsql-hacker, but I think PostgreSQL will be better with such alteration. Maybe it's already on someone's list but I couldn't find information about such work in progress. Maybe those two changes are too much for 7.1.3, but I think they would be good candidates for 8.0 . Please tell me if I'm pushing too far, I'm not much used to this list etiquette. PostgreSQL is good, I just want it to be better. regards, Jean-Francois Leveque ______________________________________________________________________ Sur WebMailS.com, mon adresse de courrier �lectronique gratuite. Service multilingue, s�r, et permanent. http://www.webmails.com/
* Peter Eisentraut <peter_e@gmx.net> wrote:
| > Better user management and policy delegations would be important
| > postgresql to succeed in enterprise environments.
|
| Keeping compatibility is also important.
Well nobody said you can't get both ;-)
| > to all databases, and you can create a user for a given database and assign
| > it to a login.
|
| That doesn't strike me as terribly better. Operating system
| administrators tend to unify user management across the whole network.
| You're essentially suggesting making separate users per file system.
| Ugh.
Well, it is important for some networks to have the ability to create users
local to a subset of the network. Let the sub networks manage themselves.
Matter of policy of course.
| > It would also be nice to be able to assign users to
| > groups(which in turn define access rights within the database).
|
| That would indeed be nice. That's why we have already implemented it.
Oops, sorry. RTFM.... But the set of permissions you can assign to a group is
fairly limited. E.g. I can't see that you are able to grant a user/group
create/drop table permissions for a database. Does that mean any user can
create/drop tables ? I think this is an example of a permission a DBA would
like to grant to users per database.
createuser/createdb are rights assigned to a user directly. Wouldn't it make
sense to be able to assign these rights to a group of users ?
regards,
Gunnar
--
Gunnar Rønning - gunnar@polygnosis.com
Senior Consultant, Polygnosis AS, http://www.polygnosis.com/