Re: database specific pg_read_all_data / pg_write_all_data

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> Fundamentally making group-role memberships per-database is a fundamental
> change that seems quite unappealing to attempt without a solid use case
> that it will enable.

Yeah, I think this would be bad from both the intellectual-complexity
and implementation-difficulty standpoints.

However ... we've had multiple requests in the past to invent
database-specific roles. I wonder if it'd suffice for Richard's
purposes to create such roles and grant them pg_read_all_data.

You can sort of do that today, in that you can muck with pg_hba.conf
or database CONNECT privileges to limit which DBs a role can log into.
But either answer works only at initial login; they don't constrain
SET ROLE, so they're not really adequate for permissions-limiting
purposes. I'm imagining a feature whereby a database-specific role
is flat out not available in other databases; can't SET ROLE to it,
can't GRANT privileges (at least on non-shared objects) to it.
Probably role membership would still be nominally global, but it
wouldn't matter if you couldn't use the role.

This might still not pass the too-much-complexity test, but it
has the advantage of being something that there's been multiple
requests for.

regards, tom lane