From mboxrd@z Thu Jan 1 00:00:00 1970
Return-Path: <d.csapak@proxmox.com>
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 <pmg-devel@lists.proxmox.com>; 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 <pmg-devel@lists.proxmox.com>; 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 <pmg-devel@lists.proxmox.com>; 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 <pmg-devel@lists.proxmox.com>; Fri, 23 Jun 2023 14:21:08 +0200 (CEST)
From: Dominik Csapak <d.csapak@proxmox.com>
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
<pmg-devel.lists.proxmox.com>
List-Unsubscribe: <https://lists.proxmox.com/cgi-bin/mailman/options/pmg-devel>,
<mailto:pmg-devel-request@lists.proxmox.com?subject=unsubscribe>
List-Archive: <http://lists.proxmox.com/pipermail/pmg-devel/>
List-Post: <mailto:pmg-devel@lists.proxmox.com>
List-Help: <mailto:pmg-devel-request@lists.proxmox.com?subject=help>
List-Subscribe: <https://lists.proxmox.com/cgi-bin/mailman/listinfo/pmg-devel>,
<mailto:pmg-devel-request@lists.proxmox.com?subject=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 <d.csapak@proxmox.com>
---
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