thr3ads.net - dovecot - Unclear how to get both user and domain quotas to work [Oct 2021]

If this information is useful, please help other people find it:
Share via:

Hans van Zijst

2021-Oct-20 16:46 UTC

Unclear how to get both user and domain quotas to work

Hi,

First of all: my apologies for this long message. I'm trying to post all 
the information anybody could need to help me on my way.

What I'm trying to make is an installation where both users and domains 
can have quotas. Ideally I would configure a quota for a domain and set 
a quota of 0 for all users within that domain, so that mail will be 
accepted up to the point where the domain quota is reached.

Setting up user quota was fairly easy, there's lots of documentation 
around and examples to take inspiration from. Not so much for domain 
quota however, or I'm really bad at searching the Net.

I'm running Debian Bullseye here:

MariaDB 10.5.12
Dovecot 2.3.13
Postfixadmin 3.3.10

I want to use dictionaries in MySQL, because eventually this will be a 
platform with several Dovecot and Postfix servers. At this time I'm only 
running one Dovecot installation and a few Postfix servers (two for 
communication with the outside world and one to do the virus and spam 
scanning).

My current setup for user quota is how Postfixadmin wants it:

  MariaDB [postfixadmin]> desc quota2;
  +----------+--------------+------+-----+---------+-------+
  | Field    | Type         | Null | Key | Default | Extra |
  +----------+--------------+------+-----+---------+-------+
  | username | varchar(100) | NO   | PRI | NULL    |       |
  | bytes    | bigint(20)   | NO   |     | 0       |       |
  | messages | int(11)      | NO   |     | 0       |       |
  +----------+--------------+------+-----+---------+-------+
  3 rows in set (0.001 sec)


In /etc/dovecot/conf.d/90-quota.conf I have this:

  plugin {
    quota = dict:User quota::proxy::sqlquota
    quota_rule = *:bytes=1024M
    quota_rule2 = Trash:bytes=+100M
    quota_vsizes = yes
    quota_status_success = DUNNO
    quota_status_nouser = DUNNO
    quota_status_overquota = "552 5.2.2 Mailbox is full"
  }

  dict {
    sqlquota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
  }


The SQL-configuration /etc/dovecot/dovecot-dict-sql.conf.ext is this:


  connect = host=... dbname=... user=... password=...
  map {
    pattern = priv/quota/storage
    table = quota2
    username_field = username
    value_field = bytes
  }
  map {
    pattern = priv/quota/messages
    table = quota2
    username_field = username
    value_field = messages
  }
  map {
    pattern = shared/expire/$user/$mailbox
    table = expires
    value_field = expire_stamp
    fields {
      username = $user
      mailbox = $mailbox
    }
  }

I use LMTP to accept mail from Postfix; the quota-plugin is activated 
for LMTP, so every incoming mail should be counted. This works like a 
charm: if I don't configure a quota for a user, he gets the default 1GB, 
if I override it in Postfixadmin, then that's what he gets.

I'm running quota-status on a TCP-port, so the gateway SMTP-servers can 
connect to Dovecot for a quotacheck to decide whether to accept or 
reject an incoming message. Again, this works flawlessly.

I can't find any serious documentation about this mapping thing. In 
particular, what's with that "pattern"? All I can find in the 
documentation is:

"The dict key must match exactly priv/quota/storage. The dict keys are 
hardcoded in the Dovecot code, so depending on what functionality you?re 
configuring you need to know the available dict keys used it."

   -- https://doc.dovecot.org/configuration_manual/dict/#dict-sql

If anyone can point me at better documentation than the actual source 
code: please! I browsed through the quota-plugin source code, but I'm 
not a programmer and couldn't find what I thought I was looking for.

That said, I'm not sure where I got the third "map" from, with the
shared/expire pattern. I seem to have forgotten to document that bit of 
my journey.

Now, domain quotas...

I've tried all kinds of stuff, but at the moment I'm stuck with 
something that seems to almost work.

I added a second quota root:

  plugin {
    quota2 = dict:Domain quota:%d:proxy::sqldomainquota
    quota2_rule = *:bytes=1024M
    quota2_status_success = DUNNO
    quota2_status_nouser = DUNNO
    quota2_status_overquota = "552 5.2.2 Domain quota exceeded"
    quota2_vsizes = yes
  }

  dict {
    sqldomainquota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
  }

