Re: public schema default ACL - Mailing list pgsql-hackers

Greetings Petr, all,

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

Oh, yes, we would definitely need an opt-out mechanism.  It's unclear to
me what adding a 'default schema' role option would do though that's
different from setting the search_path for a user.  I certainly wouldn't
expect it to create a new schema....

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

Yes, the schema would be created regardless of the rights of the user on
the database, because the admin set the flag on the role saying 'create
a schema for this user when they log in.'

If we think there is a use-case for saying "this user should only have
schemas in these databases, not all databases" then I could see having
the role attribute be a list of databases or "all", instead.  In the
end, I do think this is something which is controlled at the role level
and not something an individual database owner could override or
prevent, though perhaps there is some room for discussion there.

What I don't want is for this feature to *depend* on the users having
CREATE rights on the database, as that would allow them to create other
schemas (perhaps even one which is named the same as a likely new user
whose account hasn't been created yet or they haven't logged in yet...).

Thanks!

Stephen