dovecot - Oct 2012 - still having difficulties with per-user quotas

Hello,

I am trying to get per-user quotas working. My thanks to all who have
helped so far.

To recap I am running Dovecot 2.1 and Mysql where I've got my virtual
users. All virtual users are under the system user vmail with a UID
and GID of 5000. Looking over the wiki docs I've added a quota table
and got the dict service working, I am not having problems with
permissions or the login username and password, all that is working
fine. Here's my current doveconf -n output it is producing the
following debug error related to the userdb sql query:

# 2.1.10: /etc/dovecot/dovecot.conf
# XXX
dict {
  quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
first_valid_gid = 5000
first_valid_uid = 5000
hostname = XXX
last_valid_gid = 5000
last_valid_uid = 5000
mail_location = maildir:/home/vmail/%d/%n:LAYOUT=fs
mail_plugins = " quota"
namespace inbox {
  inbox = yes
  location   prefix }
passdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
plugin {
  quota = dict:User quota::proxy::quota
  quota_rule = *:storage=1G
  quota_rule2 = Trash:storage=+100M
  quota_warning = storage=95%% quota-warning 95 %u
  quota_warning2 = storage=80%% quota-warning 80 %u
}
protocols = imap
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0660
    user = postfix
  }
  unix_listener auth-userdb {
    mode = 0600
    user = vmail
  }
}
service dict {
  unix_listener dict {
    mode = 0600
    user = vmail
  }
}
service imap-login {
  inet_listener imap {
    address = 127.0.0.1 ::1
  }
  inet_listener imaps {
    address = xxx xxxx
    ssl = yes
  }
}
service quota-warning {
  executable = script /usr/local/bin/quota-warning.sh
  user = vmail
}
ssl_cert = </etc/ssl/certs/server.crt
ssl_key = </etc/ssl/private/server.key
userdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
protocol imap {
  mail_plugins = " quota imap_quota"
}

Oct 19 15:23:52 auth-worker(29279): Info:
mysql(/var/run/mysqld/mysqld.sock): Connected to database mail
Oct 19 15:23:52 auth-worker(29279): Debug: sql(xxx,::1): query: SELECT
user as user, password FROM virtual_users WHERE user='xxx at domain';
Oct 19 15:23:52 auth: Debug: client passdb out: OK	1	user=xxx
Oct 19 15:23:52 auth: Debug: master in:
REQUEST	3523346433	29276	1	f5cd16e3f5e078c28d850749e42c86cc
Oct 19 15:23:52 auth-worker(29279): Debug: sql(xxx,::1): SELECT
concat('*:storage=', bytes, 'M') as quota_rule FROM quota WHERE
username='xxx';
Oct 19 15:23:52 auth: Debug: master userdb out:
USER	3523346433	xxx	quota_rule=*:storage=368485M
Oct 19 15:23:52 imap-login: Info: Login: user=<xxx>, method=PLAIN,
rip=::1, lip=::1, mpid=29282, secured,
session=</xKpcW7MFgAAAAAAAAAAAAAAAAAAAAAB>
Oct 19 15:23:52 imap(xxx): Error: user xxx: Couldn't drop privileges:
User is missing UID (see mail_uid setting)
Oct 19 15:23:52 imap(xxx): Error: Internal error occurred. Refer to
server log for more information.

I am wanting a majority of my users to have the global 1GB quota, but
the users in the quota table to have given quotas. Here's what the
virtual_users and quota tables look like:

mysql> describe virtual_users;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| domain_id | int(11)      | NO   | MUL | NULL    |                |
| user      | varchar(40)  | NO   |     | NULL    |                |
| password  | varchar(128) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> describe quota;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| username | varchar(100) | NO   | PRI | NULL    |       |
| bytes    | bigint(20)   | NO   |     | 0       |       |
| messages | int(11)      | NO   |     | 0       |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

I'd appreciate any help.

Thanks.
Dave.

David Mehler wrote:
> Oct 19 15:23:52 imap(xxx): Error: user xxx: Couldn't drop privileges:
User is missing UID (see mail_uid setting)
Set the following options in your dovecot.conf:

