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

On Wed, Nov 30, 2022 at 5:35 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.

Right, that was my main feeling about this.

> 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.
>
> 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.
>
> This is close to what Robert wrote, but not exactly the same,
> so probably it will make neither of you happy ;-)

I haven't looked at how it's different from what I wrote exactly, but
it seems fine to me.

--
Robert Haas
EDB: http://www.enterprisedb.com