Granting SET and ALTER SYSTE privileges for GUCs

Hackers,

In the ongoing effort [1] to reduce the number of tasks which require operating under superuser privileges, this patch
extendsthe system to allow, per GUC variable, the ability to SET or ALTER SYSTEM for the variable.  A previous patch
setwas submitted [2] which created hard-coded privileged roles with the authority to manage associated hard-coded sets
ofGUC variables.  This current patch appears superior in several ways: 

- It allows much greater flexibility in how roles and GUCs are associated
- Custom GUC variables defined by extensions can be covered by this approach

and perhaps most importantly,

- It's what Andrew suggested

Granting SET privilege on a USERSET variable makes no practical difference, but for SUSET variables it does, and
grantingALTER SYSTEM is meaningful for all variables. The patch does not mandate that non-login roles be created for
this,but as a usage suggestion, one could define a non-login role and assign privileges for a set of GUCs, such as: 

  CREATE ROLE regress_host_resource_admin NOSUPERUSER;
  GRANT SET VALUE, ALTER SYSTEM ON
    autovacuum_work_mem, hash_mem_multiplier, logical_decoding_work_mem,
    maintenance_work_mem, max_stack_depth, min_dynamic_shared_memory,
    shared_buffers, temp_buffers, temp_file_limit, work_mem
  TO regress_host_resource_admin;

and then delegate authority to manage the set of GUCs to a non-superuser by granting membership in non-login role:

  CREATE ROLE regress_admin_member IN ROLE regress_host_resource_admin;

One disadvantage of this approach is that the GUC variables are represented both in the list of C structures in guc.c
andin the new system catalog pg_config_param's .dat file.  Failure to enter a GUC in the .dat file will result in the
inabilityto grant privileges on the GUC, at least unless/until you run CREATE CONFIGURATION PARAMETER on the GUC.
(Thisis, in fact, how extension scripts deal with the issue.)  It would perhaps be better if the list of GUCs were not
duplicated,but I wasn't clever enough to find a clean way to do that without greatly expanding the patch (nor did I
completeprototyping any such thing.) 




[1] https://www.postgresql.org/message-id/flat/F9408A5A-B20B-42D2-9E7F-49CD3D1547BC%40enterprisedb.com
[2] https://www.postgresql.org/message-id/flat/0BE45772-31CF-4BE9-9FE8-FB7D8B130240%40enterprisedb.com

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company