On Mon, 11 Oct 2021 at 11:01, Stephen Frost <sfr...@snowman.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.
