dovecot - Jan 2010 - Dovecot(MYSQL)/Lda/Postfix/Postfixadmin and Quota limit per user

================== DOVECOT.CONF ====================# 1.2.8:
/usr/local/etc/dovecot.conf
# OS: FreeBSD 8.0-STABLE i386  ufs
log_path: /var/log/dovecot-error.log
info_log_path: /var/log/dovecot.log
protocols: imap imaps pop3 pop3s
ssl_cert_file: /etc/ssl/dovecot/cert.pem
ssl_key_file: /etc/ssl/dovecot/key.pem
disable_plaintext_auth: no
login_dir: /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
login_user: postfix
login_greeting: Server ready.
login_processes_count: 4
verbose_proctitle: yes
first_valid_uid: 6000
last_valid_uid: 6000
first_valid_gid: 6000
last_valid_gid: 6000
mail_privileged_group: mail
mail_location: maildir:/home/vmail/%d/%n/Maildir
mail_debug: yes
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 netscape-eoh 
tb-extra-mailbox-sep
imap_client_workarounds(imap): delay-newmail netscape-eoh 
tb-extra-mailbox-sep
imap_client_workarounds(pop3):
pop3_uidl_format(default): %08Xu%08Xv
pop3_uidl_format(imap): %08Xu%08Xv
pop3_uidl_format(pop3): %08Xu%08Xu
pop3_client_workarounds(default):
pop3_client_workarounds(imap):
pop3_client_workarounds(pop3): outlook-no-nuls oe-ns-eoh
lda:
  postmaster_address: postmaster at example.com
  log_path: /var/log/dovecot-deliver.log
  info_log_path: /var/log/dovecot-deliver.log
  mail_plugins: quota
  mail_plugin_dir: /usr/local/lib/dovecot/lda
  quota_full_tempfail: yes
  deliver_log_format: msgid=%m: %$
  sendmail_path: /usr/sbin/sendmail
  rejection_reason: Twoja wiadomosc do <%t> zostala automatycznie 
odrzucona:%n%r
  auth_socket_path: /var/run/dovecot/auth-master
auth default:
  mechanisms: plain login
  user: nobody
  username_format: %Lu
  verbose: yes
  debug: yes
  passdb:
    driver: sql
    args: /usr/local/etc/dovecot-sql.conf
  userdb:
    driver: static
    args: uid=6000 gid=6000 home=/home/vmail/%d/%n allow_all_users=yes
  userdb:
    driver: sql
    args: /usr/local/etc/dovecot-sql.conf
  socket:
    type: listen
    client:
      path: /var/spool/postfix/private/auth
      mode: 432
      user: postfix
      group: postfix
    master:
      path: /var/run/dovecot/auth-master
      mode: 432
      user: vmailer
      group: vmailer
plugin:
  quota: maildir:User quota
  quota_rule: *:storage=4M
  convert_mail: mbox:%h/mboxmail
dict:
  quotadict: mysql:/usr/local/etc/dovecot-dict-quota.conf

====================== DOVECOT-SQL.CONF ================driver = mysql
connect = host=localhost dbname=xxx user=xxx password=xxx
default_pass_scheme = MD5
user_query = SELECT '/home/vmail/%d/%n' as home, 
'maildir:/home/vmail/%d/%n' as mail, 6000 AS uid, 6000 AS gid, 
concat('*:bytes=', mailbox.quota) AS quota_rule FROM mailbox WHERE 
mailbox.username = '%u' AND active = '1'
password_query = SELECT password, '/home/vmail/%d/%n' as home, 
'maildir:/home/vmail/%d/%n' as mail, 6000 AS uid, 6000 AS gid, 
concat('dict:bytes=', quota) AS quota_rule FROM mailbox WHERE username =
'%u' AND active = '1'

=================== DOVECOT.LOG AND DELIVER LOG============================
[root at frodo /usr/local/etc]# tail -f /var/log/dovecot.log
Jan 06 15:07:38 auth(default): Info: new auth connection: pid=44519
Jan 06 15:07:38 auth(default): Info: master in: USER    1       
round at example.com     service=deliver
Jan 06 15:07:38 auth(default): Info: master out: USER   1       
round at example.com     uid=6000        gid=6000        
home=/home/vmail/example.com/round

