Re: public schema default ACL

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)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