Re: Possibility to disable `ALTER SYSTEM`

On Tue, Feb 13, 2024 at 2:05 AM Joel Jacobson <joel@compiler.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.

So what I'd like to see is a patch that implements (5), or in the
alternative (2) but with the GUC being PGC_SIGHUP and
GUC_DISALLOW_IN_AUTO_FILE. I believe there would be adequate consensus
to proceed with either of those approaches. Anybody feel like coding
it up?

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