[root at frodo /usr/local/etc]# tail -f /var/log/dovecot-deliver.log
Jan 06 15:07:38 deliver(round at example.com): Info: Module loaded: 
/usr/local/lib/dovecot/lda/lib10_quota_plugin.so
Jan 06 15:07:38 deliver(round at example.com): Info: auth input: uid=6000
Jan 06 15:07:38 deliver(round at example.com): Info: auth input: gid=6000
Jan 06 15:07:38 deliver(round at example.com): Info: auth input: 
home=/home/vmail/example.com/round
Jan 06 15:07:38 deliver(round at example.com): Info: Quota root: name=User 
quota backend=maildir argsJan 06 15:07:38 deliver(round at example.com): Info:
Quota rule: root=User
quota mailbox=* bytes=4194304 messages=0
Jan 06 15:07:38 deliver(round at example.com): Info: maildir: 
data=/home/vmail/example.com/round/Maildir
Jan 06 15:07:38 deliver(round at example.com): Info: maildir++: 
root=/home/vmail/example.com/round/Maildir, index=, control=, 
inbox=/home/vmail/example.com/round/Maildir
Jan 06 15:07:38 deliver(round at example.com): Info: Namespace : Using 
permissions from /home/vmail/example.com/round/Maildir: mode=0700 gid=-1
Jan 06 15:07:39 deliver(round at example.com): Info: 
msgid=<4B44987D.9020903 at gmail.com>: saved mail to INBOX

===================    SQL TABLE
================================+---------------------------+------------------------------------+------+----------------------------+-----------+------------+----------------+---------------------+-----
| username                  | password                           | name 
| maildir                    | quota     | local_part | domain         | 
created             | modified            | active |
+---------------------------+------------------------------------+------+----------------------------+-----------+------------+----------------+---------------------
| round at example.com |  1    | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx   |      
| example.com/round/       |   3072000 | round      | example.com  | 
2010-01-06 11:01:26 | +-------+

========================= DOVECOT restart ====================Stopping dovecot.
Starting dovecot.
ILoading modules from directory: /usr/local/lib/dovecot/imap
IModule loaded: /usr/local/lib/dovecot/imap/lib10_quota_plugin.so
IModule loaded: /usr/local/lib/dovecot/imap/lib11_imap_quota_plugin.so
IEffective uid=65534, gid=65534, home=/tmp
IQuota root: name=User quota backend=maildir argsIQuota rule: root=User quota
mailbox=* bytes=4194304 messages=0

================================= END 
========================================
Hey,
at first happy new year.  Above you can see my configuration. The 
problem is to set up quota limit per user using limits from mysql. What 
I understood from wiki-doc my quota_rule from mysql query should 
override default  quota rule - "quota_rule: *:storage=4M", but it aint
happen.
Question is what I did wrong ? :D

P.S. There is no possibility that something wrong is with database, 
cause earlier I have been testing quotadict and dovecot updated my quota 
fields (bytes/messages) in another table.

Best regards
and thx for any info

On 6.1.2010, at 16.45, leo wrote:
> passdb:
>   driver: sql
>   args: /usr/local/etc/dovecot-sql.conf
OK, password_query is read.
> userdb:
>   driver: static
>   args: uid=6000 gid=6000 home=/home/vmail/%d/%n allow_all_users=yes
This userdb static catches everything! The below userdb sql is never used
because of it, remove it.
> userdb:
>   driver: sql
>   args: /usr/local/etc/dovecot-sql.conf
Okay.
> user_query = SELECT '/home/vmail/%d/%n' as home,
'maildir:/home/vmail/%d/%n' as mail, 6000 AS uid, 6000 AS gid,
concat('*:bytes=', mailbox.quota) AS quota_rule FROM mailbox WHERE
mailbox.username = '%u' AND active = '1'
Looks ok, except I wouldn't make home = mail.
http://wiki.dovecot.org/VirtualUsers/Home
> password_query = SELECT password, '/home/vmail/%d/%n' as home,
'maildir:/home/vmail/%d/%n' as mail, 6000 AS uid, 6000 AS gid,
concat('dict:bytes=', quota) AS quota_rule FROM mailbox WHERE username =
'%u' AND active = '1'
The home, mail, uid, gid, quota_rules are useless here. They're not used for
anything. If you want to optimize, you could use prefetch userdb.
http://wiki.dovecot.org/UserDatabase/Prefetch