Additional role attributes && superuser review

Greetings,

The attached patch for review implements a few additional role
attributes (all requested by users or clients in various forums) for
common operations which currently require superuser privileges. This
is not a complete solution for all of the superuser-only privileges we
have but it's certainly good progress and along the correct path, as
shown below in a review of the existing superuser checks in the
backend.

First though, the new privileges, about which the bikeshedding can
begin, short-and-sweet format:

BACKUP:
pg_start_backup()
pg_stop_backup()
pg_switch_xlog()
pg_create_restore_point()

LOGROTATE:
pg_rotate_logfile()

MONITOR:
View detailed information regarding other processes.
pg_stat_get_wal_senders()
pg_stat_get_activity()

PROCSIGNAL:
pg_signal_backend()
(not allowed to signal superuser-owned backends)

Before you ask, yes, this patch also does a bit of rework and cleanup.

Yes, that could be done as an independent patch- just ask, but the
changes are relatively minor. One curious item to note is that the
current if(!superuser()) {} block approach has masked an inconsistency
in at least the FDW code which required a change to the regression
tests- namely, we normally force FDW owners to have USAGE rights on
the FDW, but that was being bypassed when a superuser makes the call.
I seriously doubt that was intentional. I won't push back if someone
really wants it to stay that way though.

This also includes the previously discussed changes for pgstat and
friends to use role membership instead of GetUserId() == backend_role.

Full documentation is not included but will be forthcoming, of course.

As part of working through what the best solution is regarding the
existing superuser-only checks, I did a review of more-or-less all of
the ones which exist in the backend. From that review, I came to the
conclusion (certainly one which can be debated, but still) that most
cases of superuser() checks that should be possible for non-superusers
to do are yes/no privileges, except for when it comes to server-side
COPY and CREATE TABLESPACE operations, which need a form of
directory-level access control also (patch for that will be showing up
shortly..).

For posterity's sake, here's my review and comments on the various
existing superuser checks in the backend (those not addressed above):

CREATE EXTENSION
This could be a role attribute as the others above, but I didn't
want to try and include it in this patch as it has a lot of hairy
parts, I expect.

Connect using 'reserved' slots
This is another one which we might add as another role attribute.

Only needed during recovery, where it's fine to require superuser:
pg_xlog_replay_pause()
pg_xlog_replay_resume()

Directory / File access (addressed independently):
libpq/be/fsstubs.c
lo_import()
lo_export()

commands/tablespace.c - create tablespace
commands/copy.c - server-side COPY TO/FROM

utils/adt/genfile.c
pg_read_file() / pg_read_text_file() / pg_read_binary_file()
pg_stat_file()
pg_ls_dir()

Lots of things depend on being able to create C functions. These, in
my view, should either be done through extensions or should remain the
purview of the superuser. Below are the ones which I identified as
falling into this category:

commands/tsearchcmd.c
Create text search parser
Create text search template

tcop/utility.c
LOAD (load shared library)

commands/foreigncmds.c
Create FDW, Alter FDW
FDW ownership

commands/functioncmds.c
create binary-compatible casts
create untrusted-language functions

command/proclang.c
Create procedural languages (unless PL exists in pg_pltemplate)
Create custom procedural language

commands/opclasscmds.c
Create operator class
Create operator family
Alter operator family

commands/aggregatecmds.c
Create aggregates which use 'internal' types

commands/functioncmds.c
create leakproof functions
alter function to be leakproof

command/event_trigger.c
create event trigger

Other cases which I don't think would make sense to change (and some I
wonder if we should prevent the superuser from doing, unless they have
rolcatupdate or similar...):

commands/alter.c
rename objects (for objects which don't have an 'owner')
change object schema (ditto)

commands/trigger.c
enable or disable internal triggers

commands/functioncmds.c
execute DO blocks with untrusted languages

commands/dbcommands.c
allow encoding/locale to be different

commands/user.c
set 'superuser' on a role
alter superuser roles (other options)
drop superuser roles
alter role membership for superusers
force 'granted by' on a role grant
alter global settings
rename superuser roles

utils/misc/guc.c
set superuser-only GUCs
use ALTER SYSTEM
show ALL GUCs
get superuser-only GUC info
define custom placeholder GUC

utils/adt/misc.c
reload database configuration

utils/init/postinit.c
connect in binary upgrade mode
connect while database is shutting down

Another discussion could be had regarding the superuser-only GUCs.

Thanks!

Stephen