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
[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 = dirsizeNote 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
[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
[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. :)
>