Greetings, * Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote: > Stephen Frost wrote: > > > * Noah Misch (n...@leadboat.com) wrote: > > > > 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. > > Is a single attribute enough? I think we need two: one would authorize > to create the schema $user to the user themselves (maybe > SELF_SCHEMA_CREATE); another would automatically do so when connecting > to a database that does not have it (perhaps AUTO_CREATE_SCHEMA).
I don't see a use-case for this SELF_SCHEMA_CREATE attribute and it seems more likely to cause confusion than to be helpful. If the admin sets AUTO_CREATE_SCHEMA for a user then that's what we should do. > Now, maybe the idea of creating it as soon as a connection is > established is not great. What about creating it only when the first > object creation is attempted and there is no other schema to create in? > This avoid pointless proliferation of empty user schemas, as well as > avoid the overhead of checking existence of schem $user on each > connection. I don't see how creating schemas for roles which the admin has created with the AUTO_CREATE_SCHEMA option would be pointless. To not do so would be confusing, imo. Consider the user who logs in and doesn't realize that they're allowed to create a schema and doesn't see a schema of their own in the list- they aren't going to think "I should just try to create an object and see if a schema appears", they're going to ask the admin why they don't have a schema. * Tom Lane (t...@sss.pgh.pa.us) wrote: > Hmm. On first glance that sounds bizarre, but we do something pretty > similar for the pg_temp schemas, so it could likely be made to work. While I agree that it might not be that hard to make the code do it, since we do this for temp schemas, I still don't see real value in it and instead just a confusing system where schemas "appear" at some arbitrary point when the user happens to try to create an object without qualification. I liken this to a well-known and well-trodden feature for auto creating user home directories on Unix. Being different from that for, at best, rare use-cases which could be handled in other ways is going against POLA. If an admin is concerned about too many empty schemas or about having $user in a search_path and needing to search it, then those are entirely fixable rather easily, but those are the uncommon cases in my experience. > One issue to think about is exactly which $user we intend to make the > schema for, if we've executed SET SESSION AUTHORIZATION, or are inside > a SECURITY DEFINER function, etc etc. I'd argue that only the original > connection username should get this treatment, which may mean that object > creation can fail in those contexts. This just strengthens the "this will be confusing to our users" argument, imv. Thanks! Stephen
