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
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@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-sqlIf 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
participants (2)
-
Detrich Bertalan
-
Hans van Zijst