Re: [pmg-devel] [PATCH] fix #3164: api: quarantine: allow to return spam from all users

[Stoiko Ivanov <s.ivanov@proxmox.com>]

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 ({