Default Roles (was: Additional role attributes)

All,

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 reserves the prefix 'pg_' as being for default roles.

Having these default roles also means that third party applications
(eg: check_postgres.pl) can depend on their availability in their
installation instructions and have a clear understanding of what they
provide.

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Wed, Apr 29, 2015 at 8:20 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> > On this part I have a bit of a problem -- the prefix is not really
> > reserved, is it. I mean, evidently it's still possible to create roles
> > with the pg_ prefix ... otherwise, how come the new lines to
> > system_views.sql that create the "predefined" roles work in the first
> > place? I think if we're going to reserve role names, we should reserve
> > them for real: CREATE ROLE should flat out reject creation of such
> > roles, and the default ones should be created during bootstrap.

Agreed, and done.

> This is exactly what I mean about needing separate discussion for
> separate parts of the patch. There's so much different stuff in there
> right now that objections like this won't necessarily come out until
> it's far too late to change things around.

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.

This patch is significantly reduced in complexity (it's literally less
than 1/3 of the prior patch, with the largest change being in
system_views.sql where all the REVOKE/GRANT commands are dropped) as it
doesn't modify pg_dump, at all. pg_dumpall is minimally modified by
simply not dumping out roles starting with "pg_" on 9.5 and above
systems. pg_upgrade is similairly modified to check for roles which
start with "pg_" in pre-9.5 versions and complain if found.

I'll be playing around with the patch itself, testing, etc, but what we
really need is a discussion on if anyone is concerned about reserving
"pg_" for default roles. Exactly what roles are created and what
privileges are granted to them can be tweaked easily, though there has
been little discussion and therefore, presumably, little issue with the
categories that were proposed back in October when this was proposed
with role attributes instead of default roles.

As for the previously proposed changes to pg_dump, I don't particularly
have a reason to continue with that effort unless others are interested.
The default roles proposed in this patch solve the use-cases which I had
set out to solve 6 months ago. Please let me know if there is interest
in changing pg_dump to try and preserve permissions which are set in
pg_catalog by administrators, but we've never supported that and it
might be best left as-is.

Thanks!

Stephen