dovecot - Jun 2011 - Please help with Quota dict mysql configuration

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.

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

After doing some configrations i got errors with quota and my setup is
brocken now:
dict:
dict {
  quotadict = mysql:/etc/dovecot/conf.d/sql/dovecot-dict-sql.conf
#  expire = sqlite:/etc/dovecot/dovecot-dict-sql.conf
}

dovecot-dict-sql.conf:
connect = host=localhost dbname=postfix user=postfix password=postfixadmin
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
}

quota plugin config:
plugin {
  quota = dict:User quota::proxy::quotadict
  quota_rule = *:storage=1G
  quota_rule2 = Trash:storage=+100M
}

user_query field:
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::quotadict')
AS quota
FROM mailbox WHERE username = '%u' AND active = '1'

Errors log:
dovecot.log:
Jun 24 11:41:24 imap: Debug: Loading modules from directory: /usr/lib/dovecot
Jun 24 11:41:24 imap: Debug: Module loaded:
/usr/lib/dovecot/lib10_quota_plugin.so
Jun 24 11:41:24 imap: Debug: Module loaded:
/usr/lib/dovecot/lib11_imap_quota_plugin.so
Jun 24 11:41:24 imap: Debug: Module loaded:
/usr/lib/dovecot/lib20_autocreate_plugin.so
Jun 24 11:41:24 imap: Debug: Added userdb setting:
mail=maildir:/home/vmail/office.dev/test/
Jun 24 11:41:24 imap: Debug: Added userdb setting:
plugin/quota=dict:storage=10240 proxy::quotadict
Jun 24 11:41:24 imap(test at office.dev): Debug: Effective uid=1001,
gid=12, home=/home/vmail/office.dev/test/
Jun 24 11:41:24 imap(test at office.dev): Debug: Quota root:
name=storage=10240 proxy backend=dict args=:quotadict
Jun 24 11:41:24 imap(test at office.dev): Debug: Quota rule:
root=storage=10240 proxy mailbox=* bytes=1073741824 messages=0
Jun 24 11:41:24 imap(test at office.dev): Debug: Quota rule:
root=storage=10240 proxy mailbox=Trash bytes=+104857600 messages=0
Jun 24 11:41:24 imap(test at office.dev): Debug: dict quota:
user=test at office.dev, uri=quotadict, noenforcing=0
Jun 24 11:41:24 imap(test at office.dev): Error: Dictionary URI is
missing ':': quotadict
Jun 24 11:41:24 imap(test at office.dev): Error: user test at office.dev:
Initialization failed: Failed to initialize quota: Quota root
storage=10240 proxy: init() failed
Jun 24 11:41:24 imap(test at office.dev): Error: Invalid user settings.
Refer to server log for more information.
Jun 24 11:41:24 imap-login: Info: Login: user=<test at office.dev>,
method=PLAIN, rip=192.168.0.11, lip=192.168.0.31, mpid=4611, TLS
Jun 24 11:41:24 imap-login: Warning: SSL alert: where=0x4008, ret=256:
warning close notify [192.168.0.11]

# doveadm quota recalc
doveadm(root): Error: chdir(/root/) failed: Permission denied
(euid=1001(vmail) egid=12(mail) missing +x perm: /root, euid is not
dir owner)
doveadm(root): Error: chdir(/root) failed: Permission denied
doveadm(root): Error: net_connect_unix(/var/run/dovecot//dict) failed:
Permission denied (euid=1001(vmail) egid=12(mail) missing +r perm:
/var/run/dovecot//dict, euid is not dir owner)

why this command working in my home /root dir ?
i have done next thing after:
service dict {
  unix_listener dict {
    mode = 0660
    user = vmail
    group = mail
  }
}
now i'm getting only next:
~]# doveadm quota recalc
doveadm(root): Error: chdir(/root/) failed: Permission denied
(euid=1001(vmail) egid=12(mail) missing +x perm: /root, euid is not
dir owner)
doveadm(root): Error: chdir(/root) failed: Permission denied

Whats wrong.
Please help.