On Wed, May 29, 2013, at 04:26 AM, Albe Laurenz wrote: > Clark C. Evans wrote: > > I'd really love the ability to grant a *user* > > role-based privileges database by database. > > The only cluster-wide role permissions are the options > SUPERUSER, CREATEDB, CREATEROLE, INHERIT, > LOGIN and REPLICATION.
Incorrect; role-to-role membership (different from INHERIT) is also a cluster-wide role permission. Hence, I have no way to assign a user "auditor" role in one database, and not grant that same user "auditor" role in another database. > All object privileges of a role are limited to a certain database. This is indirectly false, since role membership is cluster wide. That is, a cluster-wide change in role membership cascades to a change in object permissions. By granting user Tom the Auditor role for the Sales database, I also have granted him Auditor role for the HR database. The work around we use is to mangle the roles in our system to prefix them by the database; e.g. Sales_Auditor. However, this process is very bothersome and error prone when you hit dozens of databases in a cluster. The other work around is to only use one database per cluster, but that seems silly to me. > Why can't you use a role "auditor" and give it different permissions > in different databases? Because the role defines the expected set of permissions. If someone has an "auditor" role, they should probably have the database object permissions to see the audit tables, etc. My schema are largely the same; differing mostly for administrative purposes. My applications use roles to define and limit access. I apologize for posting to -hackers; it was probably the wrong list. That said, I won't follow up till next year since I know everyone is super busy and this probably isn't high on anyone's list. Best, Clark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
