Greetings Petr, all, * Petr Jelinek (petr.jeli...@2ndquadrant.com) wrote: > On 07/03/18 13:14, Stephen Frost wrote: > > * Noah Misch (n...@leadboat.com) wrote: > >> On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote: > >>> * Tom Lane (t...@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