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

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.

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