thr3ads.net - dovecot - [Dovecot] postfix/dovecot/mysql quotas not working, default dovecot.conf does. [Oct 2008]

If this information is useful, please help other people find it:
Share via:

dovecot at corwyn.net

2008-Oct-08 00:03 UTC

[Dovecot] postfix/dovecot/mysql quotas not working, default dovecot.conf does.

I'm having problems getting dovecot to set imap quotas that are 
defined as individual quotas for virtual users using mysql. I'm using 
postfix/dovecot 1.1.3, just built from source about a week ago.

I've been testing using telnet and getquotaroot. when I define the 
quota in dovecot.conf
   quota = dirsize
   quota_rule = *:storage=102400

I get the following response from Dovecot:

# telnet 127.0.0.1 143
Connected to 127.0.0.1.
* OK Dovecot ready.
. login test222 at example.com password
. OK Logged in.
. getquotaroot inbox
* QUOTAROOT "inbox" ""
* QUOTA "" (STORAGE 276273 102400)


All is good (the mailbox IS currently very full as part of my 
testing). However, when I define that data in the dovecot-sql.conf 
file, it doesn't appear to pick the data up, either as an override, 
or, if I delete
   quota_rule = *:storage=102400
from dovecot.conf, I get no quota at all
. getquotaroot inbox
. OK No quota.


# grep -v '^ *\(#.*\)\?$' dovecot-sql.conf
driver = mysql
connect = host=localhost dbname=postfix user=postfix password=password
default_pass_scheme = MD5-CRYPT
password_query = \
   SELECT username, domain, password \
   FROM mailbox WHERE username = '%u' AND domain = '%d' AND
active = '1'
user_query = \
    SELECT 1015 as uid, 105 as gid, '/var/spool/mail/%d/%n' as home, \
    'maildir:/var/spool/mail/%d/%n/Maildir' as mail, \
    'dirsize' as quota, \
    concat('*:storage=', quota, 'B') as quota_rule, \
         FROM mailbox WHERE username = '%u' AND domain = '%d'
and active = '1'


the results remain the same in telnet.


Any assistance would be appreciated. I've done a fair amount of 
digging, and have no idea why it's not working. ASsociated other 
configuration below.

Thanks!

Rick




SQL data:
mysql> show columns in mailbox;
+----------+--------------+------+-----+---------------------+-------+
| Field    | Type         | Null | Key | Default             | Extra |
+----------+--------------+------+-----+---------------------+-------+
| username | varchar(255) | NO   | PRI |                     |       |
| password | varchar(255) | NO   |     |                     |       |
| name     | varchar(255) | NO   |     | NULL                |       |
| maildir  | varchar(255) | NO   |     |                     |       |
| quota    | bigint(20)   | NO   |     | 0                   |       |
| domain   | varchar(255) | NO   |     |                     |       |
| created  | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| modified | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| active   | tinyint(1)   | NO   |     | 1                   |       |
+----------+--------------+------+-----+---------------------+-------+
9 rows in set (0.00 sec)


| test222 at example.com  | hashedpassword |              | 
example.com/test222/  | 153600000 | example.com | 2008-10-01 12:13:54 
| 2008-10-06 15:49:51 |      1 |




