Dovecot quota

David Mehler dave.mehler at gmail.com
Wed Mar 28 07:57:51 EEST 2018


Hello,

I'm running Dovecot on a FreeBSD system with Postfix in a virtual user
setup, with Mysql. I am trying to understand the quota configuration.

I've got a Mysql database with an accounts table with a quota field.
I've also got two other tables one quota (currently has nothing in it
an empty set), and quota2 messages and bytes which has one entry. My
goal is to have different quotas for each user so say one user has a
512MB quota I put 512 in the accounts quota column, while another user
might have 256MB, put 256 in the accounts quota column. These are just
examples. I'm assuming messages in the quota2 table track how many
messages are under that user's is it inbox or all folders in the
account? And bytes is that the space being taken up again by inbox or
by all messages in the account?

I'm also trying to have a separate quota for my public folders, which
is not working.

If anyone could take a look at this configuration see if it looks good
and maybe where public is not happening i'd appreciate it.

Thanks.
Dave.

Configuration:
mysql> describe accounts;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name             | varchar(255)     | NO   |     | NULL    |                |
| username         | varchar(64)      | NO   | MUL | NULL    |                |
| domain           | varchar(255)     | NO   | MUL | NULL    |                |
| password         | varchar(255)     | NO   |     | NULL    |                |
| quota            | int(10) unsigned | YES  |     | 0       |                |
| enabled          | tinyint(1)       | YES  |     | 0       |                |
| sendonly         | tinyint(1)       | YES  |     | 0       |                |
| last_login       | int(11)          | YES  |     | NULL    |                |
| last_login_ip    | varchar(16)      | YES  |     | NULL    |                |
| last_login_date  | datetime         | YES  |     | NULL    |                |
| last_login_proto | varchar(16)      | YES  |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

mysql> describe quota;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| username | varchar(255) | NO   | PRI | NULL    |       |
| path     | varchar(100) | NO   | PRI | NULL    |       |
| current  | bigint(20)   | NO   |     | 0       |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe 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.01 sec)

mysql> select * from quota;
Empty set (0.00 sec)

mysql> select * from quota2;
+------------------------+-----------+----------+
| username               | bytes     | messages |
+------------------------+-----------+----------+
| user at example.com | 171430625 |    20591 |
+------------------------+-----------+----------+
1 row in set (0.00 sec)

