Simply mysql quota check

[Alfredo Saldanha]

Hi there,

I'm setting up a new email server with Dovecot(2.2.9) and MySQL.

My Dovecot configuration:

#/etc/dovecot/conf.d/90-quota.conf
service quota-warning {
executable = script /usr/local/bin/quota-warning.sh
user = dovecot
unix_listener quota-warning {
user = vmail
}
}

plugin {
quota = maildir:User quota
quota_warning = storage=95%% quota-warning 95 %u@%d
quota_warning2 = storage=80%% quota-warning 80 %u@%d
args = /etc/dovecot/dovecot-sql.conf.ext
}


#/etc/dovecot/dovecot-sql.conf.ext
password_query = \
SELECT username AS user, value AS password \
FROM mailboxes WHERE username = '%n@%d'

user_query = \
SELECT username, domain, \
CONCAT('*:storage=', SUBSTRING_INDEX(quota, 'm', 1)*1048576) AS userdb_quota_rule \
FROM mailboxes, domains WHERE mailboxes.username = '%n@%d' \
AND domains.domain = '%d'

My idea is when a message is delivered, the dovecot to check quota in the mysql according to the value received in the "user_query" but he does not.
It shows a value of 2GB that I don't know where it gets.

I execute a manual query in mysql to know the quota my test user and checked that are 5GB, but when I see the dovecot, it shows 2G.

SELECT username, domain, CONCAT('*:bytes=', SUBSTRING_INDEX(quota, 'm', 1)*1048576) AS userdb_quota_rule
FROM mailboxes, domains WHERE mailboxes.username = 'junix at domain.com' AND domains.domain = 'domain.com'

+-------------------+----------------+--------------------+
| username | domain | userdb_quota_rule |
+-------------------+----------------+--------------------+
| junix at domain.com | domain.com | *:bytes=5242880000 |
+-------------------+----------------+--------------------+

$ sudo doveadm quota get -u junix
Quota name Type Value Limit %
User quota STORAGE 10 2097152 0
User quota MESSAGE 1 -

How would the setting to simply verify the quota of users as userdb_quota_rule this query?

BR,

Alfredo Jr.

[Steffen Kaiser]

Post by Alfredo Saldanha
#/etc/dovecot/dovecot-sql.conf.ext
password_query = \
SELECT username AS user, value AS password \
user_query = \
SELECT username, domain, \
CONCAT('*:storage=', SUBSTRING_INDEX(quota, 'm', 1)*1048576) AS userdb_quota_rule \
AND domains.domain = '%d'
My idea is when a message is delivered, the dovecot to check quota in the mysql according to the value received in the "user_query" but he does not.
It shows a value of 2GB that I don't know where it gets.
I execute a manual query in mysql to know the quota my test user and checked that are 5GB, but when I see the dovecot, it shows 2G.
SELECT username, domain, CONCAT('*:bytes=', SUBSTRING_INDEX(quota, 'm', 1)*1048576) AS userdb_quota_rule
FROM mailboxes, domains WHERE mailboxes.username = 'junix at domain.com' AND domains.domain = 'domain.com'

1) your manual query uses '*:bytes=', whereas your conf uses '*:storage='.
2) you return the value as userdb_quota_rule, but it must read quota_rule
in the user_query. In the password_query fields are prefixed by userdb_ .

See http://wiki2.dovecot.org/Quota/Configuration

- --
Steffen Kaiser

[Alfredo Saldanha]

Steffen,

My problem was exactly what you said, the quota field in my query.
I fixed it and now is working very well.

Thank you very much.

Alfredo

----- Mensagem original -----
De: "Steffen Kaiser" <skdovecot at smail.inf.fh-brs.de>
Para: "Alfredo Saldanha" <asaldanha at infolink.com.br>
Cc: dovecot at dovecot.org
Enviadas: Quinta-feira, 2 de outubro de 2014 3:33:13
Assunto: Re: Simply mysql quota check

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Alfredo Saldanha
#/etc/dovecot/dovecot-sql.conf.ext
password_query = \
SELECT username AS user, value AS password \
user_query = \
SELECT username, domain, \
CONCAT('*:storage=', SUBSTRING_INDEX(quota, 'm', 1)*1048576) AS userdb_quota_rule \
AND domains.domain = '%d'
My idea is when a message is delivered, the dovecot to check quota in the mysql according to the value received in the "user_query" but he does not.
It shows a value of 2GB that I don't know where it gets.
I execute a manual query in mysql to know the quota my test user and checked that are 5GB, but when I see the dovecot, it shows 2G.
SELECT username, domain, CONCAT('*:bytes=', SUBSTRING_INDEX(quota, 'm', 1)*1048576) AS userdb_quota_rule
FROM mailboxes, domains WHERE mailboxes.username = 'junix at domain.com' AND domains.domain = 'domain.com'

1) your manual query uses '*:bytes=', whereas your conf uses '*:storage='.
2) you return the value as userdb_quota_rule, but it must read quota_rule
in the user_query. In the password_query fields are prefixed by userdb_ .

See http://wiki2.dovecot.org/Quota/Configuration

- --
Steffen Kaiser