This dict refers to the same mapping as the one for user quotas, and I 
think that's the way it should be. Please correct me if I'm wrong.

This creates a new user in the quota2 table, by the name of just the 
domain. Unfortunately, it simply fills it with the numbers from the 
first user it can find in that domain, not all users in that domain 
combined.

What I think I should do, is change something in the dict so that it 
sums up all users in that domain, something like this:

  SELECT SUM(bytes) FROM quota2 WHERE username LIKE '%@domain';

Problem is that I can't find anything useful online with regards to 
this, I'm not even sure if I'm thinking in the right direction here.

Any hints, suggestions or corrections? Or pointers to articles or 
documentation I might have missed?

Kind regards,

Hans

Detrich Bertalan

2022-Sep-10 07:58 UTC

head link

Unclear how to get both user and domain quotas to work

Hi,

This problem is not today as I saw it: 
https://dovecot.org/pipermail/dovecot/2012-February/081171.html> Domain-quota support in Dovecot is only partial. Recalculating domain quota
would require more code. Last time when someone asked about this I wasn't
even sure how it could be implemented, but nowadays it actually would be
possible to do with:
>
>   - set domain's quota to 0
>   - iterate *@domain users via userdb
>   - for each user get the user's quota and add it to domain quota
So, the suggested solution you wrote is good, this is how I solved it:

update the sql for summarize the domain usage count. You can do it once, 
beacuse after that, the dovecot is already good to counting the usage. 
The sql statements:

update quota2 as dest, (SELECT domain, sum(bytes) as summary FROM quota2 
t1 inner join (select domain from domain ) t2 on t1.username like concat 
("%@", t2.domain) group by domain) as src set dest.bytes = src.summary
where dest.username = src.domain ;

update quota2 as dest, (SELECT domain, sum(messages) as summary FROM 
quota2 t1 inner join (select domain from domain ) t2 on t1.username like 
concat ("%@", t2.domain) group by domain) as src set dest.messages = 
src.summary where dest.username = src.domain ;

There is one thing to be aware of:

Do not recalculate the quota usage with doveadm, because as you wrote: 
"it simply fills it with the numbers from the first user it can find in 
that domain, not all users in that domain combined."

if you did, you can update your db again. (or periodically from cron)

Best regards,

Bertalan

