Re: Additional role attributes && superuser review

On Tue, Dec 22, 2015 at 05:23:47PM -0500, Stephen Frost wrote:
> > >> On Tue, Dec 22, 2015 at 1:41 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > >>> Updated and rebased patch attached which takes the 'pg_switch_xlog'
> > >>> default role back out, leaving us with:
> > >>>
> > >>> pg_monitor - View privileged info
> > >>> pg_backup - start/stop backups, switch xlog, create restore points
> > >>> pg_replay - Pause/resume xlog replay on replicas
> > >>> pg_replication - Create/destroy/etc replication slots
> > >>> pg_rotate_logfile - Request logfile rotation
> > >>> pg_signal_backend - Signal other backends (cancel query/terminate)
> > >>> pg_file_settings - View configuration settings in all config files

> Updated patch attached.  I'll give it another good look and then commit
> it, barring objections.

This thread and its satellite[1] have worked their way through a few designs.
At first, it was adding role attributes, alongside existing attributes like
REPLICATION and BYPASSRLS.  It switched[2] to making pg_dump preserve ACLs on
system objects.  Built-in roles joined[3] the pg_dump work to offer predefined
collections of ACL grants.  Finally, it dropped[4] the pg_dump side and
hard-coded the roles into the features they govern.

I find pg_dump support for system object ACLs to be the best of the goals
pursued here.  [5] proposed a good division of pg_dump+roles into multiple
patches, and the pg_dump support for system object ACLs belongs as the first
of the series.  There's nothing to like about today's behavior of allowing the
GRANT but omitting it from dumps, and attacking the problem at that layer will
provide considerable admin freedom.  Starting there is important, because it
will change the roles design.  I doubt we would add role pg_rotate_logfile if
"GRANT EXECUTE ON FUNCTION pg_rotate_logfile() TO mydba" were fully usable;
likewise for other roles that would carry a single ACL.  Contrary to comments
upthread, we won't be particularly free to redefine the scope of built-in
roles later.  Removing privileges from a role will be an ordinary
compatibility break, and adding privileges will be quite sensitive.

Your first patch, to catalogs.sgml, stands alone.  May as well get that out of
the way.  To answer one question you asked a few times, reserving the pg_ role
prefix is fine.  (Whether any particular pg_foo role proposal sinks or floats
is a separate question.)

To summarize, I think the right next step is to resume designing pg_dump
support for system object ACLs.  I looked over your other two patches and will
unshelve those reviews when their time comes.

Thanks,
nm

[1] http://www.postgresql.org/message-id/flat/20150508042928.GP30322@tamriel.snowman.net
[2] http://www.postgresql.org/message-id/20150402045311.GW3663@tamriel.snowman.net
[3] http://www.postgresql.org/message-id/20150429144722.GY30322@tamriel.snowman.net
[4] http://www.postgresql.org/message-id/20150508042928.GP30322@tamriel.snowman.net
[5] http://www.postgresql.org/message-id/CA+TgmobH4tdccajn7VmPT-1RqBdzLYcAz5jUz4bJ=rkqs_gADA@mail.gmail.com