pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Noah Misch <noah@leadboat.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
Date: Thu, 1 Dec 2022 00:25:33 -0800
Message-ID: <20221201082533.GA309855@rfd.leadboat.com> (raw)
In-Reply-To: <1461643.1669847701@sss.pgh.pa.us>
References: <E1mOaBy-0006mH-1P@gemulon.postgresql.org>
	<CA+TgmoYUHsfp90inEMAP0yNr7Y_L6EphPH1YOon1JKtBztXHyQ@mail.gmail.com>
	<20221130070701.GA275942@rfd.leadboat.com>
	<CA+TgmoZg1CvL8gjZNDSxn_mr1jokXYXQcJXYazOFC8G+TYbFeg@mail.gmail.com>
	<20221130150136.GA287291@rfd.leadboat.com>
	<CA+TgmoZ+AFWhTPZnZSqDiuH5fCV6cF9df+fcJhmSoLqkBTiPEA@mail.gmail.com>
	<1461643.1669847701@sss.pgh.pa.us>

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.





view thread (20+ messages)  latest in thread

Message-ID: <20221201082533.GA309855@rfd.leadboat.com>
Permalink:  ../20221201082533.GA309855@rfd.leadboat.com/
Also on:    postgresql.org/message-id/20221201082533.GA309855@rfd.leadboat.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: noah@leadboat.com, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, pgsql-hackers@postgresql.org
  Subject: Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  In-Reply-To: <20221201082533.GA309855@rfd.leadboat.com>
  References: <E1mOaBy-0006mH-1P@gemulon.postgresql.org> <CA+TgmoYUHsfp90inEMAP0yNr7Y_L6EphPH1YOon1JKtBztXHyQ@mail.gmail.com> <20221130070701.GA275942@rfd.leadboat.com> <CA+TgmoZg1CvL8gjZNDSxn_mr1jokXYXQcJXYazOFC8G+TYbFeg@mail.gmail.com> <20221130150136.GA287291@rfd.leadboat.com> <CA+TgmoZ+AFWhTPZnZSqDiuH5fCV6cF9df+fcJhmSoLqkBTiPEA@mail.gmail.com> <1461643.1669847701@sss.pgh.pa.us> <20221201082533.GA309855@rfd.leadboat.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox