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 extends the system to allow, per GUC variable, the ability to SET or ALTER SYSTEM for the variable. A previous patch set was submitted [2] which created hard-coded privileged roles with the authority to manage associated hard-coded sets of GUC 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 granting ALTER 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 and in the new system catalog pg_config_param's .dat file. Failure to enter a GUC in the .dat file will result in the inability to grant privileges on the GUC, at least unless/until you run CREATE CONFIGURATION PARAMETER on the GUC. (This is, 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 complete prototyping any such thing.)