Tom Sommer wrote:
Timo Sirainen wrote:
On Tue, 2009-01-20 at 09:53 +0100, Tom Sommer wrote:
sql(user@example.com,127.0.0.1): query: SELECT username as user, plainpassword as password, nopassword FROM cyrususers WHERE username = 'user@example.com' AND password = PASSWORD('SECRET') AND active = 1 dovecot: Jan 20 09:01:18 Info: auth-worker(default): sql(user@example.com,127.0.0.1): unknown user
..
It appears the user missed the cache, a SQL lookup is performed (which returns 1 record, I tested the query directly) - however for some reason the lookup is set as Unknown User, a state which it then keeps.
It's most likely set to unknown user because the password=PASSWORD() check fails and no rows are returned. If you're already returning plainpassword for Dovecot, why do you do the password check also in the SQL query? That doesn't allow Dovecot to differentiate between unknown user and invalid password.
No I ran the query manually afterwards and it returned 1 row. The reason I'm using plainpassword, PASSWORD() and nopassword, etc. is because not all users have a plainpassword - yet - as time progress more and more users will return plainpassword and nopassword=NULL
That's how you fix design flaws without forcing all users to change passwords :)
auth_cache_negative_ttl seems like a good source for user flaws (login attempt before account is created = you cant log in for 3600 seconds even after the account is valid), gonna go with 0 on all servers. I'm sorry, but I still have problems with this. I got cache_size 1024, cache_ttl 3600, cache_negative_ttl 0, but if a user changes password in my SQL, sometimes it requires a restart of dovecot for him to be able to log in. Dovecot 1.1.14.
Using plain passwords, no "nopassword" feature, and MySQL for passdb.
Basically my log is: --- Dovecot starts
- auth failed, 1 attempts
- auth failed, 1 attempts
- auth failed, 1 attempts
- auth failed, 1 attempts
- auth failed, 1 attempts
- auth failed, 1 attempts
- auth failed, 1 attempts --- I RESTART DOVECOT ---
- Login successful (same password, same everything)
The cache seems to be faulty somehow, I wish there was a way to dump the contents of the cache to debug this, because somehow I cannot forcefully reproduce it. Notice in this case the user never before logged in successfully, so I dont understand why he would even be in the cache, unless there is something wrong with cache_negative_ttl.
Thanks // Tom