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 944069CD9 for ; Mon, 26 Jun 2023 16:10:27 +0200 (CEST) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id 74A3629923 for ; Mon, 26 Jun 2023 16:10:27 +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 ; Mon, 26 Jun 2023 16:10:27 +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 C688643D7D for ; Mon, 26 Jun 2023 16:10:26 +0200 (CEST) From: Dominik Csapak To: pmg-devel@lists.proxmox.com Date: Mon, 26 Jun 2023 16:10:26 +0200 Message-Id: <20230626141026.1281109-1-d.csapak@proxmox.com> X-Mailer: git-send-email 2.30.2 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 - Subject: [pmg-devel] [PATCH pmg-api v2] 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 14:10:27 -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 --- changes from v1: * rename cmd to $grantcmd * actually use it and execute it with postgres_admin_cmd/psql src/PMG/DBTools.pm | 10 ++++++++++ 1 file changed, 10 insertions(+) diff --git a/src/PMG/DBTools.pm b/src/PMG/DBTools.pm index 0b37361..a437eb6 100644 --- a/src/PMG/DBTools.pm +++ b/src/PMG/DBTools.pm @@ -344,6 +344,16 @@ 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 $grantcmd = "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\";"; + + postgres_admin_cmd('psql', { input => $grantcmd }, '-d', $dbname); }; sub create_ruledb { -- 2.30.2