Image

We begin at allow_alter_system, which is both new and politically fraught — so let’s start with a fight.

ALTER SYSTEM was added in 9.4 as a quality-of-life improvement: set GUCs from an SQL prompt, have the values written into postgresql.auto.conf, no shell access required. It was immediately controversial among people running PostgreSQL under configuration management. If Ansible owns postgresql.conf but a superuser quietly runs ALTER SYSTEM SET work_mem = '1GB', the next Ansible run doesn’t touch postgresql.auto.conf and the drift persists indefinitely. Nobody notices until something breaks.

This argument took roughly a decade to resolve. PostgreSQL 17 shipped allow_alter_system, a boolean that — when set to off — causes ALTER SYSTEM to return ERROR: ALTER SYSTEM is not allowed in this environment. Default on, context sighup, so flipping it requires only a config reload.

It is not a security feature. Say it with me: it is not a security feature. The docs say so explicitly, several times, and they are correct. A superuser determined to modify configuration outside your management system has no shortage of options — COPY ... FROM PROGRAM, untrusted PL languages, or, you know, shell access to the data directory. allow_alter_system = off stops accidental drift from a well-intentioned superuser who reached for the SQL command instead of filing a ticket. That’s it. That’s the whole feature.

Two wrinkles worth knowing:

  • You cannot set allow_alter_system via ALTER SYSTEM. It’s listed alongside data_directory and preset options as forbidden. The authors anticipated the obvious joke.
  • Turning the setting off does not invalidate existing entries in postgresql.auto.conf. Whatever was previously set via ALTER SYSTEM remains in effect. If you’re adopting this on an existing system, audit postgresql.auto.conf first.

Recommendation: If your configuration is managed by Ansible, Patroni, an operator, or any other external tool, set allow_alter_system = off. Audit postgresql.auto.conf before you do. Leave it on everywhere else — on a hand-tuned server it’s an annoyance, not a safeguard.

(This is #1 in a series on every PostgreSQL GUC as of version 18, in alphabetical order.)