greylisting: drop unneeded Host column form cgreylist table

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>
Reviewed-By: Dominik Csapak <d.csapak@proxmox.com>
Tested-By: Dominik Csapak <d.csapak@proxmox.com>

diff --git a/src/PMG/Cluster.pm b/src/PMG/Cluster.pm
index acaea8d28b5616cb690255039227b049e0d4b402..853b874329317602baeb1dd629db28daaa4d035a 100644 (file)
--- 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 c1b59c672c47b104797f48f9c7a579bc7ea19f02..d53711f8fccdf7dd214bfc0f7aa7d698deb610ce 100644 (file)
--- 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 58a48b7ca23c512564218ad256761f018541f6ee..2fd29838c0b352d4b189fa5a4ee4eb9340849383 100755 (executable)
--- 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}
            );