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

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.