Re: public schema default ACL

Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com> wrote:
>> I'm not convinced, however, that this would would really move the needle
>> in terms of the general security-uneasiness about the public schema and
>> search paths.  AFAICT, in any of your proposals, the default would still
>> be to have the public schema world-writable and in the path.

> Noah's proposed change to initdb appears to involve removing CREATE
> permission by default, so I don't think this is true.

I assume that means removing *public* CREATE permissions, not the
owner's (which'd be the DB owner with the proposed changes).

> It's hard to predict how many users that might inconvenience, but I
> suppose it's probably a big number. On the other hand, the only
> alternative is to continue shipping a configuration that, by default,
> is potentially insecure. It's hard to decide which thing we should
> care more about.

Yeah.  The thing is, if we make it harder to create stuff in "public",
that's going to result in the path-of-least-resistance being to run
everything as the DB owner.  Which is better than running everything as
superuser (at least if DB owner != postgres), but still not exactly great.
Second least difficult thing is to re-grant public CREATE permissions,
putting things right back where they were.

I'm not sure how far we can expect to move things without creating a
bad on-boarding experience.  The folks who actually need cross-user
security already know what they have to do (or if not, that's a docs
problem not a code problem).  I'm inclined to think that first-time
users do not need that, though.

What potentially could move the needle is separate search paths for
relation lookup and function/operator lookup.  We have sort of stuck
our toe in that pond already by discriminating against pg_temp for
function/operator lookup, but we could make that more formalized and
controllable if there were distinct settings.  I'm not sure offhand
how much of a compatibility problem that produces.

			regards, tom lane