Re: Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET

On 8/5/13 2:36 PM, Josh Berkus wrote:
> Most of our users not on Heroku are running with superuser as the app
> user now. Like, 95% of them based on my personal experience (because
> our object permissions management sucks).

My percentage wouldn't be nearly that high. 95% of database installs
done by developers? That I could believe. But setups done by
ex-{Oracle,DB2,Sybase...} DBAs avoid superuser rights for the
application, and that's around 1/3 of the systems I see. In general I
agree that there certainly are a lot of superuser only installs out
there, I just don't think it's quite as bad as you're painting it.

The important thing to realize here is that ALTER SYSTEM SET is a
convenience function. It’s useful to the extent that it makes people
feel more comfortable with changing things in the database. We will
never stop people from shooting themselves in the foot. And if the
barriers added for that purpose are too high, like disabling changes to
shared_buffers altogether, all you’ll do is make this so restricted that
it doesn’t satisfy anyone.

The original spec I outlined for how to implement this feature allowed
disabling the whole thing. Then it was just commenting out the
includedir directive from the postgresql.conf. Allowing people to
disable use of this feature in a managed configuration environment is
important. Beyond that, I don’t expect that we’ll ever make this foolproof.

After arguing out this topic in person with Stephen last night, I’ve
lumped ideas here into three major approaches that could be followed:

1) Don’t allow changing unsafe GUCs.

2) Provide a way for the server to start with bad setting and force the
administrator to fix the problem they introduced. Some sort of
maintenance mode that only allows superuser connections would force
someone to clean up this class of problem at next restart, while still
giving them enough access to run a new ALTER SYSTEM SET command.

3) Require extra syntax to modify an unsafe GUC.

As far as fixing the bad settings goes, there’s already code in initdb
to detect how high shared_buffers and max_connections can go. Any bogus
shared_buffers/max_connections value above the kernel limits could be
worked around with that approach.

Here’s how I would try and communicate that a change is unsafe, only
allowing it after proving user is paying some attention to the danger:

# ALTER SYSTEM SET shared_buffers = ‘8GB’;
NOTICE: Changing shared_buffers only takes effect after a server restart.
ERROR: Changing shared_buffers incorrectly can prevent the server from
starting normally. Use the FORCE option to modify the value anyway.

# ALTER SYSTEM SET shared_buffers = ‘8GB’ FORCE;
NOTICE: Changing shared_buffers only takes effect after a server restart.
ALTER SYSTEM

Will bad examples pop up in the Internet that just use FORCE all the
time? Sure they will, and people will cut and paste them without paying
attention. I don't see why that possibility has to block this feature
from being adopted though. That line of thinking leads toward removing
trust authentication, because that's similarly abused with cut and paste
tutorials.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com