From mboxrd@z Thu Jan  1 00:00:00 1970
Return-Path: <s.ivanov@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 E841971B76
 for <pmg-devel@lists.proxmox.com>; Fri, 11 Jun 2021 17:55:31 +0200 (CEST)
Received: from firstgate.proxmox.com (localhost [127.0.0.1])
 by firstgate.proxmox.com (Proxmox) with ESMTP id C73B115159
 for <pmg-devel@lists.proxmox.com>; Fri, 11 Jun 2021 17:55:01 +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 id 0C4D215120
 for <pmg-devel@lists.proxmox.com>; Fri, 11 Jun 2021 17:55:00 +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 D0E7342EDF
 for <pmg-devel@lists.proxmox.com>; Fri, 11 Jun 2021 17:54:59 +0200 (CEST)
From: Stoiko Ivanov <s.ivanov@proxmox.com>
To: pmg-devel@lists.proxmox.com
Date: Fri, 11 Jun 2021 17:54:46 +0200
Message-Id: <20210611155447.9162-2-s.ivanov@proxmox.com>
X-Mailer: git-send-email 2.20.1
In-Reply-To: <20210611155447.9162-1-s.ivanov@proxmox.com>
References: <20210611155447.9162-1-s.ivanov@proxmox.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
X-SPAM-LEVEL: Spam detection results:  0
 AWL 0.889 Adjusted score from AWL reputation of From: address
 BAYES_00                 -1.9 Bayes spam probability is 0 to 1%
 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
 URIBL_BLOCKED 0.001 ADMINISTRATOR NOTICE: The query to URIBL was blocked. See
 http://wiki.apache.org/spamassassin/DnsBlocklists#dnsbl-block for more
 information. [cgreylist.host, cluster.pm, excluded.host, dbtools.pm]
Subject: [pmg-devel] [PATCH pmg-api 1/2] greylisting: drop unneeded Host
 column form cgreylist table
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, 11 Jun 2021 15:55:32 -0000

With the changes added in f61d54891d4820b21ef9e53f7ce0ebb1d5be1f73
greylisting does the matches based on a configurable netmask, and
does not use the 'Host' column in the cgreylist table anymore.

Drop it now with PMG 7.0

Quickly tested the following scenarios (all successfully):
* Upgrading from a previous version
* Restoring a pmg-backup taken with PMG 5.2 (the greylist table is
  excluded from the backup)
* Adding a node with the changes to an existing cluster without the
  change
* Adding a node without the changes to a master-node having them

Signed-off-by: Stoiko Ivanov <s.ivanov@proxmox.com>
---
 src/PMG/Cluster.pm |  3 +--
 src/PMG/DBTools.pm | 21 +++++++++++++++------
 src/bin/pmgpolicy  |  6 ++----
 3 files changed, 18 insertions(+), 12 deletions(-)

diff --git a/src/PMG/Cluster.pm b/src/PMG/Cluster.pm
index acaea8d..853b874 100644
--- a/src/PMG/Cluster.pm
+++ b/src/PMG/Cluster.pm
@@ -823,7 +823,6 @@ sub sync_greylist_db {
 	    "mtime >= $lastmt AND CID != 0";
     };
 
