Quota tables are used for quota *tracking*. To specify quota for a user,
you need to use quota_rules. You can returns these from userdb, as
quota_rule=, quota_rule2=... etc. as documented in
https://wiki.dovecot.org/Quota/Configuration
Aki
On 28.03.2018 07:57, David Mehler wrote:
> 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 |
> +------------------------+-----------+----------+
> | u...@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:postmas...@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 = postmas...@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;
