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@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@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@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 =
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;