# dovecot -n
# 1.1.3: /usr/local/etc/dovecot.conf
protocols: imap imaps pop3 pop3s
ssl_disable: yes
ssl_cert_file: /etc/httpd/host.cert
ssl_key_file: /etc/httpd/host.key
disable_plaintext_auth: no
login_dir: /usr/local/var/run/dovecot/login
login_executable(default): /usr/local/libexec/dovecot/imap-login
login_executable(imap): /usr/local/libexec/dovecot/imap-login
login_executable(pop3): /usr/local/libexec/dovecot/pop3-login
mail_uid: 1015
mail_gid: 105
mail_location: maildir:/var/spool/mail/%d/%n/Maildir
mail_executable(default): /usr/local/libexec/dovecot/imap
mail_executable(imap): /usr/local/libexec/dovecot/imap
mail_executable(pop3): /usr/local/libexec/dovecot/pop3
mail_plugins(default): quota imap_quota
mail_plugins(imap): quota imap_quota
mail_plugins(pop3): quota
mail_plugin_dir(default): /usr/local/lib/dovecot/imap
mail_plugin_dir(imap): /usr/local/lib/dovecot/imap
mail_plugin_dir(pop3): /usr/local/lib/dovecot/pop3
imap_client_workarounds(default): delay-newmail
imap_client_workarounds(imap): delay-newmail
imap_client_workarounds(pop3):
pop3_client_workarounds(default):
pop3_client_workarounds(imap):
pop3_client_workarounds(pop3): outlook-no-nuls oe-ns-eoh
auth default:
   mechanisms: plain login
   passdb:
     driver: sql
     args: /usr/local/etc/dovecot-sql.conf
   userdb:
     driver: passwd
   userdb:
     driver: sql
     args: /usr/local/etc/dovecot-sql.conf
   userdb:
     driver: static
   socket:
     type: listen
     client:
       path: /var/run/dovecot/auth-client
       mode: 432
       user: vuser
       group: vuser
     master:
       path: /var/run/dovecot/auth-master
       mode: 384
       user: vuser
       group: vuser
plugin:
   quota: dirsize
   quota_rule: *:storage=102400
   quota_warning: storage=95%% /usr/local/sbin/quota_notify 95
   quota_warning2: storage=80%% /usr/local/sbin/quota_notify 80

Rick Steeves
http://www.sinister.net

"The journey is the destination"

Timo Sirainen

2008-Oct-08 19:27 UTC

head link

[Dovecot] postfix/dovecot/mysql quotas not working, default dovecot.conf does.

On Tue, 2008-10-07 at 20:03 -0400, dovecot at corwyn.net
wrote:> I've been testing using telnet and getquotaroot. when I define the 
> quota in dovecot.conf
>    quota = dirsize
Note that dirsize is superslow with maildir. quota=maildir is much
better.
> user_query = \
>     SELECT 1015 as uid, 105 as gid, '/var/spool/mail/%d/%n' as
home, \
>     'maildir:/var/spool/mail/%d/%n/Maildir' as mail, \
>     'dirsize' as quota, \
There's no need to duplicate "quota" here. It's already in
plugin
section.
>     concat('*:storage=', quota, 'B') as quota_rule, \
Looks correct. A bit simpler would be concat('*:bytes=',quota)

Enable auth_debug=yes and mail_debug=yes and look at the logs. It should
show quota_rule being sent as part of "master out" line.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: This is a digitally signed message part
URL:
<http://dovecot.org/pipermail/dovecot/attachments/20081008/4ba7372a/attachment-0002.bin>

dovecot at corwyn.net

2008-Oct-08 20:52 UTC

head link

[Dovecot] postfix/dovecot/mysql quotas not working, default dovecot.conf does.

(and oops forgot to get my last post on the list)
Hmm, I pulled a cleaner log file and got this:

