Re: [Dovecot] Quota + MySQL

4 Feb 2008


      Matt Rude wrote:
...
Hello, Im having problems getting Dovecot to read the quota info from
MySQL. My current dovecot.conf file is below.
If I run the user_query of:
user_query = SELECT 1000 AS uid, 1000 AS gid, maildir AS home FROM
mailbox WHERE username = '%u' AND active = '1'
Everything works fine but if I run the user_query of:
user_query = SELECT 1000 AS uid, 1000 AS gid, maildir AS home,
concat('quota=maildir:storage=', floor( mailbox.quota / 1024 )) AS quota
FROM mailbox WHERE username = '$u' AND active = '1'
if I were to run the query from MySQL the I get:
+------+------+----------------------------------+------------------------------+
| uid | gid | home | quota |
+------+------+----------------------------------+------------------------------+
| 1000 | 1000 | mattrude.com/lists@mattrude.com/ |
quota=maildir:storage=512000 |
+------+------+----------------------------------+------------------------------+
If I run the above query I am unable to log into the server:
Feb 4 20:06:11 dovecot: imap-login: Internal login failure:
user=
...
, method=PLAIN, rip=192.168.1.10, lip=192.168.1.1, TLS
## Dovecot configuration file
#protocols = imap imaps pop3 pop3s
protocols = imap imaps
login_user = postfix
listen =
ssl_listen =
ssl_disable = no
ssl_cert_file = /etc/pki/dovecot/certs/dovecot.pem
ssl_key_file = /etc/pki/dovecot/private/dovecot.pem
auth_cache_size = 128
auth_cache_ttl = 600
mail_location =
maildir:/var/spool/virtualmailboxes/%d/%u/imap/:INBOX=/var/spool/virtualmailboxes/%d/%u/:INDEX=/var/spool/virtualmailboxes/%d/%u/imap/index/
protocol imap {
mail_plugins = quota imap_quota
}
protocol pop3 {
mail_plugins = quota
}
protocol lda {
postmaster_address = postmaster@mattrude.com
hostname = samantha.mattrude.com
mail_plugins = cmusieve
mail_plugins = quota
sieve_global_path = /var/spool/sieve/dovecot.sieve
mail_plugin_dir = /usr/lib/dovecot/lda
auth_socket_path = /var/run/dovecot/auth-master
}
auth default {
mechanisms = plain digest-md5 cram-md5
userdb sql {
args = /etc/dovecot-mysql.conf
}
passdb sql {
args = /etc/dovecot-mysql.conf
}
user = root
count = 2
}
dict {
}
plugin sql {
quota = maildir:storage=1048576
quota = maildir:ignore=Trash
}
If anyone sees what im missing please let me know
Thanks
-Matt
To answer my own question. Here is the correct user_query entry for
MySQL and quotas.
<--This must be on one line for v1.0.10-->
user_query = SELECT maildir, 1000 AS uid, 1000 AS gid,
CONCAT('maildir:storage=', ROUND( mailbox.quota / 1024 ) ) AS quota FROM
mailbox WHERE username = '%u' AND active = '1'
-Matt