Re: Possibility to disable `ALTER SYSTEM`

On Sat, Feb 10, 2024 at 9:47 PM Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> To improve the UX experience, how about first checking if the file is not writeable, or catch EACCESS, and add a user-friendly hint?
>>
>> ```
>> postgres=# ALTER SYSTEM SET wal_level TO minimal;
>> ERROR: could not open file "postgresql.auto.conf": Permission denied
>> HINT: The ALTER SYSTEM command is effectively disabled as the configuration file is set to read-only.
>> ```
>
> This would do - provided we fix the issue with pg_rewind not handling read-only files in PGDATA.
>
> This seems like the simplest solution. And maybe we should be fixing pg_rewind regardless of this issue?

Is it just pg_rewind? What about pg_basebackup, for example? Will it
preserve permissions on that file in both directory and tar-format
mode? Will any of the other tools that access the data directory via
the filesystem care about this? What about third-party backup tools,
or other third-party tools that access the data directory? I think in
general we've taken the approach so far of basically making everything
in the data directory have the same permissions as each other, and
overall either everything is only user-accessible, or it's also
group-readable, and there's a fair amount of code in various places
that assumes these things are true.

What I like about using a sentinel file for this -- I like Peter's
suggestion of postgresql.auto.conf.disabled -- is that it keeps that
property that our tools and third-party tools mostly don't need to
care about file permissions, because they're all uniform. I think it
may be simpler in the long run if we stick with that idea. I suspect
that if we deviate from it we'll slowly find bugs here and there and
have to add special-case logic in various unanticipated places to
compensate.

We can also make a GUC work, if people prefer that approach. If we go
that route, the suggestion of making it PGC_SIGHUP and
GUC_DISALLOW_IN_AUTO_FILE is a good one. When I earlier referred to
managing the GUC system with GUCs as "suspect," what I really meant
was that (1) there shouldn't be an easy way to make an end run around
the thing that's disabling ALTER SYSTEM and (2) you shouldn't be able
to use ALTER SYSTEM to disable ALTER SYSTEM. It sounds like those
flags might be strong enough to prevent that. If it turns out they're
not we can always add more flags.

It's not entirely clear to me what our wider vision is here. Some
people seem to want a whole series of flags that can disable various
things that the superuser might otherwise be able to do, which is fine
with me, except that we have no plan to disable all of the things a
superuser can do to get filesystem/OS access, and I don't think it's
possible to construct such a plan. To do so, we'd have to lock down
the superuser account to the point where it can't create functions
written in any untrusted procedural language -- in particular, C
functions -- which would preclude installing most extensions; and we'd
also have to forbid direct access to the catalogs. I think those kinds
of restrictions are basically untenable. A service provider might not
want a customer to have the ability to do those kinds of things, but
some user must retain those capabilities, at the very least to handle
emergencies. So, the solution there seems to be for the service
provider to be the superuser and the customer to not be the
super-user, rather than for the service provider and the customer to
both be super-user but with some attempt at sandboxing. I'm not trying
to kill this particular proposal, which I think is broadly reasonable,
but I'm still uncomfortable with the fact that it looks a lot like
pseudo-security.

--
Robert Haas
EDB: http://www.enterprisedb.com