Unclear how to get both user and domain quotas to work

Sat Sep 10 07:58:10 UTC 2022

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>