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

> From: Greg Smith [mailto:greg(at)2ndQuadrant(dot)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(at)postgresql(dot)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.