Re: Possibility to disable `ALTER SYSTEM`

On Thu, Mar 14, 2024 at 12:37 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Tue, Feb 13, 2024 at 2:05 AM Joel Jacobson <joel(at)compiler(dot)org> wrote:
> > > Wouldn't having system wide EVTs be a generic solution which could be the
> > > infrastructure for this requested change as well as others in the same area?
> >
> > +1
> >
> > I like the wider vision of providing the necessary infrastructure to provide a solution for the general case.
>
> We don't seem to be making much progress here.
>
> As far as I can see from reading the thread, most people agree that
> it's reasonable to have some way to disable ALTER SYSTEM, but there
> are at least six competing ideas about how to do that:
>
> 1. command-line option
> 2. GUC
> 3. event trigger
> 4. extension
> 5. sentinel file
> 6. remove permissions on postgresql.auto.conf
>
> As I see it, (5) or (6) are most convenient for the system
> administrator, since they let that person make changes without needing
> to log into the database or, really, worry very much about the
> database's usual configuration mechanisms at all, and (5) seems like
> less work to implement than (6), because (6) probably breaks a bunch
> of client tools in weird ways that might not be easy for us to
> discover during patch review. (1) doesn't allow changing things at
> runtime, and might require the system administrator to fiddle with the
> startup scripts, which seems like it could be inconvenient. (2) and
> (3) seem like they put the superuser in a position to easily reverse a
> policy about what the superuser ought to do, but in the case of (2),
> that can be mitigated if the GUC can only be set in postgresql.conf
> and not elsewhere. (4) has no real advantages except for allowing core
> to maintain the fiction that we don't support this while actually
> supporting it; I think we should reject that approach outright.
>

You know it's funny, you say #4 has no advantage and should be
rejected outright, but AFAICT

a) no one has actually laid out why it wouldn't work for them,
b) and it's the one solution that can be implemented now
c) and that implementation would be backwards compatible with some set
of existing releases
d) and certainly anyone running k8s or config management system would
have the ability to install
e) and it could be custom tailored to individual deployments as needed
(including other potential commands that some systems might care
about)
f) and it seems like the least likely option to be mistaken for a
security feature
g) and also seems pretty safe wrt not breaking existing tooling (like
5/6 might do)

Looking at it, you could make the argument that #4 is actually the
best of the solutions proposed, except it has the one drawback that it
requires folks to double down on the fiction that we think extensions
are a good way to build solutions when really everyone just wants to
have everything in core.

Robert Treat
https://xzilla.net