[pmg-devel] [PATCH pmg-api 0/2] remove unused greylist table column and make test_gl more robust

[pmg-devel] [PATCH pmg-api 0/2] remove unused greylist table column and make test_gl more robust

[Stoiko Ivanov]

These two patches are the result from some intial trials to build and test
PMG on debian bullseye

The first drops a deprecated database column, and since I consider changes
to the database schema somewhat sensitive I'd like another set of eyes
to look over the changes

the second patch addresses a timing issue in test_greylist.pl, which
causes the tests to fail, because the policy daemon is not accepting
connections early enough (at least on my test-container with bullseye)
One alternative would be to skip the greylist tests here which would
enables building as non-root user.

Stoiko Ivanov (2):
  greylisting: drop unneeded Host column form cgreylist table
  tests: greylist: retry connecting 3 times

 src/PMG/Cluster.pm         |  3 +--
 src/PMG/DBTools.pm         | 21 +++++++++++++++------
 src/bin/pmgpolicy          |  6 ++----
 src/tests/test_greylist.pl | 14 ++++++++++----
 4 files changed, 28 insertions(+), 16 deletions(-)

-- 
2.20.1

[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@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

[pmg-devel] [PATCH pmg-api 2/2] tests: greylist: retry connecting 3 times

[Stoiko Ivanov]

Sometimes pmgpolicy is not done starting up when we try connecting.
Sadly strace on test_greylist.pl makes the problem disappear.

Looping 3 times should work robustly.

Signed-off-by: Stoiko Ivanov <s.ivanov@proxmox.com>
---
 src/tests/test_greylist.pl | 14 ++++++++++----
 1 file changed, 10 insertions(+), 4 deletions(-)

diff --git a/src/tests/test_greylist.pl b/src/tests/test_greylist.pl
index 3aa613f..6a5a11a 100755
--- a/src/tests/test_greylist.pl
+++ b/src/tests/test_greylist.pl
@@ -44,10 +44,16 @@ sub reset_gldb {
 
 reset_gldb();
 
-my $sock = IO::Socket::INET->new(
-    PeerAddr => '127.0.0.1',
-    PeerPort => $testport) ||
-    die "unable to open socket -  $!";
+
+my $sock;
+for (my $tries = 0 ; $tries < 3 ; $tries++) {
+    $sock = IO::Socket::INET->new(
+	PeerAddr => '127.0.0.1',
+	PeerPort => $testport);
+    last if $sock;
+    sleep 1;
+}
+die "unable to open socket -  $!" if !$sock;
 
 $/ = "\n\n";
 
-- 
2.20.1

Re: [pmg-devel] [PATCH pmg-api 0/2] remove unused greylist table column and make test_gl more robust

[Dominik Csapak]

looks good, works as advertised.
did not matter if i upgraded a master node first or not
(since we did not rely on the host column any more anyway)
nice :)

i could not reproduce the original problem of the second patch,
but it did not break it, so also ok

Reviewed-By: Dominik Csapak <d.csapak@proxmox.com>
Tested-By: Dominik Csapak <d.csapak@proxmox.com>

On 6/11/21 5:54 PM, Stoiko Ivanov wrote:
> These two patches are the result from some intial trials to build and test
> PMG on debian bullseye
> 
> The first drops a deprecated database column, and since I consider changes
> to the database schema somewhat sensitive I'd like another set of eyes
> to look over the changes
> 
> the second patch addresses a timing issue in test_greylist.pl, which
> causes the tests to fail, because the policy daemon is not accepting
> connections early enough (at least on my test-container with bullseye)
> One alternative would be to skip the greylist tests here which would
> enables building as non-root user.
> 
> Stoiko Ivanov (2):
>    greylisting: drop unneeded Host column form cgreylist table
>    tests: greylist: retry connecting 3 times
> 
>   src/PMG/Cluster.pm         |  3 +--
>   src/PMG/DBTools.pm         | 21 +++++++++++++++------
>   src/bin/pmgpolicy          |  6 ++----
>   src/tests/test_greylist.pl | 14 ++++++++++----
>   4 files changed, 28 insertions(+), 16 deletions(-)
> 

[pmg-devel] applied-series: [PATCH pmg-api 0/2] remove unused greylist table column and make test_gl more robust

[Thomas Lamprecht]

On 11.06.21 17:54, Stoiko Ivanov wrote:
> These two patches are the result from some intial trials to build and test
> PMG on debian bullseye
> 
> The first drops a deprecated database column, and since I consider changes
> to the database schema somewhat sensitive I'd like another set of eyes
> to look over the changes
> 
> the second patch addresses a timing issue in test_greylist.pl, which
> causes the tests to fail, because the policy daemon is not accepting
> connections early enough (at least on my test-container with bullseye)
> One alternative would be to skip the greylist tests here which would
> enables building as non-root user.
> 
> Stoiko Ivanov (2):
>   greylisting: drop unneeded Host column form cgreylist table
>   tests: greylist: retry connecting 3 times
> 
>  src/PMG/Cluster.pm         |  3 +--
>  src/PMG/DBTools.pm         | 21 +++++++++++++++------
>  src/bin/pmgpolicy          |  6 ++----
>  src/tests/test_greylist.pl | 14 ++++++++++----
>  4 files changed, 28 insertions(+), 16 deletions(-)
> 



applied series with Dominik's R-b/T-b tags, thanks to both!