Possibility to disable `ALTER SYSTEM`

Hi everyone,

I would like to propose a patch that allows administrators to disable
`ALTER SYSTEM` via either a runt-time option to pass to the Postgres server
process at startup (e.g. `--disable-alter-system=true`, false by default)
or a new GUC (or even both), without changing the current default method of
the server.

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.

Below you find some background information and the longer story behind this
proposal.

Sticking to the Kubernetes use case, I am primarily speaking on behalf of
the CloudNativePG open source operator (cloudnative-pg.io, of which I am
one of the maintainers). However, I am sure that this option could benefit
any operator for Postgres - an operator is the most common and recommended
way to run a complex application like a PostgreSQL database management
system inside Kubernetes.

In this case, the state of a PostgreSQL cluster (for example its number of
replicas, configuration, storage, etc.) is defined in a Custom Resource
Definition in the form of configuration, typically YAML, and the operator
works with Kubernetes to ensure that, at any moment, the requested Postgres
cluster matches the observed one. This is a very basic example in
CloudNativePG:
https://cloudnative-pg.io/documentation/current/samples/cluster-example.yaml

As I was mentioning above, in a Cloud Native environment it is expected
that workloads are secure by default. Without going into much detail, many
decisions have been made in that direction by operators for Postgres,
including CloudNativePG. The goal of this proposal is to provide a way to
ensure that changes to the PostgreSQL configuration in a Kubernetes
controlled Postgres cluster are allowed only through the Kubernetes API.

Basically, if you want to change an option for PostgreSQL, you need to
change the desired state in the Kubernetes resource, then Kubernetes will
converge (through the operator). In simple words, it’s like empowering the
operator to impersonate the PostgreSQL superuser.

However, given that we cannot force this use case, there could be roles
with the login+superuser privileges connecting to the PostgreSQL instance
and potentially “interfering” with the requested state defined in the
configuration by imperatively running “ALTER SYSTEM” commands.

For example: CloudNativePG has a fixed value for some GUCs in order to
manage a full HA cluster, including SSL, log, some WAL and replication
settings. While the operator eventually reconciles those settings, even the
temporary change of those settings in a cluster might be harmful. Think for
example of a user that, through `ALTER SYSTEM`, tries to change WAL level
to minimal, or change the setting of the log (we require CSV), potentially
creating issues to the underlying instance and cluster (potentially leaving
it in an unrecoverable state in the case of other more invasive GUCS).

At the moment, a possible workaround is that `ALTER SYSTEM` can be blocked
by making the postgresql.auto.conf read only, but the returned message is
misleading and that’s certainly bad user experience (which is very
important in a cloud native environment):

```
postgres=# ALTER SYSTEM SET wal_level TO minimal;
ERROR: could not open file "postgresql.auto.conf": Permission denied
```

For this reason, I would like to propose the option to be given to the
postgres process at startup, in order to be as less invasive as possible
(the operator could then start Postgres requesting `ALTER SYSTEM` to be
disabled). That’d be my preference at the moment, if possible.

Alternatively, or in addition, the introduction of a GUC to disable `ALTER
SYSTEM` altogether. This enables tuning this setting through configuration
at the Kubernetes level, only if the operators require it - without
damaging the rest of the users.

Before I start writing any lines of code and propose a patch, I would like
first to understand if there’s room for it.

Thanks for your attention and … looking forward to your feedback!

Ciao,
Gabriele
--
Gabriele Bartolini
Vice President, Cloud Native at EDB
enterprisedb.com