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 122CC907B for ; Fri, 23 Jun 2023 14:21:39 +0200 (CEST) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id EF4543476C for ; Fri, 23 Jun 2023 14:21:08 +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)) (No client certificate requested) by firstgate.proxmox.com (Proxmox) with ESMTPS for ; Fri, 23 Jun 2023 14:21:08 +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 1A6E742FFD for ; Fri, 23 Jun 2023 14:21:08 +0200 (CEST) From: Dominik Csapak To: pmg-devel@lists.proxmox.com Date: Fri, 23 Jun 2023 14:21:00 +0200 Message-Id: <20230623122104.2411099-2-d.csapak@proxmox.com> X-Mailer: git-send-email 2.30.2 In-Reply-To: <20230623122104.2411099-1-d.csapak@proxmox.com> References: <20230623122104.2411099-1-d.csapak@proxmox.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-SPAM-LEVEL: Spam detection results: 0 AWL 0.016 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 - URIBL_BLOCKED 0.001 ADMINISTRATOR NOTICE: The query to URIBL was blocked. See http://wiki.apache.org/spamassassin/DnsBlocklists#dnsbl-block for more information. [dbtools.pm] Subject: [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: Fri, 23 Jun 2023 12:21:39 -0000 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\";"; }; sub create_ruledb { -- 2.30.2