From mboxrd@z Thu Jan 1 00:00:00 1970
Return-Path: <s.ivanov@proxmox.com>
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 <pmg-devel@lists.proxmox.com>; 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 <pmg-devel@lists.proxmox.com>; 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 <pmg-devel@lists.proxmox.com>; 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 <pmg-devel@lists.proxmox.com>; Mon, 22 Mar 2021 15:06:16 +0100 (CET)
Date: Mon, 22 Mar 2021 15:06:15 +0100
From: Stoiko Ivanov <s.ivanov@proxmox.com>
To: Thomas Lamprecht <t.lamprecht@proxmox.com>
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
<pmg-devel.lists.proxmox.com>
List-Unsubscribe: <https://lists.proxmox.com/cgi-bin/mailman/options/pmg-devel>,
<mailto:pmg-devel-request@lists.proxmox.com?subject=unsubscribe>
List-Archive: <http://lists.proxmox.com/pipermail/pmg-devel/>
List-Post: <mailto:pmg-devel@lists.proxmox.com>
List-Help: <mailto:pmg-devel-request@lists.proxmox.com?subject=help>
List-Subscribe: <https://lists.proxmox.com/cgi-bin/mailman/listinfo/pmg-devel>,
<mailto:pmg-devel-request@lists.proxmox.com?subject=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 <t.lamprecht@proxmox.com> 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 <s.ivanov@proxmox.com>
Reviewed-By: Stoiko Ivanov <s.ivanov@proxmox.com>
>
> 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 <t.lamprecht@proxmox.com>
> ---
> 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 ({