* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost <sfr...@snowman.net> writes: > > * Andres Freund (and...@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
Description: Digital signature