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

> From: Greg Smith [mailto:greg@2ndQuadrant.com]
> Sent: Wednesday, August 07, 2013 6:55 AM
> To: Josh Berkus
> Cc: Stephen Frost; Bruce Momjian; Greg Stark; Andres Freund; Alvaro
> Herrera; Fujii Masao; Robert Haas; Amit Kapila; Dimitri Fontaine;
> pgsql-hackers@postgresql.org; Tom Lane
> Subject: Re: [HACKERS] 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.

I also believe this is the right way to move forward and similar thoughts of mine are shared in mail below:
http://www.postgresql.org/message-id/00a301ce91a3$09226970$1b673c50$@kapila@huawei.com

I think for unsafe parameter's, for initial patch may be we can choose limited number of parameters.
> 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.


With Regards,
Amit Kapila.