[pmg-devel] [PATCH pmg-api 1/2] greylisting: drop unneeded Host column form cgreylist table

[Stoiko Ivanov]

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 at 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