Hi, I am sending an updated patch, and submitting this to the next commit fest, as I still believe this could be very useful.
Thanks, Gabriele On Thu, 7 Sept 2023 at 21:51, Gabriele Bartolini < gabriele.bartol...@enterprisedb.com> 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, 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 > -- Gabriele Bartolini Vice President, Cloud Native at EDB enterprisedb.com
0001-Add-enable_alter_system-GUC.patch
Description: Binary data