From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from firstgate.proxmox.com (firstgate.proxmox.com [212.224.123.68]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by lists.proxmox.com (Postfix) with ESMTPS id CFC909874 for ; Mon, 26 Jun 2023 10:48:03 +0200 (CEST) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id B23F22520A for ; Mon, 26 Jun 2023 10:47:33 +0200 (CEST) Received: from proxmox-new.maurer-it.com (proxmox-new.maurer-it.com [94.136.29.106]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by firstgate.proxmox.com (Proxmox) with ESMTPS for ; Mon, 26 Jun 2023 10:47:33 +0200 (CEST) Received: from proxmox-new.maurer-it.com (localhost.localdomain [127.0.0.1]) by proxmox-new.maurer-it.com (Proxmox) with ESMTP id 0828443B0A for ; Mon, 26 Jun 2023 10:47:33 +0200 (CEST) Date: Mon, 26 Jun 2023 10:47:32 +0200 From: Stoiko Ivanov To: Dominik Csapak Cc: pmg-devel@lists.proxmox.com Message-ID: <20230626104732.24a7449f@rosa.proxmox.com> In-Reply-To: <20230623122104.2411099-2-d.csapak@proxmox.com> References: <20230623122104.2411099-1-d.csapak@proxmox.com> <20230623122104.2411099-2-d.csapak@proxmox.com> X-Mailer: Claws Mail 4.1.1 (GTK 3.24.24; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-SPAM-LEVEL: Spam detection results: 0 AWL 0.098 Adjusted score from AWL reputation of From: address BAYES_00 -1.9 Bayes spam probability is 0 to 1% DMARC_MISSING 0.1 Missing DMARC policy KAM_DMARC_STATUS 0.01 Test Rule for DKIM or SPF Failure with Strict Alignment SPF_HELO_NONE 0.001 SPF: HELO does not publish an SPF Record SPF_PASS -0.001 SPF: sender matches SPF record T_SCC_BODY_TEXT_LINE -0.01 - Subject: Re: [pmg-devel] [PATCH pmg-api 1/3] dbtools: grant permissions public schema for created databases X-BeenThere: pmg-devel@lists.proxmox.com X-Mailman-Version: 2.1.29 Precedence: list List-Id: Proxmox Mail Gateway development discussion List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 26 Jun 2023 08:48:03 -0000 Thanks for the patch! On Fri, 23 Jun 2023 14:21:00 +0200 Dominik Csapak wrote: > since postgres 15, the public schema is not world writeable anymore for > security reasons. In our environment, where the db is not externaly > reachable and no database users should exists except the ones we create, > we can safely give the permissions again to be able to use > the root/www-data user without modification of the remaining > code/privileges for postgres. > > Signed-off-by: Dominik Csapak > --- > src/PMG/DBTools.pm | 8 ++++++++ > 1 file changed, 8 insertions(+) > > diff --git a/src/PMG/DBTools.pm b/src/PMG/DBTools.pm > index 0b37361..f8eb054 100644 > --- a/src/PMG/DBTools.pm > +++ b/src/PMG/DBTools.pm > @@ -344,6 +344,14 @@ my $createdb = sub { > '--lc-ctype=C', > $dbname, > ); > + > + # allow root and www-data to access the public SCHEMA like pre prostgres15 > + # this is not a security issue, since the db is not externally reachable anyway and no > + # other users should exist > + my $cmd = "GRANT CREATE ON SCHEMA public To \"root\";" > + ."GRANT USAGE ON SCHEMA public To \"root\";" > + ."GRANT CREATE ON SCHEMA public To \"www-data\";" > + ."GRANT USAGE ON SCHEMA public To \"www-data\";"; > }; the command is placed in a variable, but never actually called? looking through DBTools - it might be a better fit to declare a sub create_user, and put the `createuser` invocation as well as the GRANT sql commands there - but the separate sub is only a suggestion. > > sub create_ruledb {