Unclear how to get both user and domain quotas to work

[Hans van Zijst]

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