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 BB714731F1 for ; Wed, 6 Oct 2021 18:33:32 +0200 (CEST) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id AEFB6FE9F for ; Wed, 6 Oct 2021 18:33:32 +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) server-digest SHA256) (No client certificate requested) by firstgate.proxmox.com (Proxmox) with ESMTPS id 7C7AEFE8F for ; Wed, 6 Oct 2021 18:33:31 +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 52E6F4581F for ; Wed, 6 Oct 2021 18:33:31 +0200 (CEST) Date: Wed, 6 Oct 2021 18:33:30 +0200 From: Stoiko Ivanov To: Dominik Csapak Cc: pmg-devel@lists.proxmox.com Message-ID: <20211006183330.0939687e@rosa.proxmox.com> In-Reply-To: <20210924111746.3937863-1-d.csapak@proxmox.com> References: <20210924111746.3937863-1-d.csapak@proxmox.com> X-Mailer: Claws Mail 3.17.8 (GTK+ 2.24.33; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-SPAM-LEVEL: Spam detection results: 0 AWL 0.383 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. [ldapuser.pm, whoregex.pm, ldap.pm, utils.pm] Subject: [pmg-devel] applied: [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: Wed, 06 Oct 2021 16:33:32 -0000 Huge thanks for the patch - tested and applied! On Fri, 24 Sep 2021 13:17:46 +0200 Dominik Csapak wrote: > 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;