On Wed, Mar 07, 2018 at 07:14:43AM -0500, 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.
I had in mind a site with diverse database owners, where the administrators (folks with CREATEROLE or superuser) don't know every database owner preference. If we had a SCHEMA_CREATE like you describe, I expect its documentation would say something like this: Since SCHEMA_CREATE provides the user one writable schema in each database, this allows the user to create permanent objects in any database that permits them to connect. The database owner can prevent that by creating the schema in advance of the user's first login. However, once the user has connected once, a non-superuser database owner cannot modify or drop it. Is that good enough?
