Re: GRANT role_name TO role_name ON database_name

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