pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Noah Misch <noah@leadboat.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: Wed, 30 Nov 2022 17:35:01 -0500
Message-ID: <1461643.1669847701@sss.pgh.pa.us> (raw)
In-Reply-To: <CA+TgmoZ+AFWhTPZnZSqDiuH5fCV6cF9df+fcJhmSoLqkBTiPEA@mail.gmail.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>

Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Nov 30, 2022 at 10:01 AM Noah Misch <noah@leadboat.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





view thread (20+ messages)  latest in thread

Message-ID: <1461643.1669847701@sss.pgh.pa.us>
Permalink:  ../1461643.1669847701@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1461643.1669847701@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, robertmhaas@gmail.com, noah@leadboat.com, pgsql-hackers@postgresql.org
  Subject: Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
  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>

* 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