Re: ALTER SYSTEM vs symlink

On Mon, Nov 2, 2015 at 10:14 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> * Andres Freund (andres(at)anarazel(dot)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