Re: Granting SET and ALTER SYSTE privileges for GUCs

> On Mar 17, 2022, at 8:41 AM, Andrew Dunstan <andrew(at)dunslane(dot)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 thing on which the privilege is granted. The catalog table which tracks these grants is now named pg_parameter_acl, and various other 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 previously submitted patch. The tab completion for grants and revokes of other privilege types remains fairly thin, as I resisted the urge to fix that generally, given that it is unrelated to this patch. However, in making tab completion for grant/revoke on parameters work, this patch incidentally improves the coverage for other privilege types, in so far as the 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 showing the 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)