Greetings Petr,

* Petr Jelinek ( wrote:
> On 07/03/18 17:55, Stephen Frost wrote:
> > Greetings Petr, all,
> > 
> > * Petr Jelinek ( wrote:
> >> On 07/03/18 13:14, Stephen Frost wrote:
> >>> * Noah Misch ( wrote:
> >>>> On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
> >>>>> * Tom Lane ( 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.
> Well, what $user in search_path resolves to rather than what search_path is.

Alright, that makes a bit more sense to me.  I had thought you were
suggesting we would just combine these two pieces to make up the "real"
search path, which I didn't like.

Having it replace what $user is in the search_path would be a bit
confusing, I think.  Perhaps having a new '$default' would be alright
though I'm still having a bit of trouble imagining the use-case and it
seems like we'd probably still keep the "wil this schema be created
automatically or not" seperate from this new search path variable.

> > Those are two different things and
> > I don't think we should mix them.
> I guess I am missing the point of the schema creation for user then if
> it's not also automatically the default schema for that user.

With our default search path being $user, public, it would be...



Reply via email to