no login with MySQL

[Tobias Franzén]

On 2015-09-13 22:52, Andreas Meyer wrote:
> Christian Kivalo <ml+dovecot at valo.at> schrieb am 13.09.15 um 22:08:04 Uhr:
>
>>> I think the query-string für MySQL would overwrite this mail_location,
>>> right? But how do I define it in the query?
>> yes thats how it should work but your query (looking at the query result
>> from your first email) overwrites the mail_location with
>> "/var/spool/vhosts/nimmini.de/" without a username.
>>
>>>> With my setup "mail" and "home" is returned from the userdb query from
>>>> mysql
>>>>
>>>> home: /srv/mail/%u
>>>> mail: /srv/mail/%u/Maildir
>>> The query-strings for virtual users look like this:
>>>
>>> password_query = SELECT username AS username, password FROM mailbox
>>> WHERE username = '%u' AND active = 1
>>> user_query = SELECT maildir, uid, gid, concat('*:storage=', quota) AS
>>> quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM
>>> mailbox WHERE username = '%u'
>> what about
>> user_query = SELECT CONCAT(maildir, SUBSTRING_INDEX(username,'@',+1)) as
>> mail, uid, gid, concat('*:storage=', quota) AS quota_rule,
>> concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE
>> username = '%u'
> With this query above I can login again with Roundcube and claws-mail but
> with the mysql-client I get:
>
> mysql> SELECT CONCAT(maildir, SUBSTRING_INDEX(username,'@',+1)) as mail, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u';
> Empty set (0,00 sec)
>
> This is my old query with wich I couldn't log in.
> mysql> SELECT maildir, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u';
> Empty set (0,00 sec)
>
>> The SUBSTRING_INDEX returns everything left of the '@' from field
>> username, that should give you something like
>>
>> +--------------------------------------+------+------+-------------------+--------------------+
>> | mail                                 | uid  | gid  | quota_rule
>> | quota_rule2        |
>> +--------------------------------------+------+------+-------------------+--------------------+
>> | /var/spool/vhosts/nimmini.de/a.meyer | 5000 | 5000 | *:storage=5242880
>> | Trash:storage=+100 |
>> +--------------------------------------+------+------+-------------------+--------------------+
>>
>> which hopefully is the path to your maildir.
> I get am Emty set
>
> Don't understand it.
>
>    Andreas

Hi Andreas,

Make sure to return a "home" attribute, and only optionally a "mail" 
attribute, from your SQL user query.

Your "home" attribute for user "a.meyer at nimmini.de" should return 
"/var/spool/vhosts/nimmini.de/a.meyer", and only if you want to override 
the global mail_location specify a "mail" attribute to return something 
like "maildir:/var/spool/vhosts/nimmini.de/a.meyer/Maildir". There is no 
"maildir" attribute used from the SQL query, to my knowledge.

If you want to have the maildir stored directly in the user's home 
folder, e.g. "/var/spool/vhosts/nimmini.de/a.meyer", and your global 
mail_location already is "maildir:~/", then you only need to return a 
proper "home" attribute from SQL.

BR
Tobias