-    # FIXME: drop Host column with PMG 7.0
     my $merge_sth = $dbh->prepare(PMG::DBTools::cgreylist_merge_sql());
     my $mergefunc = sub {
 	my ($ref) = @_;
@@ -831,7 +830,7 @@ sub sync_greylist_db {
 	my $ipnet = $ref->{ipnet};
 	$ipnet .= '.0/24' if $ipnet !~ /\/\d+$/;
 	$merge_sth->execute(
-	    $ipnet, 0, $ref->{sender}, $ref->{receiver},
+	    $ipnet, $ref->{sender}, $ref->{receiver},
 	    $ref->{instance}, $ref->{rctime}, $ref->{extime}, $ref->{delay},
 	    $ref->{blocked}, $ref->{passed}, 0, $ref->{cid});
     };
diff --git a/src/PMG/DBTools.pm b/src/PMG/DBTools.pm
index c1b59c6..d53711f 100644
--- a/src/PMG/DBTools.pm
+++ b/src/PMG/DBTools.pm
@@ -18,18 +18,16 @@ use PMG::Utils qw(postgres_admin_cmd);
 
 our $default_db_name = "Proxmox_ruledb";
 
-# FIXME: drop Host column with PMG 7.0
 sub cgreylist_merge_sql {
     my ($with_mask) = @_;
 
     my $network = $with_mask ? 'network(set_masklen(?, ?))' : '?';
 
     my $sql =
-    'INSERT INTO CGREYLIST (IPNet,Host,Sender,Receiver,Instance,RCTime,' .
+    'INSERT INTO CGREYLIST (IPNet,Sender,Receiver,Instance,RCTime,' .
     'ExTime,Delay,Blocked,Passed,MTime,CID) ' .
-    "VALUES ($network, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " .
+    "VALUES ($network, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " .
     'ON CONFLICT (IPNet,Sender,Receiver) DO UPDATE SET ' .
-    'Host = CASE WHEN CGREYLIST.MTime >= excluded.MTime THEN CGREYLIST.Host ELSE excluded.Host END,' .
     'CID = GREATEST(CGREYLIST.CID, excluded.CID), RCTime = LEAST(CGREYLIST.RCTime, excluded.RCTime),' .
     'ExTime = GREATEST(CGREYLIST.ExTime, excluded.ExTime),' .
     'Delay = GREATEST(CGREYLIST.Delay, excluded.Delay),' .
@@ -112,7 +110,6 @@ sub database_list {
 my $cgreylist_ctablecmd =  <<__EOD;
     CREATE TABLE CGreylist
     (IPNet VARCHAR(49) NOT NULL,
-     Host INTEGER NOT NULL,
      Sender VARCHAR(255) NOT NULL,
      Receiver VARCHAR(255) NOT NULL,
      Instance VARCHAR(255),
@@ -531,7 +528,6 @@ sub upgradedb {
 		 "AND value = 'content-type:application/x-java-vm';");
     };
 
-    # FIXME: drop Host column with PMG 7.0
     # increase column size of cgreylist.ipnet for ipv6 support and transfer data
     eval {
 	my $sth = $dbh->prepare("SELECT character_maximum_length ".
@@ -559,6 +555,19 @@ sub upgradedb {
 	die $err;
     }
 
+    # drop greylist Host column with PMG 7.0
+    if (database_column_exists($dbh, 'CGreylist', 'Host')) {
+	eval {
+	    $dbh->begin_work;
+	    $dbh->do("ALTER TABLE CGreylist DROP COLUMN Host");
+	    $dbh->commit;
+	};
+	if (my $err = $@) {
+	    $dbh->rollback;
+	    die $err;
+	}
+    }
+
     foreach my $table (keys %$tables) {
 	eval { $dbh->do("ANALYZE $table"); };
 	warn $@ if $@;
diff --git a/src/bin/pmgpolicy b/src/bin/pmgpolicy
index 58a48b7..2fd2983 100755
--- a/src/bin/pmgpolicy
+++ b/src/bin/pmgpolicy
@@ -630,10 +630,8 @@ sub greylist_value {
 		    # table later. We set 'blocked' to 100000 to identify those entries.
 
 		    if (!defined($ref->{rctime})) {
-
-			# FIXME: drop Host column with PMG 7.0
 			$dbh->do(PMG::DBTools::cgreylist_merge_sql(1), undef,
-				 $ip, $masklen, 0, $sender, $rcpt, $instance,
+				 $ip, $masklen, $sender, $rcpt, $instance,
 				 $ctime, $ctime + 10, 0, 100000, 0, $ctime, $self->{lcid});
 		    }
 
@@ -687,7 +685,7 @@ sub greylist_value {
 
 	    $dbh->do(
 		PMG::DBTools::cgreylist_merge_sql(1), undef, $ip, $masklen,
-		0, $sender, $rcpt, $instance, $ctime, $ctime + $greylist_lifetime,
+		$sender, $rcpt, $instance, $ctime, $ctime + $greylist_lifetime,
 		0, 1, 0, $ctime, $self->{lcid}
 	    );
 
-- 
2.20.1