On Wed, Nov 30, 2022 at 5:35 PM Tom Lane <t...@sss.pgh.pa.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
