[Dovecot] Please help with Quota dict mysql configuration

[Johan Hendriks]

Denis Iskandarov schreef:
> Hello
> So what i have:
> centos 5.6
> postfix 2.3.3
> dovecot 2.0.13
> postfixadmin 2.3.3
> roundcube 0.5.3
>
> All general things are working right now i can send receive email with
> ssl connections etc...
> Now i'm trying to configure additional options like quota and some
> other plugins like sieve, trash, expire and deleted-to-trash.
> Configuration of quota is on first place. I have read wiki regarding
> quota config , dict config but can't make things running.
> Hope there are normal sample configs you can share with me.
> I'm total noob in sql, so it's very difficult for me to arrange stuff
> in a way i need.
>
> My configs:
>
> dict {
>    quota = mysql:/etc/dovecot/conf.d/sql/dovecot-dict-sql.conf
> #  expire = sqlite:/etc/dovecot/dovecot-dict-sql.conf
> }
>
> dovecot-dict-sql.conf sample i took from postfixadmin docs provided
> with package, also changed table to quota2 as suggested for dovecot 2:
> driver = mysql
> connect = host=localhost dbname=postfix user=postfix password=postfixadmin
> default_pass_scheme = MD5-CRYPT
> table = quota2
> select_field = current
> where_field = path
> username_field = username
>
> my sql.conf i took it from tutorial on your wiki : LDA postfix postfixadmin:
>
> driver = mysql
>
> connect = host=localhost dbname=postfix user=postfix password=postfixadmin
>
> 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'
>
> password_query = SELECT username as user, password,
> concat('/home/vmail/', maildir) as userdb_home,
> concat('maildir:/home/vmail/', maildir) as userdb_mail, 1001 as
> userdb_uid, 12 as userdb_gid FROM mailbox WHERE username = '%u' AND
> active = '1'
>
> The second user_query string i took from postfixadmin docs. But i dont
> know how to aply it. If it is better to use with mysql/postfixadmin
>
> and after this all i dont how to configure quota.conf (90-quota.conf):
>
> plugin {
>    #quota = dirsize:User quota
>    #quota = maildir:User quota
>    #quota = dict:User quota::proxy::quota
>    #quota = fs:User quota
> }
>
> # Multiple quota roots are also possible, for example this gives each user
> # their own 100MB quota and one shared 1GB quota within the domain:
> plugin {
>    #quota = dict:user::proxy::quota
>    #quota2 = dict:domain:%d:proxy::quota_domain
>    #quota_rule = *:storage=102400
>    #quota2_rule = *:storage=1048576
> }
>
> Please help me with configuration. I really stuck with this thing. And
> yes i've tried to find solutions on mailing list, but no luck.
> I want per domain/user quota in mysql. Also space usage to be shown in
> roundcube.
> Hope to get proper help  ...
> And sorry...
> Thanks in advance.
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