Re: public schema default ACL

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

regards, tom lane