Re: public schema default ACL

On 07/03/18 08:23, Noah Misch 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:
>>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>>> On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
>>>>> I propose, for v11, switching to "GRANT USAGE ON SCHEMA
>>>>> public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
>>>>> default search_path to "$user"; that would be break more applications, and I
>>>>> don't see an advantage to compensate for that.
>>>
>>>> Isn't this going to cause widespread breakage? Unprivileged users
>>>> will suddenly find that they can no longer create tables, because
>>>> $user doesn't exist and they don't have permission on public. That
>>>> seems quite unfriendly.
>
> It will, but the level of breakage seems similar to that from removing
> PGC_SIGHUP GUCs, which we've done in major releases without great harm.
>
>>> 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

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.

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.

TBH I would personally prefer if we got rid of search_path as GUC
completely because it makes certain aspects of DDL logical replication
and connection pooling much more complex, but that does not seem to be a
realistic change.

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

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