The PostgreSQL community debates ALTER SYSTEM

The PostgreSQL server is a complex beast that can be extensively configured and tuned for the environment in which it runs. There are, naturally, many configuration parameters available for administrators to work with. There are also two ways to set those parameters. In most deployments, perhaps, administrators will edit the postgresql.conf file to configure the system as needed. It is, however, also possible to adjust parameters within a running database with the ALTER SYSTEM command. Any changes made that way will be saved to a separate postgresql.auto.conf file, which is also read by the server at startup; these changes are thus persistent.

Disabling ALTER SYSTEM

In early September 2023, Gabriele Bartolini raised the idea of allowing administrators to disable ALTER SYSTEM, either via a command-line parameter or a configuration option in its own right (which, using PostgreSQL jargon, he calls a "GUC", standing for "grand unified configuration" parameter).

The main reason is that this would help improve the "security by default" posture of Postgres in a Kubernetes/Cloud Native environment - and, in general, in any environment on VMs/bare metal behind a configuration management system in which changes should only be made in a declarative way and versioned like Ansible Tower, to cite one.

PostgreSQL core contributor Tom Lane quickly expressed his disagreement with this proposal: "I don't think we need random kluges added to the permissions system. I especially don't believe in kluges to the effect of 'superuser doesn't have all permissions anymore'." He suggested using event triggers to implement such a restriction locally if it is really wanted.

Alvaro Herrera pointed out that ALTER SYSTEM, as a system-wide command, does not invoke event triggers. He also more thoroughly explained the use case that was driving this request:

I've read that containers people consider services in a different light than how we've historically seen them; they say "cattle, not pets". This affects the way you think about these services. postgresql.conf (all the PG configuration, really) is just a derived file from an overall system description that lives outside the database server. You no longer feed your PG servers one by one, but rather they behave as a herd, and the herder is some container supervisor (whatever it's called).

Ensuring that the configuration state cannot change from within is important to maintain the integrity of the service. If the user wants to change things, the tools to do that are operated from outside.

Lane, though, described the feature as "false security", and the discussion wound down for a while. But the fundamental disconnect had already become apparent: the opposition to restricting ALTER SYSTEM was based on the idea that it was intended as a security feature. As such, it would be a failure, since there are many other ways for a PostgreSQL user with the ability to run ALTER SYSTEM to take control of the server. But, as Bartolini said, this restriction is meant as a usability feature, closing off a configuration mechanism that is not meant to be used in systems where declarative configuration systems are in charge.

The new year

Robert Haas restarted the conversation in January, acknowledging that the proposal is not meant as a security feature, but worrying that it would be seen that way anyway:

I have to admit that I'm a little afraid that people will mistake this for an actual security feature and file bug reports or CVEs about the superuser being able to circumvent these restrictions. If we add this, we had better make sure that the documentation is extremely clear about what we are guaranteeing, or more to the point about what we are not guaranteeing.

Even then, he worried, about "security researchers threatening to publish our evilness in the Register". Lane then declared that the project "should reject not only this proposal, but any future ones that intend to prevent superusers from doing things that superusers normally could do". Haas responded, though, that the original proposal might have merit, and should be taken seriously.

The conversation continued inconclusively; two months later, Haas complained that not much progress was being made. That notwithstanding, he said: "As far as I can see from reading the thread, most people agree that it's reasonable to have some way to disable ALTER SYSTEM". There were, though, six competing ways in which that objective could be accomplished. These included the command-line option and configuration parameter originally proposed, along with an event trigger, pushing it into an extension module, recognizing a sentinel file created by the administrator, or just changing the permissions on postgresql.auto.conf. He suggested that the configuration option and the sentinel file were the most viable options.

Lane answered that any such restriction, implemented by any of the above mechanisms, could still be bypassed by a hostile administrator. Haas replied that the proposal was not a security feature, but Lane dismissed it as "a loaded foot-gun painted in kid-friendly colors" that would lead to more bogus CVE numbers being filed against the project.

A new patch

On March 15, Jelte Fennema-Nio posted a patch implementing the restriction as a configuration parameter. It was, in the end, just an updated version of the patch posted by Bartolini in September with some documentation tweaks. Various comments resulted in a number of newer versions; the sixth of which came out a few days later. At this point, the patch consisted mostly of documentation, including this admonition:

Note that this setting cannot be regarded as a security feature. It only disables the ALTER SYSTEM command. It does not prevent a superuser from changing the configuration remotely using other means.

Haas welcomed this version and asked whether there was a consensus on proceeding with it. Lane seemingly had changed his view somewhat at this point, saying: "I never objected to the idea of being able to disable ALTER SYSTEM". Bruce Momjian, instead, worried that an administrator could enter an ALTER SYSTEM command disabling ALTER SYSTEM, at which point recovery could be difficult. In fact, as Fennema-Nio answered, that parameter cannot be set that way, so that particular trap does not exist.

Momjian also took issue with the name of the parameter (which was externally_managed_configuration), saying that it didn't really describe what was being restricted. He suggested sql_alter_system_vars as an alternative. Haas agreed with the complaint, but thought that allow_alter_system made more sense. That is, in the end, the name that was chosen for this option.

The discussion was not over yet, though. Lane wanted the server to ensure that postgresql.conf and postgresql.auto.conf were not writable by the postgres user if allow_alter_system is disabled. Otherwise, the database administrator would still be able to modify the configuration simply by editing one of the files. Fennema-Nio disagreed, saying that disabling ALTER SYSTEM was sufficient for the intended use case, but Lane said that the configuration parameter on its own is "a fig leaf that might fool incompetent auditors but no more". Fennema-Nio reminded Lane that the point of allow_alter_system is not security. Haas complained that: "I don't understand why people who hate the feature and hope it dies in a fire get to decide how it has to work." The file-permission check was, in the end, not added.

Even then, the discussion was not quite done; Momjian questioned merging this change so late in the PostgreSQL development cycle. "My point is that we are designing the user API in the last weeks of the commitfest, which usually ends badly for us". Fennema-Nio pointed out that the API was essentially unchanged from its initial, September form, and that months had been spent discussing alternatives. Haas said that such a small patch would not improve by being held up for another release cycle: "I think it has to be right to get this done while we're all thinking about it and the issue is fresh in everybody's mind."

On March 28, Momjian agreed that the patch could be merged. One day later, Haas did exactly that. And, with that, one of the longest-running development discussions in recent PostgreSQL history came to an end. The success of this effort is a testament to the persistence of a small number of developers who saw it through months of opposition and "helpful" implementation suggestions. Having decided that difficult issue, the project can turn its attention to the small list of simple topics to be resolved during the upcoming July commitfest.