Re: Possibility to disable `ALTER SYSTEM`

On 3/20/24 22:30, Michael Banck wrote:
>
> On Tue, Mar 19, 2024 at 10:51:50AM -0400, Tom Lane wrote:
>> Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
>>> Perhaps we could make that even better with a GUC though. I propose a
>>> GUC called 'configuration_managed_externally = true / false". If you set
>>> it to true, we prevent ALTER SYSTEM and make the error message more
>>> definitive:
>>
>>> postgres=# ALTER SYSTEM SET wal_level TO minimal;
>>> ERROR: configuration is managed externally
>>
>>> As a bonus, if that GUC is set, we could even check at server startup
>>> that all the configuration files are not writable by the postgres user,
>>> and print a warning or refuse to start up if they are.
>>
>> I like this idea. The "bonus" is not optional though, because
>> setting the files' ownership/permissions is the only way to be
>> sure that the prohibition is even a little bit bulletproof.
>
> Isn't this going to break pgbackrest restore then, which (AIUI, and was
> mentioned upthread) writes recovery configs into postgresql.auto.conf?
> Or do I misunderstand the proposal? I think it would be awkward if only
> root users are able to run pgbackrest restore. I have added David to the
> CC list to make him aware of this, in case he was not following this
> thread.

It doesn't sound like people are in favor of requiring read-only
permissions for postgresql.auto.conf, but in any case it would not be a
big issue for pgBackRest or other backup solutions as far as I can see.

pgBackRest stores all permissions and ownership so a restore by the user
will bring everything back just as it was. Restoring as root sounds bad
on the face of it, but for managed environments like k8s it would not be
all that unusual.

There is also the option of restoring and then modifying permissions
later, or in pgBackRest use the --type=preserve option to leave
postgresql.auto.conf as it is. Permissions could also be updated before
the backup tool is run and then set back.

Since this feature is intended for managed environments scripting these
kinds of changes should be pretty easy and not a barrier to using any
backup tool.

Regards,
-David