FAQ
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

Search Discussions

  • Albe Laurenz at May 29, 2013 at 8:27 am

    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
  • Clark C. Evans at May 29, 2013 at 11:49 am

    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
  • Albe Laurenz at May 29, 2013 at 12:17 pm

    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
  • Stephen Frost at May 29, 2013 at 1:45 pm

    * 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 C. Evans at May 29, 2013 at 6:47 pm

    On Wed, May 29, 2013, at 09:45 AM, Stephen Frost wrote:
    * Albe Laurenz (laurenz.albe@wien.gv.at) wrote:
    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
  • Stephen Frost at May 29, 2013 at 2:08 pm
    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
  • Clark C. Evans at May 29, 2013 at 8:22 pm

    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
  • Stephen Frost at Jun 3, 2013 at 1:14 pm

    * 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 28, '13 at 5:01p
activeJun 3, '13 at 1:14p
posts9
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase