Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas

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.