Re: Rationale for PUBLIC having CREATE and USAGE privileges on the schema "public" by default

Thanks for the reply.

> I'm not sure whether you are really being limited/forced here or if you
are thinking that having CREATE and USAGE on a schema is more powerful than
it is...

As far as I know, having these permissions has a DoS potential, though,
admittedly, negligible, if the rest of the database is secured properly.
Just wanted to play safe and revoke them.

> ... PostgreSQL isn't going to be changing its default install and almost
certainly break tons of scripts that rely upon it
>
> It is a convenience feature by definition ... that nearly all users have
come to rely upon

That's understandable, but, if deemed necessary, can't PostgreSQL break
something like this on a major release? Or is the benefit of doing so being
weighed against the added friction to upgrading?

> ... any solution at this point would require intervention on the part of
Compose
>
> Given the above, you should probably ask Compose

Have already done that. This was a bit over a year ago, actually, just
never got around to reporting it here, until now. Seems like they still
haven't addressed this.

> ... I'm not seeing where "essentially forced to use a non-public schema"
is a hardship

Now that I have thought about it some more, there's no point in using an
application-specific schema just because the "public" schema has these
permissions, because the DoS angle is still there, as long as the
permissions aren't revoked or the schema isn't deleted, so I take back the
"essentially forced to" part.

Regards,
Olegs

On Sat, Feb 17, 2018 at 2:57 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Fri, Feb 16, 2018 at 4:50 PM, Olegs Jeremejevs <olegs(at)jeremejevs(dot)com>
> wrote:
>
>> Hi,
>>
>> I'm aware that these default privileges are documented:
>>
>> https://www.postgresql.org/docs/10/static/ddl-schemas.html#
>> DDL-SCHEMAS-PRIV
>>
>> However, I'm unable to find any reasoning behind their existence.
>> Normally, one can just revoke them and move on, but they have caused me
>> some trouble in a managed deployment (Compose, where you can't login as the
>> owner of the schema or as a superuser, so it's impossible to do the
>> revocation, so you're essentially forced to use a non-public schema), and I
>> would like to understand the need for them.
>>
>> I assume they make it a bit easier for new users to try out the database,
>> and I see that they go at least as far as 7.3, but has there been any
>> discussion of removing them? And if so, why was it decided to keep them?
>>
>>
> ​No recent discussions that I recall.
>
> I'm not sure whether you are really being limited/forced here or if you
> are thinking that having CREATE and USAGE on a schema is more powerful than
> it is...
>
> In any case any solution at this point would require intervention on the
> part of Compose since PostgreSQL isn't going to be changing its default
> install and almost certainly break tons of scripts that rely upon it. No
> matter whether the original rationale still holds as strongly, whatever it
> may have been. It is a convenience feature by definition, and one that
> especially benefits new users but that nearly all users have come to rely
> upon (some may choose to immediately revoke public defaults on their
> systems but I'd assume many do not).
>
> Given the above, you should probably ask Compose to add an option to their
> system where you can request disabling of certain default privileges (or
> removal of the public schema altogether) and their system will do so, as
> superuser, on your behalf.
>
> I haven't had any issues with creating and using application-specific
> schemas and as such I'm not seeing where "essentially forced to use a
> non-public schema" is a hardship.
>
> David J.
>
>