Re: ALTER SYSTEM vs symlink - Mailing list pgsql-hackers

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Andres Freund (andres@anarazel.de) wrote:
> >> You can just revoke permissions on the file if necessary. Results in the
> >> expected
> >> ERROR:  XX000: could not open file "../postgresql.auto.conf": Permission denied
>
> > Yes, I know, but that's a really grotty way of offering a way to disable
> > ALTER SYSTEM.  It's also not exactly intuitive to someone reading the
> > release notes or working on upgrading their existing postgresql.conf.
>
> While I won't stand in the way if someone is dead set on providing a
> disable switch for ALTER SYSTEM, I fail to see the point of one.  It's
> a superuser-only feature to begin with, and if you are handing out
> superuser on production-critical installations to people you don't trust
> completely, you need to have your head examined.

There's a few different pieces to this.

The first is that there is no superuser without a running database and
there's a difference between the perfect admin and reality.  A DBA may
be trusted to be a superuser and to connect to the database to perform
superuser operations without fully understanding the shared_buffers
parameter or the requirements it places on the system.  Perhaps that
implies that they shouldn't be a superuser, but it's far different to
say "ALTER SYSTEM SET shared_buffers = X;" and "DELETE FROM pg_proc;" to
even minimally experienced DBAs, though both can lead to a PG instance
which can't be started.  As I argued on the ALTER SYSTEM thread, there
may be an organizational role distinction between the roles which are
responsible for starting the database and making sure that it's running
and the set of individuals who have superuser rights.  This is quite
common in environments where there is a central management (CM) system
where everything in /etc is really under the OPs perview, as is making
sure that things are running.  We made things more difficult for them
when we added ALTER SYSTEM because it's no longer the case that the GUCs
which can only be set through postgresql.conf can, well, only be set
through postgresql.conf.  Even a superuser can't control what
postgresql.conf is used to start or to make changes directly to the
postgresql.conf if it's owned by root or another user.

The second is that through shared_preload_libraries and the various
hooks which are available, it's possible to load libraries which do
reduce the capabilities of the superuser in PG.  If the superuser can
still use ALTER SYSTEM and remove those libraries from
shared_preload_libraries then that approach to limiting what actions can
be performed doesn't, ultimately, help.  Such a library could hook into
standard_ProcessUtility to deal with that, but a way to configure
postgresql.conf would be much cleaner.  A deeper hook in the
ALTER SYSTEM might be another approach, or one in the GUC system to
allow external libraries to control what various GUCs can be set to
(either via ALTER SYSTEM or through regular SET calls).

Lastly, I've long been a strong advocate of reducing the set of actions
only a superuser can perform by allowing non-superusers access to
more, by extending our privilege system to be more fine-grained.  Adding
more superuser-only operations goes against the grain a bit.  I'm not
suggesting that it would make sense for ALTER SYSTEM to be non-superuser
or that we shouldn't add superuser-only options, just that I'd like us
to offer more control during initial configuration of the system, to the
point where it's very clear what actions are allowed during ongoing
operations.

> As a directly comparable example, I note that you yourself were in favor
> of getting rid of rolcatupdate, which was the only mechanism we ever had
> that could prevent a superuser from destroying the catalogs entirely
> with a mistyped update --- consider "DELETE FROM pg_proc", for example,
> which unlike ALTER SYSTEM there is simply no way to recover from.

I was an advocate of getting rid of rolcatupdate because it wasn't
fully implemented and that didn't appear likely to change.  Further, I
can see cases where that could end up being more a problem than a help-
where the catalogs have been corrupted in some part and need to be
fixed.

> How is it that we don't need rolcatupdate but we do need a way to shut
> off ALTER SYSTEM?  Doesn't compute, IMO.

I'd like the ability to control all of the above, ultimately.  I don't
believe that we should be allowing the superuser to always modify the
catalog directly- and things like the sepgsql module can actually
address that and limit when the superuser is allowed to with better
granularity then what rolcatupdate provided (or was ever likely to
provide, being a single boolean role attribute).

Thanks!

Stephen