[译文] PostgreSQL 15：设置参数更灵活

PostgreSQL 15 的另一个不错的功能是：您现在可以控制允许谁设置特定参数。这为您提供了更多的灵活性，尤其是在您为客户托管 PostgreSQL 实例时。您可以将更多控制权委托给受信任的用户或角色，当然，您也可以稍后在不再需要时撤销它。这样做的一个目标是减少需要超级用户权限的任务数量。和往常一样，让我们​​做一个简单的例子来突出你如何使用它。

首先，我们创建一个新用户并与之连接：

postgres=# create user u with login password 'u';
CREATE ROLE
postgres=# \c postgres u
You are now connected to database "postgres" as user "u".

该用户允许设置哪些参数？实例和数据库级别上没有任何内容：

postgres=> alter system set work_mem='12MB';
ERROR:  permission denied to set parameter "work_mem"
postgres=> alter database postgres set work_mem='12MB';
ERROR:  must be owner of database postgres

当然，用户可以为自己设置一个新值（永久或在会话中）：

postgres=> alter user u set work_mem='12MB';
ALTER ROLE
postgres=> set work_mem='10MB';
SET

这适用于 pg_settings 中具有“user”上下文的所有参数：

postgres=> select name,context from pg_settings where context = 'user' order by 1;
                name                 | context 
-------------------------------------+---------
 application_name                    | user
 array_nulls                         | user
 backend_flush_after                 | user
 backslash_quote                     | user
 bytea_output                        | user
 check_function_bodies               | user
 client_connection_check_interval    | user
 client_encoding                     | user
 client_min_messages                 | user
 commit_siblings                     | user
 constraint_exclusion                | user
 cpu_index_tuple_cost                | user
 cpu_operator_cost                   | user
 cpu_tuple_cost                      | user
 cursor_tuple_fraction               | user
 DateStyle                           | user
 debug_pretty_print                  | user
 debug_print_parse                   | user
...
 vacuum_cost_limit                   | user
 vacuum_cost_page_dirty              | user
 vacuum_cost_page_hit                | user
 vacuum_cost_page_miss               | user
 vacuum_failsafe_age                 | user
 vacuum_freeze_min_age               | user
 vacuum_freeze_table_age             | user
 vacuum_multixact_failsafe_age       | user
 vacuum_multixact_freeze_min_age     | user
 vacuum_multixact_freeze_table_age   | user
 wal_sender_timeout                  | user
 wal_skip_threshold                  | user
 work_mem                            | user
 xmlbinary                           | user
 xmloption                           | user
(136 rows)

对于所有其他参数，它不起作用：

postgres=> select name,context from pg_settings where context != 'user' order by 1;
                  name                  |      context      
----------------------------------------+-------------------
 allow_in_place_tablespaces             | superuser
 allow_system_table_mods                | superuser
 archive_cleanup_command                | sighup
 archive_command                        | sighup
 archive_library                        | sighup
 archive_mode                           | postmaster
 archive_timeout                        | sighup
 authentication_timeout                 | sighup
...
 wal_sync_method                        | sighup
 wal_writer_delay                       | sighup
 wal_writer_flush_after                 | sighup
 zero_damaged_pages                     | superuser
(202 rows)
postgres=> set track_counts='on';
ERROR:  permission denied to set parameter "track_counts"
postgres=> 

这些时代现在结束了：从 PostgreSQL 15 开始，您可以授予用户或角色设置特定参数的权限：

postgres=# select current_user;
 current_user
--------------
 postgres
(1 row)
postgres=# grant set on parameter track_counts to u;
GRANT
postgres=# \c postgres u
You are now connected to database "postgres" as user "u".
postgres=> set track_counts = 'on';
SET
postgres=> 

这也适用于实例级别：

postgres=# grant alter system on parameter shared_buffers to u;
GRANT
postgres=# \c postgres u
You are now connected to database "postgres" as user "u".
postgres=> alter system set shared_buffers = '129MB';
ALTER SYSTEM
postgres=> 

旁边有一个新的目录视图，其中列出了参数的所有授权：

postgres=> \c postgres
You are now connected to database "postgres" as user "u".
postgres=> select * from pg_parameter_acl;
  oid  |    parname     |               paracl                
-------+----------------+-------------------------------------
 16392 | track_counts   | {postgres=sA/postgres,u=s/postgres}
 16393 | wal_level      | {postgres=sA/postgres,u=s/postgres}
 16394 | shared_buffers | {postgres=sA/postgres,u=A/postgres}
(3 rows)

很好，感谢所有相关人员。

作者：Daniel Westermann
文章来源：https://blog.dbi-services.com/postgresql-15-more-flexibility-with-setting-parameters/