> From: Greg Smith [mailto:g...@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$@kap...@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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