Oct  8 16:44:51 webmail dovecot: auth(default): new auth connection: pid=14573
Oct  8 16:44:55 webmail dovecot: auth(default): client in: 
AUTH^I1^IPLAIN^Iservice=imap^Isecured^Ilip=127.0.0.1^Irip=127.0.0.1^Ilport=143^Irport=50346^Iresp=<hidden>
Oct  8 16:44:55 webmail dovecot: auth-worker(default): 
sql(test111 at example.com,127.0.0.1): query: SELECT username, domain, 
password FROM mailbox WHERE username = 'test111 at example.com' AND 
domain = 'example.com' AND active = '1'
Oct  8 16:44:55 webmail dovecot: auth(default): client out: 
OK^I1^Iuser=test111 at example.com
Oct  8 16:44:55 webmail dovecot: auth(default): master in: 
REQUEST^I28^I14248^I1
Oct  8 16:44:55 webmail dovecot: auth(default): 
passwd(test111 at example.com,127.0.0.1): lookup
Oct  8 16:44:55 webmail dovecot: auth(default): 
passwd(test111 at example.com,127.0.0.1): unknown user
Oct  8 16:44:55 webmail dovecot: auth-worker(default): 
sql(test111 at example.com,127.0.0.1): SELECT 1015 as uid, 105 as gid, 
'/var/spool/mail/example.com/test111' as home, 
'maildir:/var/spool/mail/example.com/test111/Maildir' as mail, 
concat('maildir:storage=', quota) as quota, concat('*:storage=',
quota, '') as quota_rule, FROM mailbox WHERE username = 
'test111 at example.com' AND domain = 'example.com' and active =
'1'
Oct  8 16:44:55 webmail dovecot: auth-worker(default): 
sql(test111 at example.com,127.0.0.1): User query failed: You have an 
error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'FROM mailbox 
WHERE username = 'test111 at example.com' AND domain = 'exampl'
at line 1
Oct  8 16:44:55 webmail dovecot: auth(default): master out: 
USER^I28^Itest111 at example.com
Oct  8 16:44:55 webmail dovecot: imap-login: Login: 
user=<test111 at example.com>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1,
secured
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Loading 
modules from directory: /usr/local/lib/dovecot/imap
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Module 
loaded: /usr/local/lib/dovecot/imap/lib10_quota_plugin.so
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Module 
loaded: /usr/local/lib/dovecot/imap/lib11_imap_quota_plugin.so
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Effective 
uid=1015, gid=105, homeOct  8 16:44:55 webmail dovecot: IMAP(test111 at
example.com): Quota
root: name=storage=10240 backend=maildir argsOct  8 16:44:55 webmail dovecot:
IMAP(test111 at example.com): Quota
rule: root=storage=10240 mailbox=* bytes=104857600 (0%) messages=0 (0%)
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Quota 
warning: bytes=99614720 (95%) messages=0 (0%) 
command=/usr/local/sbin/quota_notify 95
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): Quota 
warning: bytes=83886080 (80%) messages=0 (0%) 
command=/usr/local/sbin/quota_notify 80
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): maildir: 
data=/var/spool/mail/example.com/test111/Maildir
Oct  8 16:44:55 webmail dovecot: IMAP(test111 at example.com): 
maildir++: root=/var/spool/mail/example.com/test111/Maildir, index=, 
control=, inbox=/var/spool/mail/example.com/test111/Maildir


I see a sql error in there, but it doesn't make any sense. Why would 
it hack off the last part of the domain? (domain names have been 
changed to protect the innocent, it was truncated at 9 characters)

rick


