Re: public schema default ACL

On 07/03/18 17:55, Stephen Frost wrote:
> Greetings Petr, all,
>
> * Petr Jelinek (petr(dot)jelinek(at)2ndquadrant(dot)com) wrote:
>> On 07/03/18 13:14, Stephen Frost wrote:
>>> * Noah Misch (noah(at)leadboat(dot)com) wrote:
>>>> On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
>>>>> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>>>>>> I wonder whether it'd be sensible for CREATE USER --- or at least the
>>>>>> createuser script --- to automatically make a matching schema. Or we
>>>>>> could just recommend that DBAs do so. Either way, we'd be pushing people
>>>>>> towards the design where "$user" does exist for most/all users. Our docs
>>>>>> comment (section 5.8.7) that "the concepts of schema and user are nearly
>>>>>> equivalent in a database system that implements only the basic schema
>>>>>> support specified in the standard", so the idea of automatically making
>>>>>> a schema per user doesn't seem ridiculous on its face. (Now, where'd I
>>>>>> put my flameproof long johns ...)
>>>>>
>>>>> You are not the first to think of this in recent days, and I'm hopeful
>>>>> to see others comment in support of this idea. For my 2c, I'd suggest
>>>>> that what we actually do is have a new role attribute which is "when
>>>>> this user connects to a database, if they don't have a schema named
>>>>> after their role, then create one." Creating the role at CREATE ROLE
>>>>> time would only work for the current database, after all (barring some
>>>>> other magic that allows us to create schemas in all current and future
>>>>> databases...).
>>>>
>>>> I like the idea of getting more SQL-compatible, if this presents a distinct
>>>> 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?
>>>
>>> My suggestion was that this would be a role attribute. If an
>>> administrator doesn't wish for that role to have a schema created
>>> on-demand at login time, they would set the 'SCHEMA_CREATE' (or whatever
>>> we name it) role attribute to false.
>>>
>> Yeah I think role attribute makes sense, it's why I suggested something
>> like DEFAULT_SCHEMA, that seems to address both schema creation (dba can
>> point the schema to public for example) and also the fact that $user
>> schema which is first in search_path might or might not exist.
>
> What I dislike about this proposal is that it seems to conflate two
> things- if the schema will be created for the user automatically or not,
> and what the search_path setting is.

Well, what $user in search_path resolves to rather than what search_path is.

> Those are two different things and
> I don't think we should mix them.

I guess I am missing the point of the schema creation for user then if
it's not also automatically the default schema for that user.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services