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

On Mon, Nov 2, 2015 at 10:14 AM, 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.
>
> 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.
>
> How is it that we don't need rolcatupdate but we do need a way to shut
> off ALTER SYSTEM?  Doesn't compute, IMO.

Yeah, I quite agree.  I think ALTER SYSTEM is a heck of a lot safer
than some things we are currently allowing superusers to do.  I'd
really like there to be some kind of GUC like allow_me_to_hose_myself.
If that's not set, direct catalog modifications should fail, even as
superuser.  I don't think rolcatupdate has ever prevented that, and I
think a GUC would be better than a user-level setting, because you
might easily want to turn it off on a per-session basis.

I have not seen much evidence that the problem with ALTER SYSTEM is
more than hypothetical.  I agree that you COULD configure it so that
the database server doesn't start, but the same is true of manually
editing postgresql.conf - but now that we're mostly out from under
operating system limits on shared memory, there aren't actually that
many ways to do that.  And, unlike vi $PGDATA/postgresql.conf, ALTER
SYSTEM actually has a pretty healthy amount of sanity checking built
in:

rhaas=# alter system set wal_level = arcive;
ERROR:  invalid value for parameter "wal_level": "arcive"
HINT:  Available values: minimal, archive, hot_standby, logical.

I would be willing to wager that a lot more people will hose their
systems by avoiding ALTER SYSTEM than will do so by using it.  Has
anyone ever run across a case where this actually happened?  What GUC
did the user set to cause the problem, and to what value?  I was able
to make it happen on my MacBook by setting max_connections to 100,000,
but that's unlikely to come up much in the real world, and 50,000
worked.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company