Re: Default Roles (was: Additional role attributes)

All,

* Stephen Frost (sfrost(at)snowman(dot)net) wrote:
> Starting a new thread, as suggested by Robert, for consideration of
> adding default roles for sets of administrative functions, therefore
> removing the need for superuser-level roles in many use-cases.

[...]

> This is part 2 and really the "guts" of the changes proposed. Part 1
> was the patch sent earlier today to change pg_stat_get_activity() to use
> a tuplestore, and this patch depends on that one. I'll rebase and
> resend after that's gone in. I did notice that Andres just pushed
> upsert though, and it wouldn't surprise me if there are now merge
> conflicts. Will address all of that tomorrow, in any case.

Here's a rebase with a few additional items as follows.

Andres suggested that we drop the REPLICATION role attribute and just
use membership in pg_replication instead. That's turned out quite
fantastically as we can now handle upgrades without breaking anything-
CREATE ROLE and ALTER ROLE still accept the attribute but simply grant
pg_replication to the role instead, and postinit.c has been changed to
check role membership similar to other pg_hba role membership checks
when a replication connection comes in. Hat's off to Andres for his
suggestion.

I've added two more default roles, since it was pointed out to me that I
hadn't exactly mimicked the role attributes originally proposed. These
are pg_rotate_logfile and pg_signal_backend. This also removes any
direct object grants to pg_admin; it now means only "all of the other
roles combined" without anything additional.

Documentation and regression tests updated.

Comments and suggestions are most welcome, as always.

Thanks!

Stephen