Обсуждение: GRANT role_name TO role_name ON database_name
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
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
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
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. Now I understand what you want. Maybe the db_user_namespace parameter can help: http://www.postgresql.org/docs/9.2/static/runtime-config-connection.html#GUC-DB-USER-NAMESPACE Yours, Laurenz Albe
* Albe Laurenz (laurenz.albe@wien.gv.at) wrote: > Maybe the db_user_namespace parameter can help: > http://www.postgresql.org/docs/9.2/static/runtime-config-connection.html#GUC-DB-USER-NAMESPACE I doubt it and I wouldn't encourage anyone to use it even if it happened to help in this situation.. Thanks, Stephen
Clark,
* Clark C. Evans (cce@clarkevans.com) wrote:
> I apologize for posting to -hackers; it was probably the wrong list.
I don't know about that.. It's a new feature request, not sure where
else you'd email about it.
That said, it's also a non-trivial thing to change and it would have to
be done in a way that doesn't break things for people who expect the
current behavior, and I don't immediately see an easy way to do that.
This capability might well come with a real way to have per-database
roles in general, which has been asked for quite often as well. You
would then be able to have an 'auditor' role in each database and have
them actually be different roles- would that match your needs..?
Thanks,
Stephen
On Wed, May 29, 2013, at 09:45 AM, Stephen Frost wrote: > * Albe Laurenz (laurenz.albe@wien.gv.at) wrote: > > Maybe the db_user_namespace parameter can help: > > http://www.postgresql.org/docs/9.2/static/runtime-config-connection.html#GUC-DB-USER-NAMESPACE > > I doubt it and I wouldn't encourage anyone to use it even if it happened > to help in this situation.. This feature won't help me, and I'd concur with Stephen that I wouldn't encourage its use. Auto-magical name-mangling sounds suspiciously like an application feature. The major problem isn't prefixing - you can do that in application logic easy enough. The harder problem is that this convention would have to be respected by dump/restore and create database from template. So, the application role auditor@sales would be dumped in a serialization of the "sales" database; and, when restored into "sales-testing" would become "auditor@sales-testing". Speaking of which, the choice of a @ delimiter is unfortunate, since email addresses (authenticated by Mozilla Persona) make lovely user names. If a delimiter is used for name mangling, I'd lobby for a character that is an "unwise" RFC2396 character and not a "reserved" RFC3986 character. So, perhaps the PIPE (|) or caret (^) would be good choices since those can be percent-encoded in valid emails, and don't have assigned meanings as a standard URI. Best, Clark
On Wed, May 29, 2013, at 10:08 AM, Stephen Frost wrote: > This capability might well come with a real way to have per-database > roles in general, which has been asked for quite often as well. You > would then be able to have an 'auditor' role in each database and have > them actually be different roles- would that match your needs..? Yes, if we had per-database roles, it would work. However, I don't think it's necessary. We've already got role permissions specific toa database; so we're most of the way there. The mainpiece missing is a way for me to assign a role to a user, but only for a specificdatabase. Let me rephrase this, using a different syntax... CAST <user> AS <role> ON <database> This statement permits the <user> to execute "SET ROLE <role>" when they are attached to <database>. The <user> doesn't inherit from the role, it's only a permission that enables them to SET ROLE and only when attached to the permitted database. I think this would solve my problem. Suppose again I've got a database cluster with a "sales" and an "hr" database. In this database cluster, I've got an "auditor" role which can read the audit_table in the respective database. Now, suppose I wish for Tom to be an auditor for Sales, and not for HR. I'd issue "CAST tom AS auditor ON sales". When Tom normally joins the database, he wouldn't see auditor tables (since this CAST isn't really role inheritance). But, if he wanted to see them, and he were in the Sales database, he'd issue: "SET ROLE auditor". Then he could query audit_table. On other hand, just because Tom joined the HR database to enter his timeslips, he'd not have access to audit_table; and if he did a "SET ROLE auditor" it'd fail to escalate his permission. I hope this makes sense and that it might be general enough. Best, Clark
* Clark C. Evans (cce@clarkevans.com) wrote:
> Yes, if we had per-database roles, it would work. However, I don't
> think it's necessary. We've already got role permissions specific to
> a database; so we're most of the way there.
PG has two sets of catalogs, per-databases ones and 'shared' ones.
There are role permissions in both (pg_database being one of the more
obvious 'shared' cases).
> The main piece missing
> is a way for me to assign a role to a user, but only for a specific
> database. Let me rephrase this, using a different syntax...
I'm pretty sure that I understand what you're getting at here, but I
think the direction we'd really like to go in is to have per-database
roles. There are a lot of additional advantages that would provide
along with covering your use-case. Inventing new syntax and having to
add new catalog tables without actually getting the per-DB role system
that has long been asked for seems like the wrong approach to me.
Thanks,
Stephen