Re: public schema default ACL

Greetings Tom, all,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> >> I propose, for v11, switching to "GRANT USAGE ON SCHEMA
> >> public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
> >> default search_path to "$user"; that would be break more applications, and I
> >> don't see an advantage to compensate for that.
>
> > Isn't this going to cause widespread breakage? Unprivileged users
> > will suddenly find that they can no longer create tables, because
> > $user doesn't exist and they don't have permission on public. That
> > seems quite unfriendly.
>
> Well, the fundamental problem here is that the arrangements around schema
> public were set up to allow a smooth transition from the pre-7.3
> no-schemas world, not to provide any kind of security. If we want to use
> schemas for security then we're going to have to do *something* that's not
> compatible. Or we can continue to ship an insecure default configuration,
> but I recall many people arguing against that sort of choice in the past.

I concur that this is the fundamental issue and that the privilege
system around schemas weren't considered due to the desire to provide a
smooth transition, but we are quite a long way from 7.3 and there's
abundent evidence that the current defaults are insecure by default.

I'll point out that a number of our *other* defaults are also insecure
(pg_hba.conf entries with 'trust' being particulalrly bad). Those have
been worked around by packagers, but that really isn't ideal. I'd love
to see us ship an actually secure (or even just reasonable, frankly...)
default configuration.

> I wonder whether it'd be sensible for CREATE USER --- or at least the
> createuser script --- to automatically make a matching schema. Or we
> could just recommend that DBAs do so. Either way, we'd be pushing people
> towards the design where "$user" does exist for most/all users. Our docs
> comment (section 5.8.7) that "the concepts of schema and user are nearly
> equivalent in a database system that implements only the basic schema
> support specified in the standard", so the idea of automatically making
> a schema per user doesn't seem ridiculous on its face. (Now, where'd I
> put my flameproof long johns ...)

You are not the first to think of this in recent days, and I'm hopeful
to see others comment in support of this idea. For my 2c, I'd suggest
that what we actually do is have a new role attribute which is "when
this user connects to a database, if they don't have a schema named
after their role, then create one." Creating the role at CREATE ROLE
time would only work for the current database, after all (barring some
other magic that allows us to create schemas in all current and future
databases...).

Thanks!

Stephen