mail_uid = vmail
mail_gid = vmail

Also see section "Mail users" at
http://wiki2.dovecot.org/UserIds

Regards
Daniel
-- 
https://plus.google.com/103021802792276734820

David Mehler wrote:
> Thanks for your reply. So with the extending of the query to return a
> default quota rule, do you have an example of that by the way, does
> that mean I only have to put the overrided users in the quota table?
Assuming that quota values are in the dovecot_users table...

# passdb with userdb prefetch and default quota of 1024M for quota=0 rows
# The userdb_ prefix is for prefetch userdb entries in password_query
password_query = SELECT username AS user, \
  password AS password, \
  home AS userdb_home, \
  uid AS userdb_uid, \
  gid AS userdb_gid, \
  CASE quota \
    WHEN 0 \
      THEN '*:bytes=1024M:messages=0' \
    ELSE \
      CONCAT('*:bytes=', CAST(quota AS CHAR), 'M:messages=',
CAST(quota_message AS CHAR)) \
  END AS `userdb_quota_rule` \
  FROM dovecot_users \
  WHERE username='%u';

# user_query with default quota of 1024M for quota=0 rows
user_query = SELECT username AS user, \
  home AS home, \
  uid AS uid, \
  gid as gid, \
  CASE quota \
    WHEN 0 \
      THEN '*:bytes=1024M:messages=0' \
    ELSE \
      CONCAT('*:bytes=', CAST(quota AS CHAR), 'M:messages=',
CAST(quota_message AS CHAR)) \
  END AS `quota_rule` \
  FROM dovecot_users \
  WHERE username='%u';

Your user_query needs to return a row if the user exists,
otherwise dovecot will assume that the user does not exist
and the mail or user will be rejected.

Regards
Daniel
-- 
https://plus.google.com/103021802792276734820

Hi Dave,

David Mehler wrote:
> Thanks for all your help so far. I have per-user quotas now working, I
> had to also alter my dict config file as well. I am having two
> outstanding issues, actually one outstanding issue and one question.
> 
> Here's the question, given that the userdb sql query returns a default
> quota entry for rows of zero in quota and quota_messages is the
> default quota section needed in 90-quota.conf?
>
> plugin {
>   quota_rule = *:storage=1G
>   quota_rule2 = Trash:storage=+100M
> }
You need quota_rule2 to give the user some additional space
in the Trash folder if he/she wants to delete messages when over quota.
The dict is also needed for quota lookup from the database.

The only thing which might be omitted is the global quota_rule
since it is returned by the userdb/passwd in any case, but I'm
not sure what happens if you only configure a "quota_rule2" without
configuring a "quota_rule".
> My outstanding issue is whenever I as the root mysql user update a
> user's quota the other user also gets an update, I noticed with one
> the messages column on the other user went from 0 to 2, another time
> the quota value went up from 0 to 3500 it seems random.
You should not be accounting the actual mailbox usage in the
same virtual_users table as the quota is read from.

Use *different* column or table name in your dict file
where dovecot may write the current storage/message count.
> dovecot-dict-sql.conf.ext
> 
> map {
>   pattern = priv/quota/storage
>   table = virtual_users
>   username_field = user
>   value_field = quota
value_field should be current_quota_storage (writable column)
> }
> map {
>   pattern = priv/quota/messages
>   table = virtual_users
>   username_field = user
>   value_field = quota_messages
value_field should be current_quota_messages (writable column)
> I'd like to know why these columns are updating.
Dovecot stores the current storage and mailcount in there.
These columns should be different from the columns defining the maximum limit.

Regards
Daniel

David Mehler wrote:
> Thanks, so if I understand what your saying the reason I'm getting the
> column update issues is Dovecot is reading from and writing to the
> quota and quota_messages columns in my virtual_users table?
> 
> My database user I believe only has select permissions on that table.
> 
> So, I either need another table and to adjust my dovecot-dict.sql file
> for that table, that's where Dovecot will write to, or two more
> columns in the virtual_users table?
> 
> Which way do you recommend?
I would recommend to create a new table for dovecot_usage
where dovecot is granted write permission.

Regards
Daniel