On Wed, Mar 7, 2018 at 5:11 PM, David G. Johnston <david.g.johns...@gmail.com> wrote: > I still feel like I want to mull this over more but auto-creating schemas > strikes me as being "spooky action at a distance".
I don't think that it's a terrible proposal, but I don't see it as fixing the real issue. If we do something even as simple as removing 'public' from the default search_path, then I predict that a very significant number of people will run pg_upgrade (or dump and restore, or logically replicate the database), restart their application, and find that it no longer works and they have absolutely no idea what has gone wrong or how to fix it. That seems like a major usability fail to me, and auto-creating per-user schemas does absolutely nothing to improve the situation. That's not to say that it's a bad idea; honestly, I think it's kind of nifty, and it certainly makes things a lot nicer if there's no public schema any more because it makes CREATE TABLE work out of the box again, something that we certainly want. But if we don't have some solution to the problem of upgrade => everything breaks, then I don't think we really have a good solution here. I also wonder why we're all convinced that this urgently needs to be changed. I agree that the default configuration we ship is not the most secure configuration that we could ship. However, I think it's a big step from saying that the configuration is not as secure as it could be to saying that we absolutely must change it for v11. We have shipped tons of releases with sslmode=prefer and a wide-open pg_hba.conf, and those aren't the most secure default configurations either. And changing either of those things would probably break a lot fewer users than the changes being proposed on this thread. This issue isn't something that is brand new in a recent release of PostgreSQL, and a lot of users are unaffected by it. People need to be aware that having the Donald Trump campaign and the Hilary Clinton campaign share access to the same public schema, to which both campaigns have CREATE and USAGE access, is probably asking for trouble, but to be honest I suspect a fair number of users had figured that out well before this security release went out the door. It's certainly worth considering ideas for improving PostgreSQL's security out-of-the-box, but the sky isn't falling, and it appears to me that the risk of collateral damage from changes in this area is pretty high. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company