[Dovecot] Please help with Quota dict mysql configuration

[Johan Hendriks]

Denis Iskandarov schreef:
> Thanks for your samples, they would help me so much!
> My config is commented because i didn't know how to properly configure
> it, didn't understand theory to start doing things on practice.
> can you show me your dovecot-mysql.conf as well ? it should be final
> peace of puzzle for me.
> i'm interested in this two almost different user_query lines:
>
> user_query = SELECT concat('/home/vmail/', maildir) as home,
> concat('maildir:/home/vmail/', maildir) as mail, 1001 AS uid, 12 AS
> gid, concat('maildir:storage=', quota) AS quota FROM mailbox WHERE
> username = '%u' AND active = '1'
>
> user_query = SELECT maildir, 1001 AS uid, 1001 AS gid,
> CONCAT('dict:storage=',floor(quota/1000),' proxy::quota') as quota
> FROM mailbox WHERE username = '%u' AND active='1'
>
> first one provided from dovecot wiki and second one from postfixadmin
> docs regarding dovecot configuration.
> may i use user_query like this? :
>
> user_query = SELECT concat('/home/vmail/', maildir) as home,
> concat('maildir:/home/vmail/', maildir) as mail, 1001 AS uid, 12 AS
> gid, CONCAT('dict:storage=',floor(quota/1000),' proxy::quota') AS
> quota FROM mailbox WHERE
> username = '%u' AND active = '1'
>
> seems like this line doing some nice customization showing quota in
> kilobytes and not in bytes. or if someone could explain what does this
> string means.
> also what does this line means ?:
> concat('maildir:/home/vmail/', maildir) as mail
> string about home i understood dovecot reads users home dir from this line.
> And i dont understand if why should i indicate in 10-mail.conf next string:
> mail_location = maildir:/home/vmail/%d/%u
> If it is overided by user_query from mysql?
>
> Also what is meaning of quota(2) table. does dovecot reading
> configuration from there or just stores usage information ?
> Also what does this string mean?: pattern = priv/quota/messages
> what privileges those who reads the, or where are they indicated ?
>
> Thanks in advance.
> Denis.
>
>> Maybe stupid, but are these  plugin examples really from your dovecot.conf
>> file ?
>> If so remove the # before the quota lines!
>>
>> this is my part from dovecot.conf (only relavant quota parts.)
>>
>>   quotadict = mysql:/usr/local/etc/dovecot/dovecot-dict-quota.conf
>>
>> plugin {
>> # Quota config
>>   quota = dict:User quota::noenforcing:proxy::quotadict
>>   quota_rule = *:storage=1G
>>   quota_rule2 = Trash:storage=+100M
>>   quota_warning = storage=95%% quota-warning 95 %u
>>   quota_warning2 = storage=90%% quota-warning 90 %u
>>   quota_warning3 = storage=80%% quota-warning 80 %u
>>   #OTHER Plugins
>> ....
>> ....
>> }
>>
>> service quota-warning {
>>   executable = script /usr/local/bin/quota-warning.sh
>>   user = vmail
>>   unix_listener quota-warning {
>>   user = vmail
>>   }
>> }
>>
>> protocol imap {
>>   imap_client_workarounds = delay-newmail tb-extra-mailbox-sep
>>   imap_idle_notify_interval = 120 s
>>   imap_logout_format = bytes=%i/%o
>>   imap_max_line_length = 65536
>>   mail_plugins = quota imap_quota autocreate acl imap_acl mail_log notify fts
>> fts_squat
>> }
>>
>> protocol lmtp {
>>   auth_socket_path = /var/run/dovecot/auth-master
>>   mail_plugins = sieve quota acl mail_log notify
>>   postmaster_address = postmaster at yourdomain.com
>>   sendmail_path = /usr/local/sbin/sendmail
>> }
>>
>> Above protocol lmtp could be protocol lda in your case
>>
>> This is my dovecot-dict-quota.conf  file
>>
>> # Dovecot 2.0.x
>> connect = host=192.xxx.xxx.xxx dbname=postfix user=postfix
>> password=mypostfixpasswd
>> 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
>> }
>>
>> This is my /usr/local/bin/quota-warning.sh file
>>
>> #!/usr/local/bin/bash
>> PERCENT=$1
>> USER=$2
>> cat<<  EOF | /usr/local/libexec/dovecot/deliver -d $USER -o
>> "plugin/quota=maildir:User quota:noenforcing"
>> From: support at yourdomain.com
>> Subject: quota warning
>>
>> Uw mailbox is momenteel voor $PERCENT% gevult.
>> Verwijder oude mail, of vraag een verruiming van uw quota aan.
>>
>> Your mailbox is now $PERCENT% full.
>> Please remove some old mail, or ask for a larger quota.
>>
>> Skrzynka pocztowa jest w tym momencie w $PERCENT% zapelniona.
>> Usun stare wiadomosci,albo przenies w inny folder.
>>
>> EOF
>>
>> Hope this helps.
>>
>> Regards,
>> Johan Hendriks
>> Double L Automatisering
Here it is.

# Database driver: mysql, pgsql
driver = mysql

# Currently supported schemes include PLAIN, PLAIN-MD5, DIGEST-MD5, and 
CRYPT.
default_pass_scheme = CRYPT

# Database options
connect = host=192.xxx.xxx.xxx dbname=postfix user=postfix 
password=mypostfixpassword

password_query = select password \
   from mailbox where username = '%u' \
   and active = '1'

user_query = select maildir, \
   concat('*:messages=50000:bytes=', quota) as quota_rule \
   from mailbox where username = '%u' and active = '1'


Gr
Johan