Re: public schema default ACL

On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
> * 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.

It will, but the level of breakage seems similar to that from removing
PGC_SIGHUP GUCs, which we've done in major releases without great harm.

> > 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...).

I like the idea of getting more SQL-compatible, if this presents a distinct
opportunity to do so. I do think it would be too weird to create the schema
in one database only. Creating it on demand might work. What would be the
procedure, if any, for database owners who want to deny object creation in
their databases?