> From apprenti at sorcier.eu? Wed Oct 20 19:46:43 2021
> From: apprenti at sorcier.eu (Hans van Zijst)
> Date: Wed, 20 Oct 2021 18:46:43 +0200
> Subject: Unclear how to get both user and domain quotas to work
> Message-ID: <40304cf1-63c0-9795-8647-fe8946bdc2dd at sorcier.eu>
> Hi, 
> First of all: my apologies for this long message. I'm trying to post
all
> the information anybody could need to help me on my way.
>
> What I'm trying to make is an installation where both users and domains
> can have quotas. Ideally I would configure a quota for a domain and set
> a quota of 0 for all users within that domain, so that mail will be
> accepted up to the point where the domain quota is reached.
>
> Setting up user quota was fairly easy, there's lots of documentation
> around and examples to take inspiration from. Not so much for domain
> quota however, or I'm really bad at searching the Net.
>
> I'm running Debian Bullseye here:
>
> MariaDB 10.5.12
> Dovecot 2.3.13
> Postfixadmin 3.3.10
>
> I want to use dictionaries in MySQL, because eventually this will be a
> platform with several Dovecot and Postfix servers. At this time I'm
only
> running one Dovecot installation and a few Postfix servers (two for
> communication with the outside world and one to do the virus and spam
> scanning).
>
> My current setup for user quota is how Postfixadmin wants it:
>
>    MariaDB [postfixadmin]> desc quota2;
>    +----------+--------------+------+-----+---------+-------+
>    | Field    | Type         | Null | Key | Default | Extra |
>    +----------+--------------+------+-----+---------+-------+
>    | username | varchar(100) | NO   | PRI | NULL    |       |
>    | bytes    | bigint(20)   | NO   |     | 0       |       |
>    | messages | int(11)      | NO   |     | 0       |       |
>    +----------+--------------+------+-----+---------+-------+
>    3 rows in set (0.001 sec)
>
>
> In /etc/dovecot/conf.d/90-quota.conf I have this:
>
>    plugin {
>      quota = dict:User quota::proxy::sqlquota
>      quota_rule = *:bytes=1024M
>      quota_rule2 = Trash:bytes=+100M
>      quota_vsizes = yes
>      quota_status_success = DUNNO
>      quota_status_nouser = DUNNO
>      quota_status_overquota = "552 5.2.2 Mailbox is full"
>    }
>
>    dict {
>      sqlquota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
>    }
>
>
> The SQL-configuration /etc/dovecot/dovecot-dict-sql.conf.ext is this:
>
>
>    connect = host=... dbname=... user=... password=...
>    map {
>      pattern = priv/quota/storage
>      table = quota2
>      username_field = username
>      value_field = bytes
>    }
>    map {
>      pattern = priv/quota/messages
>      table = quota2
>      username_field = username
>      value_field = messages
>    }
>    map {
>      pattern = shared/expire/$user/$mailbox
>      table = expires
>      value_field = expire_stamp
>      fields {
>        username = $user
>        mailbox = $mailbox
>      }
>    }
>
> I use LMTP to accept mail from Postfix; the quota-plugin is activated
> for LMTP, so every incoming mail should be counted. This works like a
> charm: if I don't configure a quota for a user, he gets the default
1GB,
> if I override it in Postfixadmin, then that's what he gets.
>
> I'm running quota-status on a TCP-port, so the gateway SMTP-servers can
> connect to Dovecot for a quotacheck to decide whether to accept or
> reject an incoming message. Again, this works flawlessly.
>
> I can't find any serious documentation about this mapping thing. In
> particular, what's with that "pattern"? All I can find in the
> documentation is:
>
> "The dict key must match exactly priv/quota/storage. The dict keys are
> hardcoded in the Dovecot code, so depending on what functionality you?re
> configuring you need to know the available dict keys used it."
>
>     --https://doc.dovecot.org/configuration_manual/dict/#dict-sql
>
> If anyone can point me at better documentation than the actual source
> code: please! I browsed through the quota-plugin source code, but I'm
> not a programmer and couldn't find what I thought I was looking for.
>
> That said, I'm not sure where I got the third "map" from,
with the
> shared/expire pattern. I seem to have forgotten to document that bit of
> my journey.
>
> Now, domain quotas...
>
> I've tried all kinds of stuff, but at the moment I'm stuck with
> something that seems to almost work.
>
> I added a second quota root:
>
>    plugin {
>      quota2 = dict:Domain quota:%d:proxy::sqldomainquota
>      quota2_rule = *:bytes=1024M
>      quota2_status_success = DUNNO
>      quota2_status_nouser = DUNNO
>      quota2_status_overquota = "552 5.2.2 Domain quota exceeded"
>      quota2_vsizes = yes
>    }
>
>    dict {
>      sqldomainquota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
>    }
>
> This dict refers to the same mapping as the one for user quotas, and I
> think that's the way it should be. Please correct me if I'm wrong.
>
> This creates a new user in the quota2 table, by the name of just the
> domain. Unfortunately, it simply fills it with the numbers from the
> first user it can find in that domain, not all users in that domain
> combined.
>
> What I think I should do, is change something in the dict so that it
> sums up all users in that domain, something like this:
>
>    SELECT SUM(bytes) FROM quota2 WHERE username LIKE '%@domain';
>
> Problem is that I can't find anything useful online with regards to
> this, I'm not even sure if I'm thinking in the right direction
here.
>
> Any hints, suggestions or corrections? Or pointers to articles or
> documentation I might have missed?
>
> Kind regards,
>
> Hans-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<https://dovecot.org/pipermail/dovecot/attachments/20220910/49585def/attachment.htm>

dovecot - Oct 2021 - Unclear how to get both user and domain quotas to work

Unclear how to get both user and domain quotas to work

Unclear how to get both user and domain quotas to work