Re: Possibility to disable `ALTER SYSTEM`

On 3/19/24 07:49, Andrew Dunstan wrote:
> 
> 
> On Tue, Mar 19, 2024 at 5:26 AM Heikki Linnakangas <hlinnaka@iki.fi 
> <mailto:hlinnaka@iki.fi>> wrote:
> 
>     I want to remind everyone of this from Gabriele's first message that
>     started this thread:
> 
>      > 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
>      > ```
> 
>     I think making the config file read-only is a fine solution. If you
>     don't want postgres to mess with the config files, forbid it with the
>     permission system.
> 
>     Problems with pg_rewind, pg_basebackup were mentioned with that
>     approach. I think if you want the config files to be managed outside
>     PostgreSQL, by kubernetes, patroni or whatever, it would be good for
>     them to be read-only to the postgres user anyway, even if we had a
>     mechanism to disable ALTER SYSTEM. So it would be good to fix the
>     problems with those tools anyway.
> 
>     The error message is not great, I agree with that. Can we improve it?
>     Maybe just add a HINT like this:
> 
>     postgres=# ALTER SYSTEM SET wal_level TO minimal;
>     ERROR:  could not open file "postgresql.auto.conf" for writing:
>     Permission denied
>     HINT:  Configuration might be managed outside PostgreSQL
> 
> 
>     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.
> 
>     (Another way to read this proposal is to rename the GUC that's been
>     discussed in this thread to 'configuration_managed_externally'. That
>     makes it look less like a security feature, and describes the intended
>     use case.)
> 
> 
> 
> 
> I agree with pretty much all of this.


+1 me too.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com