Horton

Re: Possibility to disable `ALTER SYSTEM`

David Steele <david@pgmasters.net>

View thread
From: David Steele <david@pgmasters.net>
To: Michael Banck <mbanck@gmx.net>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Heikki Linnakangas <hlinnaka@iki.fi>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Jelte Fennema-Nio <postgres@jeltef.nl>, Daniel Gustafsson <daniel@yesql.se>, Bruce Momjian <bruce@momjian.us>, Joel Jacobson <joel@compiler.org>, Andrew Dunstan <andrew@dunslane.net>, Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>, Magnus Hagander <magnus.hagander@redpill-linpro.com>, Maciek Sakrejda <m.sakrejda@gmail.com>, Robert Haas <robertmhaas@gmail.com>
Date: 2024-03-20T21:21:08Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add allow_alter_system GUC.

  2. Rename COMPAT_OPTIONS_CLIENT to COMPAT_OPTIONS_OTHER.

  3. Remove support for version-0 calling conventions.

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@iki.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