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 4CBB2694CE for ; Mon, 22 Mar 2021 15:06:18 +0100 (CET) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id 44A312492B for ; Mon, 22 Mar 2021 15:06:18 +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 F09942491B for ; Mon, 22 Mar 2021 15:06:16 +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 B9DF442697 for ; Mon, 22 Mar 2021 15:06:16 +0100 (CET) Date: Mon, 22 Mar 2021 15:06:15 +0100 From: Stoiko Ivanov To: Thomas Lamprecht Cc: pmg-devel@lists.proxmox.com Message-ID: <20210322150615.2894ba90@rosa.proxmox.com> In-Reply-To: <20210322090046.26278-1-t.lamprecht@proxmox.com> References: <20210322090046.26278-1-t.lamprecht@proxmox.com> X-Mailer: Claws Mail 3.17.3 (GTK+ 2.24.32; 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.062 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: Re: [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 14:06:18 -0000 Huge thanks for tackling this feature quite a few users have asked and have been waiting for! On Mon, 22 Mar 2021 10:00:44 +0100 Thomas Lamprecht wrote: > 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). agreed @postgres being a performant dbms - small nit - the quarantine query has a join (it's quering cmsreceivers and cmailstore in one query) On my machine a 30k mail test-set even showed some room for improvement with the postgres parameters (sorting resorting to a temp-file for < 10Megs sounds like a bad tradeoff) - however this is a different topic and as you said - users with huge deployments using quarantine, hopefully know how to tweak a few postgres parameters. quickly gave your patches a spin - it works quite well on my test-setup (currently at 48k mails selecting 'all' takes 5s in the GUI (and yields 10.4MB of results) The changes to pmg-api look good! The ones to pmg-gui and proxmox-widet-toolkit as well (but I'm not the best judge of java-script code) with that said: Tested-By: Stoiko Ivanov Reviewed-By: Stoiko Ivanov > > 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 ({