Re: GRANT role_name TO role_name ON database_name

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