Re: public schema default ACL

Greeting Petr, all,

* Petr Jelinek (petr(dot)jelinek(at)2ndquadrant(dot)com) wrote:
> On 07/03/18 13:18, Stephen Frost wrote:
> > Greetings,
> >
> > * Petr Jelinek (petr(dot)jelinek(at)2ndquadrant(dot)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
> 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.

Thanks!

Stephen