doveconf -n
# 2.2.35 (b1cb664): /usr/local/etc/dovecot/dovecot.conf
# Pigeonhole version 0.4.23 (b2e41927)
# OS: FreeBSD 11.1-RELEASE-p4 amd64
# Hostname: localhost
auth_cache_size = 24 M
auth_cache_ttl = 18 hours
auth_default_realm = example.com
auth_mechanisms = plain login
auth_realms = example.com
dict {
  acl = mysql:/usr/local/etc/dovecot/dovecot-dict-sql.conf.ext
  quota = mysql:/usr/local/etc/dovecot/dovecot-dict-sql.conf.ext
}
first_valid_gid = 999
first_valid_uid = 999
hostname = mail.example.com
imap_idle_notify_interval = 10 mins
last_valid_gid = 999
last_valid_uid = 999
lda_mailbox_autocreate = yes
lda_mailbox_autosubscribe = yes
lda_original_recipient_header = X-Original-To
listen = 127.0.0.1 xxx.xxx.xxx.xxx
log_path = /var/log/dovecot/dovecot.log
log_timestamp = "%Y-%m-%d %H:%M:%S "
mail_access_groups = vmail
mail_fsync = never
mail_gid = vmail
mail_home = /home/vmail/mailboxes/%d/%n
mail_location = maildir:~/mail:LAYOUT=fs
mail_plugins = acl mail_log notify quota quota_clone trash virtual welcome zlib
mail_privileged_group = vmail
mail_server_admin = mailto:postmaster at example.com
mail_uid = vmail
mailbox_idle_check_interval = 59 secs
mailbox_list_index = yes
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope
encoded-character vacation subaddress comparator-i;ascii-numeric
relational regex imap4flags copy include variables body enotify
environment mailbox date index ihave duplicate mime foreverypart
extracttext spamtest spamtestplus virustest editheader imapflags
notify imapsieve vnd.dovecot.imapsieve
namespace {
  location = maildir:/home/vmail/public/:CONTROL=~/mail/public:INDEX=~/mail/public
  mailbox TestFolder {
    auto = subscribe
    comment = Public Folder for message sharing
  }
  prefix = Public/
  separator = /
  subscriptions = yes
  type = public
}
namespace {
  list = children
  location = maildir:/home/vmail/mail/%%d/%%n:LAYOUT=fs:INDEX=/home/vmail/indexes/%d/%n/shared/%%u:INDEXPVT=/home/vmail/indexes/%d/%n/shared/%%u
  prefix = shared/%%d/%%n/
  separator = /
  subscriptions = no
  type = shared
}
namespace inbox {
  inbox = yes
  location =
  mailbox Archives {
    auto = subscribe
    special_use = \Archive
  }
  mailbox Drafts {
    auto = subscribe
    special_use = \Drafts
  }
  mailbox Sent {
    auto = subscribe
    special_use = \Sent
  }
  mailbox Spam {
    auto = subscribe
    autoexpunge = 30 days
    special_use = \Junk
  }
  mailbox Trash {
    auto = subscribe
    autoexpunge = 30 days
    special_use = \Trash
  }
  mailbox virtual/All {
    comment = All my messages
    special_use = \All
  }
  prefix =
  separator = /
  type = private
}
namespace virtual {
  location = virtual:/usr/local/etc/dovecot/virtual:INDEX=~/virtual:CONTROL=~/virtual
  prefix = virtual/
  separator = /
}
passdb {
  args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
plugin {
  acl = vfile
  acl_shared_dict = proxy::acl
  fts = lucene
  fts_autoindex = yes
  fts_autoindex_max_recent_msgs = 80
  fts_index_timeout = 90
  fts_lucene = whitespace_chars=@. normalize no_snowball
  imapsieve_mailbox1_before = file:/home/vmail/sieve/global/learn-spam.sieve
  imapsieve_mailbox1_causes = COPY
  imapsieve_mailbox1_name = Spam
  imapsieve_mailbox2_before = file:/home/vmail/sieve/global/learn-ham.sieve
  imapsieve_mailbox2_causes = COPY
  imapsieve_mailbox2_from = Spam
  imapsieve_mailbox2_name = *
  mail_log_events = delete undelete expunge copy mailbox_delete mailbox_rename
  mail_log_fields = uid box msgid size
  quota = dict:User quota::proxy::quota
  quota_exceeded_message = Storage quota for this account has been
exceeded, please try again later.
  quota_grace = 10%%
  quota_status_nouser = DUNNO
  quota_status_overquota = 552 5.2.2 Mailbox is full
  quota_status_success = DUNNO
  quota_vsizes = true
  quota_warning = storage=100%% quota-exceeded 100 %u
  quota_warning2 = storage=95%% quota-warning 95 %u
  quota_warning3 = storage=90%% quota-warning 90 %u
  quota_warning4 = storage=85%% quota-warning 85 %u
  quota_warning5 = storage=75%% quota-warning 75 %u
  sieve = ~/.dovecot.sieve
  sieve_before = /home/vmail/sieve/before.d
  sieve_default = /home/vmail/sieve/default.sieve
  sieve_dir = ~/sieve
  sieve_extensions = +notify +imapflags +spamtest +spamtestplus
+virustest +editheader
  sieve_global_dir = /home/vmail/sieve
  sieve_global_extensions = +vnd.dovecot.pipe +vnd.dovecot.execute
+vnd.dovecot.environment
  sieve_max_redirects = 30
  sieve_max_script_size = 1M
  sieve_pipe_bin_dir = /home/vmail/sieve
  sieve_plugins = sieve_imapsieve sieve_extprograms
  sieve_spamtest_max_header = X-Spamd-Result: default: [[:alnum:]]+
\[-?[[:digit:]]+\.[[:digit:]]+ / (-?[[:digit:]]+\.[[:digit:]]+)\]
  sieve_spamtest_status_header = X-Spamd-Result: default: [[:alnum:]]+
\[(-?[[:digit:]]+\.[[:digit:]]+) / -?[[:digit:]]+\.[[:digit:]]+\]
  sieve_spamtest_status_type = score
  sieve_user_log = /home/vmail/sieve/sieve_error.log
  sieve_virustest_status_header = X-Virus-Scan: Found to be (.+)\.
  sieve_virustest_status_type = text
  sieve_virustest_text_value1 = clean
  sieve_virustest_text_value5 = infected
  welcome_script = welcome %u
  welcome_wait = yes
}
postmaster_address = postmaster at example.com
protocols = imap lmtp sieve
sendmail_path = /usr/local/sbin/sendmail
service auth-worker {
  user = vmail
}
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0666
    user = postfix
  }
  unix_listener auth-userdb {
    group = vmail
    mode = 0666
    user = vmail
  }
}
service dict {
  unix_listener dict {
    group = vmail
    mode = 0660
    user = vmail
  }
  user = root
}
service imap-login {
  inet_listener imap {
    address = 127.0.0.1
    port = 143
  }
  inet_listener imaps {
    address = xxx.xxx.xxx.xxx
    port = 993
    ssl = yes
  }
}
service imap {
  executable = imap
}
service lmtp {
  unix_listener /var/spool/postfix/private/dovecot-lmtp {
    group = postfix
    mode = 0666
    user = postfix
  }
}
service managesieve-login {
  inet_listener sieve {
    address = 127.0.0.1
    port = 4190
  }
}
service quota-status {
  client_limit = 1
  executable = quota-status -p postfix
  unix_listener /var/spool/postfix/private/dovecot-quota {
    group = postfix
    mode = 0660
    user = postfix
  }
}
service quota-warning {
  executable = script /usr/local/etc/dovecot/quota-warning.sh
  unix_listener quota-warning {
    group = vmail
    mode = 0660
    user = vmail
  }
  user = vmail
}
service welcome {
  executable = script /usr/local/etc/dovecot/welcome.sh
  unix_listener welcome {
    user = vmail
  }
  user = vmail
}
ssl = required
ssl_cert = </usr/local/etc/ssl/acme/example.com/fullchain.pem
ssl_cipher_list =
ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256
ssl_dh_parameters_length = 4096
ssl_key =  # hidden, use -P to show it
ssl_options = no_compression
ssl_prefer_server_ciphers = yes
ssl_protocols = !SSLv2 !SSLv3 !TLSv1 !TLSv1.1 TLSv1.2
userdb {
  args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
protocol lmtp {
  mail_fsync = optimized
  mail_plugins = acl mail_log notify quota quota_clone trash virtual
welcome zlib quota acl sieve
}
protocol lda {
  mail_fsync = optimized
  mail_plugins = acl mail_log notify quota quota_clone trash virtual
welcome zlib quota acl sieve virtual
}
protocol imap {
  mail_max_userip_connections = 20
  mail_plugins = acl mail_log notify quota quota_clone trash virtual
welcome zlib quota acl imap_acl imap_quota imap_sieve imap_zlib
last_login virtual fts fts_lucene
}

dovecot-dict-sql.conf.ext
connect = host=SocketLocation dbname=DBName user=UserName password=PasswordHere

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/shared-boxes/user/$to/$from
  table = user_shares
  value_field = dummy

  fields {
    from_user = $from
    to_user = $to
  }
}

map {
  pattern = shared/shared-boxes/anyone/$from
  table = anyone_shares
  value_field = dummy

  fields {
    from_user = $from
  }
}

dovecot-sql.conf.extdriver = mysql
connect = host=SocketLocation dbname=DBName user=UserName password=PasswordHere
default_pass_scheme = SHA512-CRYPT
password_query = SELECT username AS user, domain, password FROM
accounts WHERE username = '%n' AND domain = '%d' and enabled = true
LIMIT 1;
user_query = SELECT concat('*:storage=', quota, 'M') AS quota_rule
FROM accounts WHERE username = '%n' AND domain = '%d' AND sendonly =
false;
iterate_query = SELECT username, domain FROM accounts where sendonly = false;


More information about the dovecot mailing list