Re: public schema default ACL

On 07/03/18 16:26, Stephen Frost wrote:
> 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?

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