At 04:29 PM 10/8/2008, Rick Steeves wrote:>At 03:27 PM 10/8/2008, Timo Sirainen wrote:
>>Note that dirsize is superslow with maildir. quota=maildir is much
>>better.
>
>Yeah, I knew that, but was trying the simplest option. I've now 
>switched everything over to maildir.
>
>> >     concat('*:storage=', quota, 'B') as
quota_rule, \
>>
>>Looks correct. A bit simpler would be concat('*:bytes=',quota)
>>
>>Enable auth_debug=yes and mail_debug=yes and look at the logs. It should
>>show quota_rule being sent as part of "master out" line.
>
>I enabled both of those. in /var/log/maillog I'm don't see a
"master" line.
>Here's the log files:
>
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Module 
>loaded: /usr/local/lib/dovecot/imap/lib10_quota_plugin.so
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Module 
>loaded: /usr/local/lib/dovecot/imap/lib11_imap_quota_plugin.so
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): 
>Effective uid=1015, gid=105, home>Oct  8 16:30:32 webmail dovecot:
IMAP(test111 at example.com): Quota
>root: name=storage=10240 backend=maildir args>Oct  8 16:30:32 webmail
dovecot: IMAP(test111 at example.com): Quota
>rule: root=storage=10240 mailbox=* bytes=104857600 (0%) messages=0 (0%)
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Quota 
>warning: bytes=99614720 (95%) messages=0 (0%) 
>command=/usr/local/sbin/quota_notify 95
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Quota 
>warning: bytes=83886080 (80%) messages=0 (0%) 
>command=/usr/local/sbin/quota_notify 80
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): maildir: 
>data=/var/spool/mail/example.com/test111/Maildir
>Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): 
>maildir++: root=/var/spool/mail/example.com/test111/Maildir, index=, 
>control=, inbox=/var/spool/mail/example.com/test111/Maildir
>
>
>I've been tweaking things as I try to figure it out, so I include 
>the current ones just in case it's critical to what I'm doing wrong
>
>I DO get dovecot to report quotas, they just don't match the quota 
>in sql (which for this user currently is 0:
>| test111 at example.com  | encryptedpassword | Test TEST    | 
>example.com/test111/  |         0 | example.com | 2008-09-30 17:24:2
>
>dovecot.conf:
>   quota = maildir:storage=10240
>   quota_rule = *:storage=102400
>   quota_warning = storage=95%% /usr/local/sbin/quota_notify 95
>   quota_warning2 = storage=80%% /usr/local/sbin/quota_notify 80
>
>dovecot-sql.conf:
>driver = mysql
>connect = host=localhost dbname=postfix user=postfix password=pfix1723
>default_pass_scheme = MD5-CRYPT
>password_query = \
>   SELECT username, domain, password \
>   FROM mailbox WHERE username = '%u' AND domain = '%d' AND
active = '1'
>user_query = \
>    SELECT 1015 as uid, 105 as gid, '/var/spool/mail/%d/%n' as home,
\
>    'maildir:/var/spool/mail/%d/%n/Maildir' as mail, \
>    concat('maildir:storage=', quota) as quota, \
>    concat('*:storage=', quota, '') as quota_rule, \
>         FROM mailbox WHERE username = '%u' AND domain =
'%d' and active = '1'
>
>
>
>Thanks
>
>Rick
>
>
>

dovecot at corwyn.net

2008-Oct-08 21:19 UTC

head link

[Dovecot] postfix/dovecot/mysql quotas not working, default dovecot.conf does.

dovecot-sql.conf:
driver = mysql
connect = host=localhost dbname=postfix user=postfix password=postfix
default_pass_scheme = MD5-CRYPT
password_query = \
   SELECT username, domain, password \
   FROM mailbox WHERE username = '%u' AND domain = '%d' AND
active = '1'
user_query = \
    SELECT 1015 as uid, 105 as gid, '/var/spool/mail/%d/%n' as home, \
    'maildir:/var/spool/mail/%d/%n/Maildir' as mail, \
    concat('*:storage=', quota, 'B') as quota_rule, \
         FROM mailbox WHERE username = '%u' AND domain = '%d'
and active = '1'

dovecot.conf
   quota = maildir
   quota_rule = *:storage=102400
   quota_warning = storage=95%% /usr/local/sbin/quota_notify 95
   quota_warning2 = storage=80%% /usr/local/sbin/quota_notify 80

Telnet:
Connected to 127.0.0.1.
Escape character is '^]'.
* OK Dovecot ready.
. login test111 at example.com Test111
. OK Logged in.
. getquotaroot inbox
* QUOTAROOT "inbox" ""
* QUOTA "" (STORAGE 67 102400)
. OK Getquotaroot completed.

Quota still shows what's in dovecot.conf

/var/log/maillog
Oct  8 17:16:23 webmail dovecot: auth(default): client in: 
AUTH^I1^IPLAIN^Iservice=imap^Isecured^Ilip=127.0.0.1^Irip=127.0.0.1^Ilport=143^Irport=49601^Iresp=<hidden>
Oct  8 17:16:23 webmail dovecot: auth-worker(default): 
sql(test111 at example.com,127.0.0.1): query: SELECT username, domain, 
password FROM mailbox WHERE username = 'test111 at example.com' AND 
domain = 'example.com' AND active = '1'
Oct  8 17:16:23 webmail dovecot: auth(default): client out: 
OK^I1^Iuser=test111 at example.com
Oct  8 17:16:23 webmail dovecot: auth(default): master in: 
REQUEST^I11^I23281^I1
Oct  8 17:16:23 webmail dovecot: auth(default): 
passwd(test111 at example.com,127.0.0.1): lookup
Oct  8 17:16:23 webmail dovecot: auth(default): 
passwd(test111 at example.com,127.0.0.1): unknown user
Oct  8 17:16:23 webmail dovecot: auth-worker(default): 
sql(test111 at example.com,127.0.0.1): SELECT 1015 as uid, 105 as gid, 
'/var/spool/mail/example.com/test111' as home, 
'maildir:/var/spool/mail/example.com/test111/Maildir' as mail, 
concat('*:storage=', quota, 'B') as quota_rule, FROM mailbox
WHERE
username = 'test111 at example.com' AND domain = 'example.com'
and active = '1'
Oct  8 17:16:23 webmail dovecot: auth-worker(default): 
sql(test111 at example.com,127.0.0.1): User query failed: You have an 
error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'FROM mailbox 
WHERE username = 'test111 at example.com' AND domain =
'example.c' at line 1
Oct  8 17:16:23 webmail dovecot: auth(default): master out: 
USER^I11^Itest111 at example.com
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Loading 
modules from directory: /usr/local/lib/dovecot/imap
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Module 
loaded: /usr/local/lib/dovecot/imap/lib10_quota_plugin.so
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Module 
loaded: /usr/local/lib/dovecot/imap/lib11_imap_quota_plugin.so
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Effective 
uid=1015, gid=105, homeOct  8 17:16:23 webmail dovecot: IMAP(test111 at
example.com): Quota
root: name= backend=maildir argsOct  8 17:16:23 webmail dovecot: IMAP(test111 at
example.com): Quota
rule: root= mailbox=* bytes=104857600 (0%) messages=0 (0%)
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Quota 
warning: bytes=99614720 (95%) messages=0 (0%) 
command=/usr/local/sbin/quota_notify 95
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): Quota 
warning: bytes=83886080 (80%) messages=0 (0%) 
command=/usr/local/sbin/quota_notify 80
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): maildir: 
data=/var/spool/mail/example.com/test111/Maildir
Oct  8 17:16:23 webmail dovecot: IMAP(test111 at example.com): 
maildir++: root=/var/spool/mail/example.com/test111/Maildir, index=, 
control=, inbox=/var/spool/mail/example.com/test111/Maildir
Oct  8 17:16:23 webmail dovecot: imap-login: Login: 
user=<test111 at example.com>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1,
secured


