Re: Proposal: allow database-specific role memberships

On Mon, 11 Oct 2021 at 11:01, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Having an ability to GRANT predefined roles within a particular database
> is certainly something that I'd considered when adding the pg_read/write
> data roles. I'm not super thrilled with the idea of adding a column to
> pg_auth_members just for predefined roles though and I'm not sure that
> such role membership makes sense for non-predefined roles. Would
> welcome input from others as to if that's something that would make
> sense or if folks have asked about that before. We'd need to carefully
> think through what this means in terms of making sure we don't end up
> with any loops too.
>

I think the ability to grant a role within a particular database would be
useful. For example, imagine I have a dev/testing instance with multiple
databases, each a copy of production modified in some way for different
testing purposes. For example, one might be scrambled data (to make the
testing data non- or less- confidential); another might be limited to data
from the last year (to reduce the size of everything); another might be
limited to 1% of all the customers (to reduce the size in a different way);
and of course these could be combined.

It’s easy to imagine that I might want to grant a user the ability to
connect to all of these databases, but to have different privileges. For
example, maybe they have read_confidential_data in the scrambled database
but not in the reduced-but-not-scrambled databases. But maybe they have a
lesser level of access to these databases, so just using the connect
privilege won't do the job.

I’ve already found it a bit weird that I can set per-role, per-database
settings (e.g search_path), and of course privileges on individual objects,
but not which roles the role is a member of.

I haven’t thought about implementation at all however. The thought occurs
to me that the union of all the role memberships in all the database should
form a directed acyclic graph. In other words, you could not have X a
member of Y (possibly indirectly) in one database while Y is a member of X
in another database; the role memberships in each database would then be a
subset of the complete graph of memberships.