Re: Possibility to disable `ALTER SYSTEM`

Hi Magnus,

On Mon, 11 Sept 2023 at 16:04, Magnus Hagander <magnus(at)hagander(dot)net> wrote:

> But to do that, there would need to be a very in-your-face warning in
> the documentation about it like "note that this only disables the
> ALTER SYSTEM command. It does not prevent a superuser from changing
> the configuration remotely using other means".
>

Although I did not include any docs in the PoC patch, that's exactly the
plan. So +1 from me.

> Yes, this is marginally harder than saying ALTER SYSTEM SET
> work_mem='1TB', but only very very marginally so. And from a security
> perspective, there is zero difference.
>

Agree, but the primary goal is not security. Indeed, security requires a
more holistic approach (and in my initial thread I deliberately did not
mention all the knobs that the operator provides, with stricter and
stricter default values, as I thought it was not relevant from a Postgres'
PoV). However, as I explained in the patch PoC thread, the change is
intended primarily to warn legitimate administrators in a configuration
managed controlled environment that ALTER SYSTEM has been disabled for that
system. These are environments where network access for a superuser is
prohibited, but still possible for local SREs to log in via the container
in the pod for incident resolution - very often this happens in high stress
conditions and I believe that this gate will help them remind that if they
want to change the settings they need to do it through the Kubernetes
resources. So primarily: usability.

Another idea to solve the problem could be to instead introduce a
> specific configuration file (either hardcoded or an
> include_final_parameter=<path> parameter, in which case patroni or the
> k8s operator could set that parameter on the command line and that
> parameter only) that is parsed *after* postgresql.auto.conf and
> thereby would override the manual settings. This file would explicilty
> be documented as intended for this type of tooling, and when you have
> a tool - whether patroni or another declarative operator - it owns
> this file and can overwrite it with whatever it wants. And this would
> also retain the ability to use ALTER SYSTEM SET for *other*
> parameters, if they want to.
>

But that is exactly the whole point of this request: disable the last
operation altogether. This option will easily give any operator (or
deployment in a configuration management scenario) the possibility to ship
a system that, out-of-the box, facilitates this one direction update of the
configuration, allowing the observed state to easily reconcile with the
desired one. Without breaking any existing deployment.

> Stopping ALTER SYSTEM SET without actually preventing the superuser
> from doing the same thing as they were doing before would to me be at
> least as much of a hack as what patroni does today is.
>

Agree, but as I said above, that'd be at that point the role of an
operator. An operator, at that point, will have the possibility to
configure this knob in conjunction with others. A possibility that Postgres
is not currently giving.

Postgres itself should be able to give this possibility, as these
environments demand Postgres to address their emerging needs.

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