It looks like the error is in all likelihood here:
'/var/spool/mail/example.com/test111' as home, 
'maildir:/var/spool/mail/example.com/test111/Maildir' as mail, 
concat('*:storage=', quota, 'B') as quota_rule, FROM mailbox
WHERE
username = 'test111 at example.com' AND domain = 'example.com'
and active = '1'
Oct  8 17:16:23 webmail dovecot: auth-worker(default): 
sql(test111 at example.com,127.0.0.1): User query failed: You have an 
error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'FROM mailbox 
WHERE username = 'test111 at example.com' AND domain =
'example.c' at line 1

What's interesting is that it's not truncated in the line above it

wt?

Rick


At 04:46 PM 10/8/2008, Timo Sirainen wrote:>On Wed, 2008-10-08 at 16:29 -0400, dovecot at corwyn.net wrote:
> > >Enable auth_debug=yes and mail_debug=yes and look at the logs. It
should
> > >show quota_rule being sent as part of "master out" line.
> >
> > I enabled both of those. in /var/log/maillog I'm don't see a
"master" line.
>
>It should have been before the "Module loaded" etc stuff. Inside
the
>authentication. But anyway:
>
> > Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Quota
> > root: name=storage=10240 backend=maildir args>
>This looks wrong. Your quota root name is "storage=10240", which
really
>isn't what you want.
>
> > Oct  8 16:30:32 webmail dovecot: IMAP(test111 at example.com): Quota
> > rule: root=storage=10240 mailbox=* bytes=104857600 (0%) messages=0
(0%)
>
>This is ok.
>
> >     concat('maildir:storage=', quota) as quota, \
>
>Remove this entirely.
>
> >     concat('*:storage=', quota, '') as quota_rule, \
>
>You also broke this now. :)
>

Possibly Parallel Threads

Search for more apparently analagous threads

dovecot - Oct 2008 - postfix/dovecot/mysql quotas not working, default dovecot.conf does.

[Dovecot] postfix/dovecot/mysql quotas not working, default dovecot.conf does.

[Dovecot] postfix/dovecot/mysql quotas not working, default dovecot.conf does.

[Dovecot] postfix/dovecot/mysql quotas not working, default dovecot.conf does.

[Dovecot] postfix/dovecot/mysql quotas not working, default dovecot.conf does.

Possibly Parallel Threads