Re: public schema default ACL - Mailing list pgsql-hackers
From | Stephen Frost |
---|---|
Subject | Re: public schema default ACL |
Date | |
Msg-id | 20180307171732.GP2416@tamriel.snowman.net Whole thread Raw |
In response to | Re: public schema default ACL (Petr Jelinek <petr.jelinek@2ndquadrant.com>) |
List | pgsql-hackers |
Greetings Petr, * Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote: > On 07/03/18 17:55, Stephen Frost wrote: > > 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. > > 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... Thanks! Stephen
pgsql-hackers by date: