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?
> >>> 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
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