Re: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

* Josh Berkus (josh(at)agliodbs(dot)com) wrote:
> On 08/02/2013 07:54 AM, Stephen Frost wrote:
> > Curiously, I've not heard any argument about what parameters are "safe"
> > and what aren't, though I was asked which ones I thought were safe and
> > which weren't. Perhaps looking at the specific options that would
> > likely cause PG to not start would be useful to this discussion.
>
> I really think this is the wrong approach. If we start removing
> "unsafe" parameters from ALTER SYSTEM SET, we basically hobble the
> feature to the point of uselessness. Out of the 15 or so parameters 80%
> of our users touch, half of them are on your "unsafe" list.

They're on the 'unsafe' list because they're likely to *break* things.

I'm not at *all* surprised that the list comprises 80% of the parameters
that people actually modify/use, but that doesn't mean it's smart to let
them hack at those parameters remotely w/ no access to the host system
to fix things if they screw it up, which is really the case that I'm
thinking about here because it's what we're enabling through this
mechanism. If the users already had access to the host system to go
modify the parameters in the config on the filesystem, they'd be likely
to just go *do* that, no?

> A much simpler solution to the issue Stephen proposes is to have a way
> to start up the server with all settings from ALTER SYSTEM SET disabled,
> just like some software allows you to start it up in "safe mode".

See above for why I'm not thrilled wih this approach, unless it was set
up to happen automatically, but you couldn't simply ignore *all* the
ALTER SYSTEM SET parameters because then you might not be able to
connect in due to some ALTER SYSTEM SET parameter being necessary for
remote connectivity or authentication.

> Of course, automatically disabling the *individual* parameters would be
> even better from a usability perspective. This would be equally useful
> for a manually-written postgresql.conf, i.e.:
>
> "PostgreSQL is unable to start because we couldn't allocate all of the
> memory you asked for. Starting up with shared_buffers set to 32MB.".

Right; this is part of what I was getting at with the list- there are
definitely items on that list that we could start up without, were they
misconfigured. The one question remaining from *that* however is if
there would be such a security impact from the config change that it
wouldn't be *safe* for us to do so (consider if we used our 'default'
pg_hba.conf, with 'trust' all over the place, in the event that we
couldn't load the system pg_hba.conf..).

> ... However, I'm not confident that we'll always be able to do that.
> We'd also need to have a way to "kick and scream" so that sysadmins
> would actually SEE it when the system disables a parameter.

Yes, this is also true. When it comes to a 'safe mode', my original
inclination was to only allow connections over a unix socket from a user
running as the same Unix UID as the database is running as... That's
not a solution for the Windows folks though, I'm afraid. :/

Thanks,

Stephen