PostgreSQL 15: More flexibility with setting parameters

Another nice feature just made it into PostgreSQL 15: You can now control who is allowed to set specific parameters. This gives you a lot more flexibility, especially when you are hosting PostgreSQL instances for your customers. You can delegate more control to trusted users or roles and of course, you can also revoke it later on when it is not anymore required. One goal of this is to reduce the number of tasks which require superuser privileges. As always, lets do a simple example to highlight how you might use it.

To start with, we create a new user and connect with it:

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

Which parameters is this user allowed to set? Nothing on the instance and database level:

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

Of course the user can set a new value for himself (either permanently or in the session):

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

This works for all parameters that have a context of “user” in pg_settings:

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)

For all the other parameters it does not work:

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=> 

These times are now over: From PostgreSQL 15 on you can grant setting specific parameters to users or roles:

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=> 

This also works on the instance level:

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=> 

A new catalog view comes alongside which lists all the grants for parameters:

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)

Nice, thanks to all involved.