Re: Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET - Mailing list pgsql-hackers

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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com