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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 to
a database; so we're most of the way there. 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...

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* 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