Greetings,
* David G. Johnston (david.g.johnston@gmail.com) wrote:
> On Sun, Oct 10, 2021 at 2:29 PM Kenaniah Cerny <kenaniah@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
