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