<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
Hi,<br>
<br>
This problem is not today as I saw it:
<a class="moz-txt-link-freetext" href="https://dovecot.org/pipermail/dovecot/2012-February/081171.html">https://dovecot.org/pipermail/dovecot/2012-February/081171.html</a><br>
<blockquote type="cite">
<pre>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</pre>
</blockquote>
<br>
So, the suggested solution you wrote is good, this is how I solved
it: <br>
<br>
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: <br>
<br>
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 ; <br>
<br>
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 ; <br>
<br>
There is one thing to be aware of: <br>
<br>
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."<br>
<br>
if you did, you can update your db again. (or periodically from
cron) <br>
<br>
Best regards, <br>
<br>
Bertalan <br>
<br>
<br>
<blockquote type="cite">From apprenti at sorcier.eu Wed Oct 20
19:46:43 2021<br>
From: apprenti at sorcier.eu (Hans van Zijst)<br>
Date: Wed, 20 Oct 2021 18:46:43 +0200<br>
Subject: Unclear how to get both user and domain quotas to work<br>
Message-ID:
<a class="moz-txt-link-rfc2396E" href="mailto:40304cf1-63c0-9795-8647-fe8946bdc2dd@sorcier.eu"><40304cf1-63c0-9795-8647-fe8946bdc2dd@sorcier.eu></a></blockquote>
<blockquote type="cite">Hi,
</blockquote>
<blockquote type="cite">
<pre>
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."
-- <a href="https://doc.dovecot.org/configuration_manual/dict/#dict-sql" class="moz-txt-link-freetext" moz-do-not-send="true">https://doc.dovecot.org/configuration_manual/dict/#dict-sql</a>
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
</pre>
</blockquote>
</body>
</html>