Re: Proposal: allow database-specific role memberships

On Sun, Oct 10, 2021 at 2:29 PM Kenaniah Cerny <kenaniah(at)gmail(dot)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.

David J.