Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0et4-0006lQ-DG for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Dec 2022 08:25:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p0et3-0006hM-1U for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Dec 2022 08:25:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0et2-0006hD-HC for pgsql-hackers@lists.postgresql.org; Thu, 01 Dec 2022 08:25:40 +0000 Received: from mail-ot1-x329.google.com ([2607:f8b0:4864:20::329]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p0esz-0000xI-Ve for pgsql-hackers@postgresql.org; Thu, 01 Dec 2022 08:25:39 +0000 Received: by mail-ot1-x329.google.com with SMTP id p24-20020a0568301d5800b0066e6dc09be5so492113oth.8 for ; Thu, 01 Dec 2022 00:25:37 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=leadboat.com; s=google; h=user-agent:in-reply-to:content-disposition:mime-version:references :message-id:subject:cc:to:from:date:from:to:cc:subject:date :message-id:reply-to; bh=2Yyu9raRaE4Vf7QM84prcYBeO67Sli/ZKZXkUdzQHS0=; b=FVlg4qTiYJ3wtfCgWwDYE3TyzSX9Ph7iTI82xjz15A5ZCrZkKrN8EezfYDMtZQDnrJ +BPihWyJvQzJ9q40WAe2wZaM+MW0JnrNG5xKUHXJUYUmXR9XqX6ylCJhQSVDIjTe9j/o IMKp0Zfs+7lgTmFmXpkDAJ0Wr1TUsRJhXhESc= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=user-agent:in-reply-to:content-disposition:mime-version:references :message-id:subject:cc:to:from:date:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=2Yyu9raRaE4Vf7QM84prcYBeO67Sli/ZKZXkUdzQHS0=; b=gYaw8pMTGMVQaiZ6pAqwj1UWqeobzjNK3jrrF3MoBBhsrIrQOZuhCRtINd8wbSmZz7 V6hYHkWpJFuN30EEFL4JdKSfOdJMj3uNH2Ptj5l6dlFeMLS09cUAucEeGOoFzUEOOh7x QT3l8Lcw/GaCgTV7SYdQ9gzfbHCaD1mj7tyoYO86DSaoMFkMUbSZtmerv1EJ/NJAUkL/ MfFPxU90BOapiTmR3kKOD2fLdBrM6JYBycc7+AlZAQaUVh9mY8pDjjmB/wiN2IxgKGG9 ZOlKCnuVc90Kb49yaa2nRFQTmxaWuHKoX8o63f7U0BMLig7sawpG4VztrEWywA8vgt1e N+gw== X-Gm-Message-State: ANoB5pnDJVJHwEsAPA+XTieYvq6zgu86LnVtrVQvgr8c4gnxLsa9rikV VbIBX2rVjvfcSIxuNOadjVWW0Uy06/0H4A== X-Google-Smtp-Source: AA0mqf4+XbnlLyUu4z1yOuN48+7n61DIcdxIWOCo7OeSyx1jps4LVX7YIxuSkB9J4B5XvFNVfV2a4g== X-Received: by 2002:a9d:7692:0:b0:667:54f1:aa19 with SMTP id j18-20020a9d7692000000b0066754f1aa19mr33504568otl.15.1669883136744; Thu, 01 Dec 2022 00:25:36 -0800 (PST) Received: from rfd.leadboat.com ([2600:1702:a20:5750::2e]) by smtp.gmail.com with ESMTPSA id d2-20020a4aa582000000b0049e9a80c690sm1575405oom.1.2022.12.01.00.25.35 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 01 Dec 2022 00:25:36 -0800 (PST) Date: Thu, 1 Dec 2022 00:25:33 -0800 From: Noah Misch To: Tom Lane Cc: Robert Haas , "pgsql-hackers@postgresql.org" Subject: Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas Message-ID: <20221201082533.GA309855@rfd.leadboat.com> References: <20221130070701.GA275942@rfd.leadboat.com> <20221130150136.GA287291@rfd.leadboat.com> <1461643.1669847701@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1461643.1669847701@sss.pgh.pa.us> User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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.