Re: Possibility to disable `ALTER SYSTEM`

On 7/9/23 21:51, Gabriele Bartolini wrote:
> 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
> <http://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.
>

    Coming from a similar background to Gabriele's, I support this
proposal.

    In StackGres (https://stackgres.io) we also allow users to manage
postgresql.conf's configuration declaratively. We have a CRD (Custom
Resource Definition) that precisely defines and controls how a
postgresql.conf configuration looks like (see
https://stackgres.io/doc/latest/reference/crd/sgpgconfig/). This
configuration, once created by the user, is strongly validated by
StackGres (parameters are valid for the given major version, values are
within the ranges and appropriate types) and then periodically applied
to the database if there's any drift between that user-declared
(desired) state and current system status.

    Similarly, we also have some parameters which the user is not
allowed to change
(https://gitlab.com/ongresinc/stackgres/-/blob/main/stackgres-k8s/src/operator/src/main/resources/postgresql-blocklist.properties).
If the user is allowed to use ALTER SYSTEM and modify some of these
parameters, significant breakage can happen in the cluster, as the
operator may become "confused" and manual operation may be required,
breaking many of the user's expectations of stability and how the system
works and heals automatically.

    Sure, as mentioned elsewhere in the thread, a "malicious" user can
still use other mechanisms such as COPY or untrusted PLs to still
overwrite the configuration. But both attempts are obviously conscious
attempts to break the system (and if so, it's all yours to break it).
But ALTER SYSTEM may be an *unintended* way to break it, causing a bad
user's experience. This may be defined more of a way to avoid users
shooting themselves in the feet, inadvertedly.

    There's apparently some opposition to implementing this. But given
that there's also interest in having it, I'd like to know what the
negative effects of implementing such a startup configuration property
would be, so that advantages can be compared with the disadvantages.

    All that being said, the behavior to prevent ALTER SYSTEM can also
be easily implemented as an extension. Actually some colleagues wrote
one with a similar scope
(https://gitlab.com/ongresinc/extensions/noset), and I believe it could
be the base for a similar extension focused on preventing ALTER SYSTEM.

    Regards,

    Álvaro

--

Alvaro Hernandez

-----------
OnGres