On 07/03/18 16:26, Stephen Frost wrote: > Greeting Petr, all, > > * Petr Jelinek (petr.jeli...@2ndquadrant.com) wrote: >> On 07/03/18 13:18, Stephen Frost wrote: >>> Greetings, >>> >>> * Petr Jelinek (petr.jeli...@2ndquadrant.com) wrote: >>>> Certain "market leader" database behaves this way as well. I just hope >>>> we won't go as far as them and also create users for schemas (so that >>>> the analogy of user=schema would be complete and working both ways). >>>> Because that's one of the main reasons their users depend on packages so >>>> much, there is no other way to create a namespace without having to deal >>>> with another user which needs to be secured. >>> >>> I agree that we do *not* want to force role creation on schema creation. >>> >>>> One thing we could do to limit impact of any of this is having >>>> DEFAULT_SCHEMA option for roles which would then be the first one in the >>>> search_path (it could default to the role name), that way making public >>>> schema work again for everybody would be just about tweaking the roles a >>>> bit which can be easily scripted. >>> >>> I don't entirely get what you're suggesting here considering we already >>> have $user, and it is the first in the search_path..? >>> >> >> What I am suggesting is that we add option to set user's default schema >> to something other than user name so that if people don't want the >> schema with the name of the user auto-created, it won't be. > > We have ALTER USER joe SET search_path already though..? And ALTER > DATABASE, and in postgresql.conf? What are we missing?
That will not change the fact that we have created schema joe for that user though. While something like CREATE USER joe DEFAULT_SCHEMA foobar would. My point is that I don't mind if we create schemas for users by default, but I want simple way to opt out. > >>>>> 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? >>>> >>>> Well, REVOKE CREATE ON DATABASE already exists. >>> >>> That really isn't the same.. In this approach, regular roles are *not* >>> given the CREATE right on the database, the system would just create the >>> schema for them on login automatically if the role attribute says to do >>> so. >> >> What's the point of creating schema for them if they don't have CREATE >> privilege? > > They would own the schema and therefore have CREATE and USAGE rights on > the schema itself. Creating objects checks for schema rights, it > doesn't check for database rights- that's only if you're creating > schemas. > Yes, but should the schema for them be created at all if they don't have CREATE privilege on the database? If yes then I have same question as Noah, how does dba prevent object creation in their databases? -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
