Re: Possibility to disable `ALTER SYSTEM`

On Wed, Mar 20, 2024 at 8:04 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Wed, Mar 20, 2024 at 11:07 AM Jelte Fennema-Nio <postgres(at)jeltef(dot)nl>
> wrote:
> > > Ugh, please let's not do this. This was bouncing around in my head
> last night, and this is really a quite radical change - especially just to
> handle the given ask, which is to prevent a specific command from running.
> Not implement a brand new security system. There are so many ways this
> could go wrong if we start having separate permissions for some of our
> files. In addition to backups and other tools that need to write to the
> conf files as the postgres user, what about systems that create a new
> cluster automatically e.g. Patroni? It will now need elevated privs just to
> create the conf files and assign the new ownership to them. Lots of moving
> pieces there and ways things could go wrong. So a big -1 from me, as they
> say/ :)
> >
> > Well put. I don't think the effort of making all tooling handle this
> > correctly is worth the benefit that it brings. afaict everyone on this
> > thread that actually wants to use this feature would be happy with the
> > functionality that the current patch provides (i.e. having
> > postgresql.auto.conf writable, but having ALTER SYSTEM error out).
>
> Yeah, I agree with this completely. 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.
>
> And also, if we verify that the configuration files are all read-only
> at the OS level, that also prevents the external tool from managing
> them. Well, it can: it can make them non-read-only after server start,
> then modify them, then make them read-only again, and it can make sure
> that if the system crashes, it again marks them read-only before
> trying to start PG. But it seems quite obvious that this will be
> inconvenient and difficult to get right. I find it quite easy to
> understand the idea that someone wants the PostgreSQL configuration to
> be managed by Kubernetes rather than via by ALTER SYSTEM, but I can't
> think of any scenario when you just don't want to be able to manage
> the configuration at all. Who in the world would want that?
>

Yeah, I don't see why it's our responsibility to decide what permissions
people should have on their config files.

I would argue that having the default permissions not allow postgres to
edit it's own config files *except* for postgresql.auto.conf would be a
better default than what we have now, but that's completely independent of
the patch being discussed on this thread. (And FWIW also already solved on
debian-based platforms for example, which but the main config files in /etc
with postgres only having read permissions on them - and having the
*packagers* adapt such things for their platforms in general seems like a
better place).

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>