Clark C. Evans wrote: > I'd really love the ability to grant a *user* > role-based privileges database by database. > > For background, I have several databases running > in a single cluster, one database per business unit. > Each database has the same core schema with the same > basic role permissions, but with significant > customizations. Even if it were technically possible > to make them a single database, it would be unwise > for administrative reasons. Each user may have > access to any number of databases, but, within > each database may be assigned to different roles. > > For example, we may have an 'auditor' role which > gives specific access to some trigger-maintained > change history. But, a given user may only be an > auditor for the business units they are assigned. > That said, they may have other roles in other > business units. My requirements are very fluid > here and dictated by regulatory requirements. > > Currently, we work around the lack of per-database > role permissions by prefixing roles with the name > of the database. This is quite tedious though, > it requires specialized logic to overlay creation, > backups, restores, updating and deleting databases. > It's very irritating, requires custom code and > conventions, even though it works. > > About 5 years ago, I think I asked for roles to > become database specific. I know think that is a > bit draconian given the cluster-wide permission > structure used by PostgreSQL. However, perhaps > a way to make it optionally limited to a given > database would simplify my permission tracking?
The only cluster-wide role permissions are the options SUPERUSER, CREATEDB, CREATEROLE, INHERIT, LOGIN and REPLICATION. It seems to me that these are not needed in your setup. All object privileges of a role are limited to a certain database. Why can't you use a role "auditor" and give it different permissions in different databases? Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
