Re: Proposal: allow database-specific role memberships

Greetings,

* David G. Johnston (david.g.johnston@gmail.com) wrote:
> On Monday, October 11, 2021, Stephen Frost  wrote:
> > 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."
> 
> Sure, that has a benefit.  But creating a second user for the other
> database and putting the onus on the user to use the correct credentials
> when logging into a particular database is a valid option  - it is in fact
> the status quo.  Due to the complexity of adding a whole new grant
> dimension to the system the status quo is an appealing option.  Annoyance
> factor aside it technically solves the per-database permissions problem put
> forth.

I disagree entirely that forcing users to have multiple accounts and to
deal with "using the correct one" is at all reasonable.  That's an utter
hack that results in a given user having multiple different accounts-
something that gets really ugly to deal with in enterprise deployments
which use any kind of centralized authentication system.

No, that's not a solution.  Perhaps there's another way to implement
this capability that is simpler than what's proposed here, but saying
"just give each user two accounts" isn't a solution.  Sure, it'll work
for existing released versions of PG, just like there's a lot of things
that people can do to hack around our deficiencies, but that doesn't
change that these are areas which we are lacking and where we should be
trying to provide a proper solution.

Thanks,

Stephen