From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: 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 ; 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 ; 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 ; 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 ; Fri, 11 Jun 2021 17:54:59 +0200 (CEST) From: Stoiko Ivanov 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 List-Unsubscribe: , List-Archive: List-Post: List-Help: List-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 --- 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