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.