[Dovecot] Quota + MySQL

[Matt Rude]

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 at 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=<lists at mattrude.com mythtv at mattrude.com
> From=akstcaloprestimnsdgs at alopresti.com File=/tmp/clamav/virus.7Q
>  >, 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 at 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