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?
Best,
Clark