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 2C4126CC7E for ; Fri, 24 Sep 2021 13:24:57 +0200 (CEST) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id 1BB53A169 for ; Fri, 24 Sep 2021 13:24:27 +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 8E687A160 for ; Fri, 24 Sep 2021 13:24:26 +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 45DD844A96 for ; Fri, 24 Sep 2021 13:17:47 +0200 (CEST) From: Dominik Csapak To: pmg-devel@lists.proxmox.com Date: Fri, 24 Sep 2021 13:17:46 +0200 Message-Id: <20210924111746.3937863-1-d.csapak@proxmox.com> X-Mailer: git-send-email 2.30.2 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-SPAM-LEVEL: Spam detection results: 0 AWL 0.354 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. [utils.pm, ldap.pm, whoregex.pm, ldapuser.pm] Subject: [pmg-devel] [PATCH pmg-api] fix #2071: RuleDB: ignore duplicate entries for Who objects 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, 24 Sep 2021 11:24:57 -0000 if we detect an entry with a value that is identical, return that id instead of adding it again to the db. Signed-off-by: Dominik Csapak --- src/PMG/RuleDB/LDAP.pm | 10 ++++++++++ src/PMG/RuleDB/LDAPUser.pm | 10 ++++++++++ src/PMG/RuleDB/WhoRegex.pm | 10 ++++++++++ src/PMG/Utils.pm | 17 +++++++++++++++++ 4 files changed, 47 insertions(+) diff --git a/src/PMG/RuleDB/LDAP.pm b/src/PMG/RuleDB/LDAP.pm index e17441b..a132499 100644 --- a/src/PMG/RuleDB/LDAP.pm +++ b/src/PMG/RuleDB/LDAP.pm @@ -81,6 +81,16 @@ sub save { } else { # insert + # check if it exists first + if (my $id = PMG::Utils::get_existing_object_id( + $ruledb->{dbh}, + $self->{ogroup}, + $self->otype(), + $confdata + )) { + return $id; + } + my $sth = $ruledb->{dbh}->prepare( "INSERT INTO Object (Objectgroup_ID, ObjectType, Value) " . "VALUES (?, ?, ?);"); diff --git a/src/PMG/RuleDB/LDAPUser.pm b/src/PMG/RuleDB/LDAPUser.pm index f550870..022d784 100644 --- a/src/PMG/RuleDB/LDAPUser.pm +++ b/src/PMG/RuleDB/LDAPUser.pm @@ -83,6 +83,16 @@ sub save { } else { # insert + # check if it exists first + if (my $id = PMG::Utils::get_existing_object_id( + $ruledb->{dbh}, + $self->{ogroup}, + $self->otype(), + $confdata + )) { + return $id; + } + my $sth = $ruledb->{dbh}->prepare( "INSERT INTO Object (Objectgroup_ID, ObjectType, Value) " . "VALUES (?, ?, ?);"); diff --git a/src/PMG/RuleDB/WhoRegex.pm b/src/PMG/RuleDB/WhoRegex.pm index b9519ad..37ec3aa 100644 --- a/src/PMG/RuleDB/WhoRegex.pm +++ b/src/PMG/RuleDB/WhoRegex.pm @@ -70,6 +70,16 @@ sub save { } else { # insert + # check if it exists first + if (my $id = PMG::Utils::get_existing_object_id( + $ruledb->{dbh}, + $self->{ogroup}, + $self->otype(), + $adr + )) { + return $id; + } + my $sth = $ruledb->{dbh}->prepare ( "INSERT INTO Object (Objectgroup_ID, ObjectType, Value) " . "VALUES (?, ?, ?);"); diff --git a/src/PMG/Utils.pm b/src/PMG/Utils.pm index 20686ad..92c3a7a 100644 --- a/src/PMG/Utils.pm +++ b/src/PMG/Utils.pm @@ -1505,4 +1505,21 @@ sub update_local_spamassassin_channels { return $fresh_updates } +sub get_existing_object_id { + my ($dbh, $obj_id, $obj_type, $value) = @_; + + my $sth = $dbh->prepare("SELECT id FROM Object WHERE ". + "Objectgroup_ID = ? AND ". + "ObjectType = ? AND ". + "Value = ?" + ); + $sth->execute($obj_id, $obj_type, $value); + + if (my $ref = $sth->fetchrow_hashref()) { + return $ref->{id}; + } + + return; +} + 1; -- 2.30.2