public inbox for pmg-devel@lists.proxmox.com
 help / color / mirror / Atom feed
From: Stoiko Ivanov <s.ivanov@proxmox.com>
To: pmg-devel@lists.proxmox.com
Subject: [pmg-devel] [PATCH pmg-api 1/2] greylisting: drop unneeded Host column form cgreylist table
Date: Fri, 11 Jun 2021 17:54:46 +0200	[thread overview]
Message-ID: <20210611155447.9162-2-s.ivanov@proxmox.com> (raw)
In-Reply-To: <20210611155447.9162-1-s.ivanov@proxmox.com>

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





  reply	other threads:[~2021-06-11 15:55 UTC|newest]

Thread overview: 5+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2021-06-11 15:54 [pmg-devel] [PATCH pmg-api 0/2] remove unused greylist table column and make test_gl more robust Stoiko Ivanov
2021-06-11 15:54 ` Stoiko Ivanov [this message]
2021-06-11 15:54 ` [pmg-devel] [PATCH pmg-api 2/2] tests: greylist: retry connecting 3 times Stoiko Ivanov
2021-06-16 11:04 ` [pmg-devel] [PATCH pmg-api 0/2] remove unused greylist table column and make test_gl more robust Dominik Csapak
2021-06-16 12:21 ` [pmg-devel] applied-series: " Thomas Lamprecht

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=20210611155447.9162-2-s.ivanov@proxmox.com \
    --to=s.ivanov@proxmox.com \
    --cc=pmg-devel@lists.proxmox.com \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line before the message body.
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox
Service provided by Proxmox Server Solutions GmbH | Privacy | Legal