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.
Thanks
Tom Sommer