Re: public schema default ACL

Robert Haas <robertmhaas@gmail.com> writes:
> On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.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