Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas - Mailing list pgsql-hackers

On Wed, Nov 30, 2022 at 05:35:01PM -0500, Tom Lane wrote:
> Also, I'd like to structure things so that the first para covers what
> you need to know in a clean v15+ installation, and details that only
> apply in upgrade scenarios are in the second para.  The upgrade scenario
> is going to be interesting to fewer and fewer people over time, so let's
> not clutter the lede with it.
> 
> So maybe about like this?
> 
>     Constrain ordinary users to user-private schemas.  To implement
>     this pattern, for every user needing to create non-temporary
>     objects, create a schema with the same name as that user.  (Recall
>     that the default search path starts with $user, which resolves to
>     the user name. Therefore, if each user has a separate schema, they
>     access their own schemas by default.)  Also ensure that no other
>     schemas have public CREATE privileges.  This pattern is a secure
>     schema usage pattern unless an untrusted user is the database
>     owner or holds the CREATEROLE privilege, in which case no secure
>     schema usage pattern exists.

This is free from the problem found in ddl-create-public-reorg-really.patch.
However, the word "other" doesn't belong there.  (The per-user schemas should
not have public CREATE privilege.)  I would also move that same sentence up
front, like this:

    Constrain ordinary users to user-private schemas.  To implement this
    pattern, first ensure that no schemas have public CREATE privileges.
    Then, for every user needing to create non-temporary objects, create a
    schema with the same name as that user.  (Recall that the default search
    path starts with $user, which resolves to the user name. Therefore, if
    each user has a separate schema, they access their own schemas by
    default.)  This pattern is a secure schema usage pattern unless an
    untrusted user is the database owner or holds the CREATEROLE privilege, in
    which case no secure schema usage pattern exists.

With that, I think you have improved on the status quo.  Thanks.

>     In PostgreSQL 15 and later, the default configuration supports
>     this usage pattern.  In prior versions, or when using a database
>     that has been upgraded from a prior version, you will need to
>     remove the public CREATE privilege from the public schema (issue
>     REVOKE CREATE ON SCHEMA public FROM PUBLIC).  Then consider
>     auditing the public schema for objects named like objects in
>     schema pg_catalog.

> BTW, is "create a schema with the same name" sufficient detail?
> You have to either make it owned by that user, or explicitly
> grant CREATE permission on it.  I'm not sure if that detail
> belongs here, but it feels like maybe it does.

Maybe.  Failing to GRANT that will yield a clear error when the user starts
work, so it's not critical to explain here.