Greetings, * David G. Johnston (david.g.johns...@gmail.com) wrote: > On Sun, Oct 10, 2021 at 2:29 PM Kenaniah Cerny <kenan...@gmail.com> wrote: > > > In building off of prior art regarding the 'pg_read_all_data' and > > 'pg_write_all_data' roles, I would like to propose an extension to roles > > that would allow for database-specific role memberships (for the purpose of > > granting database-specific privileges) as an additional layer of > > abstraction. > > > > = Problem = > > > > There is currently no mechanism to grant the privileges afforded by the > > default roles on a per-database basis. This makes it difficult to cleanly > > accomplish permissions such as 'db_datareader' and 'db_datawriter' (which > > are database-level roles in SQL Server that respectively grant read and > > write access within a specific database). > > > > The recently-added 'pg_read_all_data' and 'pg_write_all_data' work > > similarly to 'db_datareader' and 'db_datawriter', but work cluster-wide. > > My first impression is that this is more complex than just restricting > which databases users are allowed to connect to. The added flexibility > this would provide has some benefit but doesn't seem worth the added > complexity.
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. Does seem like we'd probably need to change more than just what's suggested here so that you could, for example, ask "is role X a member of role Y in database Z" without actually being connected to database Z. That's just a matter of adding some functions though- the existing functions would work with just the assumption that you're asking about within the current database. I don't think "just don't grant access to those other databases" is actually a proper answer- there is certainly a use-case for "I want user X to have read access to all tables in *this* database, and also allow them to connect to some other database but not have that same level of access there." Thanks, Stephen
signature.asc
Description: PGP signature