Re: public schema default ACL - Mailing list pgsql-hackers

Greetings Petr, all,

* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
> On 07/03/18 13:14, Stephen Frost wrote:
> > * Noah Misch (noah@leadboat.com) wrote:
> >> On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
> >>> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >>>> 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?
> >
> > My suggestion was that this would be a role attribute.  If an
> > administrator doesn't wish for that role to have a schema created
> > on-demand at login time, they would set the 'SCHEMA_CREATE' (or whatever
> > we name it) role attribute to false.
> >
> Yeah I think role attribute makes sense, it's why I suggested something
> like DEFAULT_SCHEMA, that seems to address both schema creation (dba can
> point the schema to public for example) and also the fact that $user
> schema which is first in search_path might or might not exist.

What I dislike about this proposal is that it seems to conflate two
things- if the schema will be created for the user automatically or not,
and what the search_path setting is.  Those are two different things and
I don't think we should mix them.

> Question would be what happens if schema is then explicitly dropper (in
> either case).

I'm not sure that I see an issue with that- if it's dropped then it gets
recreated when that user logs back in.  The systems I'm aware of, as
best as I can recall, didn't have any particular check or explicit
additional behavior for such a case.

Thanks!

Stephen