Some time ago I’ve written about the options to disable the “alter system” command in PostgreSQL. While there is nothing up to PostgreSQL 16 to do this natively, there are solutions for this requirement (see linked post). PostgreSQL 17 will change that and will come with an in-core option to disable the “alter system” command.
The parameter to control this is called “allow_alter_system”, which by default is turned to on:
postgres=# \dconfig *alter_system*
List of configuration parameters
Parameter | Value
--------------------+-------
allow_alter_system | on
(1 row)
Changing this parameter via “alter system” does not make much sense, so this will obviously generate an error:
postgres=# alter system set allow_alter_system = off;
ERROR: parameter "allow_alter_system" cannot be changed
postgres=#
If you want to change this, you need to do this directly in the configuration file:
postgres=# \! echo "allow_alter_system=off" >> $PGDATA/postgresql.auto.conf
As this parameter has a “context” of SIGHUP, a simple reload makes this change active:
postgres=# select context from pg_settings where name = 'allow_alter_system';
context
---------
sighup
(1 row)
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# \dconfig *alter_system*
List of configuration parameters
Parameter | Value
--------------------+-------
allow_alter_system | off
(1 row)
From now on, any attempt to change the system’s configuration with “alter system” will trigger an error:
postgres=# alter system set work_mem='12MB';
ERROR: ALTER SYSTEM is not allowed in this environment
This makes sense for systems, where the configuration is managed externally, e.g. by an operator or configuration management tools.
Please note that this is not considered a security feature as super users have other ways of modifying the configuration, e.g. by executing shell commands.