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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Nov 30, 2022 at 10:01 AM Noah Misch <noah(at)leadboat(dot)com> wrote:
>> Could remove the paragraph about v14. Could have that paragraph say
>> explicitly that the REVOKE is a no-op. Would either of those be an
>> improvement?

> Well, I thought what I proposed was a nice improvement, but I guess if
> you don't like it I'm not inclined to spend a lot of time discussing
> other possibilities. If we get some opinions from more people that may
> make it clearer which direction to go; if I'm the only one that
> doesn't like the way it is now, it's probably not that important.

Hey, I'll step up to the plate ;-)

I agree that it's confusing to tell people to do a REVOKE that might do
nothing. A parenthetical note explaining that might help, but the text
is pretty dense already, so really I'd rather have that info in a
separate para.

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.

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 ;-)

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.

regards, tom lane