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.

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

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to