Re: public schema default ACL

On Wed, Mar 7, 2018 at 5:11 PM, David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)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