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) server-digest SHA256) (No client certificate requested) by lists.proxmox.com (Postfix) with ESMTPS id 6EB4F69282 for ; Mon, 22 Mar 2021 10:00:58 +0100 (CET) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id 62E15211EA for ; Mon, 22 Mar 2021 10:00:58 +0100 (CET) Received: from proxmox-new.maurer-it.com (proxmox-new.maurer-it.com [212.186.127.180]) (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 919FD211D1 for ; Mon, 22 Mar 2021 10:00:57 +0100 (CET) Received: from proxmox-new.maurer-it.com (localhost.localdomain [127.0.0.1]) by proxmox-new.maurer-it.com (Proxmox) with ESMTP id 5A46C42E19 for ; Mon, 22 Mar 2021 10:00:57 +0100 (CET) From: Thomas Lamprecht To: pmg-devel@lists.proxmox.com Date: Mon, 22 Mar 2021 10:00:44 +0100 Message-Id: <20210322090046.26278-1-t.lamprecht@proxmox.com> X-Mailer: git-send-email 2.20.1 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-SPAM-LEVEL: Spam detection results: 0 AWL -0.046 Adjusted score from AWL reputation of From: address KAM_DMARC_STATUS 0.01 Test Rule for DKIM or SPF Failure with Strict Alignment RCVD_IN_DNSWL_MED -2.3 Sender listed at https://www.dnswl.org/, medium trust SPF_HELO_NONE 0.001 SPF: HELO does not publish an SPF Record SPF_PASS -0.001 SPF: sender matches SPF record Subject: [pmg-devel] [PATCH] fix #3164: api: quarantine: allow to return spam from all users 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: Mon, 22 Mar 2021 09:00:58 -0000 The pmail was only checked for the spam quarantine call, and there mainly to ensure that the quarantine user only can check their own mails. Make the pmail parameter also optional for this quarantine related endpoint as long as one has a role other than quser. This allows to query all spam quarantine entries from all pmails at once, providing the backend side to address #3164. The main argument against this was performance, but postgres can handle even hundreds of thousands of rows rather fine, it's a high performant database after all and this is quite the simple query (no joins, functions on columns or nested queries). Some data, 45k records on a read limited disk, gathered with EXPLAIN ANALYZE commands: All caches dropped and fresh start: 440ms Running for a bit with caches warm: 55ms A simple extrapolation would mean that for half a million rows we would spent about 5s in the DB, which is not too bad considering our hard limit of 30s per requests, and the overhead of perl/https seems to put the limit on my not so beefy VM at at least ~1.5 million rows from a *cold* cache, which seems plenty (default 7 days keep window and an avg. of 10 spam mails per day means >21k qusers). And with warm caches and a beefier machine one can probably gain one or even two order of magnitudes here. And at the end, no mail admin is forced to use this and if they run a setup with tens of millions of spam in their spam-keep time window, well, they really should not be surprised that querying all has a certain cost. Signed-off-by: Thomas Lamprecht --- src/PMG/API2/Quarantine.pm | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/src/PMG/API2/Quarantine.pm b/src/PMG/API2/Quarantine.pm index 56f248d..666dffa 100644 --- a/src/PMG/API2/Quarantine.pm +++ b/src/PMG/API2/Quarantine.pm @@ -597,14 +597,14 @@ my $quarantine_api = sub { my $rpcenv = PMG::RESTEnvironment->get(); my $authuser = $rpcenv->get_user(); + my $role = $rpcenv->get_role(); my $start = $param->{starttime} // (time - 86400); my $end = $param->{endtime} // ($start + 86400); my $select; my $pmail; - if ($check_pmail) { - my $role = $rpcenv->get_role(); + if ($check_pmail || $role eq 'quser') { $pmail = $verify_optional_pmail->($authuser, $role, $param->{pmail}); $select = "SELECT * " . "FROM CMailStore, CMSReceivers WHERE " . @@ -700,7 +700,7 @@ __PACKAGE__->register_method ({ }, code => sub { my ($param) = @_; - return $quarantine_api->($param, 'S', 1); + return $quarantine_api->($param, 'S', defined($param->{pmail})); }}); __PACKAGE__->register_method ({ -- 2.20.1