Re: Granting SET and ALTER SYSTE privileges for GUCs

> On Mar 17, 2022, at 8:41 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
> If we abandoned that for this form of GRANT/REVOKE I think we could
> probably get away with
>
>
>     GRANT { SET | ALTER SYSTEM } ON setting_name ...
>
>
> I haven't tried it, so I could be all wrong.

Version 12 of the patch uses SET and ALTER SYSTEM as the names of the privileges, and PARAMETER as the name of the
thingon which the privilege is granted.  The catalog table which tracks these grants is now named pg_parameter_acl, and
variousother parts of the patch have been adjusted to use a "parameter" based, rather than a "setting" based, naming
scheme. One exception to this rule is the "setacl" column in pg_parameter_acl, which is much more compact than the
"parameteracl"name would be, so that remains under the old name. 

The tab completion for grants and revokes of "set" and "alter system" on parameters has been extended compared to the
previouslysubmitted patch.  The tab completion for grants and revokes of other privilege types remains fairly thin, as
Iresisted the urge to fix that generally, given that it is unrelated to this patch.  However, in making tab completion
forgrant/revoke on parameters work, this patch incidentally improves the coverage for other privilege types, in so far
asthe prefixes for those commands match the prefixes of these new commands.  

Version 12 also introduces \dcp (pneumonic, "Describe Configuration Parameter") for listing parameters, with \dcp+ also
showingthe acl, like: 

mark.dilger=# \dcp+ %client%
                               List of configuration parameters
            Parameter             | Context | Setting | Unit |       Access privileges
----------------------------------+---------+---------+------+--------------------------------
 client_connection_check_interval | user    | 0       | ms   |
 client_encoding                  | user    | UTF8    |      | "mark.dilger"=sA/"mark.dilger"
 client_min_messages              | user    | notice  |      | "mark.dilger"=sA/"mark.dilger"+
                                  |         |         |      | =s/"mark.dilger"              +
                                  |         |         |      | alice=A*/"mark.dilger"
(3 rows)

The "Access privileges" column is blank for parameters which have neither been granted nor revoked.  In the example
above,that's true for client_connection_check_interval. 

\dcp[+] only shows "user" and "superuser" parameters:

mark.dilger=# \dcp %wal%
   List of configuration parameters
        Parameter         |  Context
--------------------------+-----------
 track_wal_io_timing      | superuser
 wal_compression          | superuser
 wal_consistency_checking | superuser
 wal_init_zero            | superuser
 wal_recycle              | superuser
 wal_sender_timeout       | user
 wal_skip_threshold       | user
(7 rows)

whereas \dcpS[+] also shows all parameters (so, also "sighup", "backend", "superuser-backend", "postmaster", and
"internal".)

mark.dilger=# \dcpS %wal%
      List of configuration parameters
           Parameter           |  Context
-------------------------------+------------
 max_slot_wal_keep_size        | sighup
 max_wal_senders               | postmaster
 max_wal_size                  | sighup
 min_wal_size                  | sighup
 track_wal_io_timing           | superuser
 wal_block_size                | internal
 wal_buffers                   | postmaster
 wal_compression               | superuser
 wal_consistency_checking      | superuser
 wal_init_zero                 | superuser
 wal_keep_size                 | sighup
 wal_level                     | postmaster
 wal_log_hints                 | postmaster
 wal_receiver_create_temp_slot | sighup
 wal_receiver_status_interval  | sighup
 wal_receiver_timeout          | sighup
 wal_recycle                   | superuser
 wal_retrieve_retry_interval   | sighup
 wal_segment_size              | internal
 wal_sender_timeout            | user
 wal_skip_threshold            | user
 wal_sync_method               | sighup
 wal_writer_delay              | sighup
 wal_writer_flush_after        | sighup
(24 rows)



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