[sisyphus] dovecot & mysql

[Oleg Sukhanov <bh@kaznaufk.amur.ru>]

[-- Attachment #1: Type: text/plain, Size: 119 bytes --]


Никак не удаётся забрать из базы пароль юзера.

Пытался по всякому - никак.

Как это лечить? Юзер в базе точно есть.


[-- Attachment #2: dovecot --]
[-- Type: text/plain, Size: 3959 bytes --]


black@cdmz ~ $ telnet localhost 110
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
+OK Dovecot ready.
user test@cdmz.ufk23.fsfk.local
+OK
pass PAwz3tYR
-ERR [IN-USE] Internal login failure. Refer to server log for more information.
Connection closed by foreign host.



Apr 24 14:34:35 cdmz dovecot: auth(default): client in: AUTH^I1^IPLAIN^Iservice=POP3^Isecured^Ilip=127.0.0.1^Irip=127.0.0.1^Iresp=AHRlc3RAY2Rtei51ZmsyMy5mc2ZrLmxvY2FsAFBBd3ozdFlS
Apr 24 14:34:35 cdmz dovecot: auth-worker(default): sql(test@cdmz.ufk23.fsfk.local,127.0.0.1): query: SELECT password FROM users WHERE login = 'test@cdmz.ufk23.fsfk.local' AND domain = 'cdmz.ufk23.fsfk.local' AND status = '1'
Apr 24 14:34:35 cdmz dovecot: auth(default): client out: OK^I1^Iuser=test@cdmz.ufk23.fsfk.local
Apr 24 14:34:35 cdmz dovecot: auth(default): master in: REQUEST^I1^I11188^I1
Apr 24 14:34:35 cdmz dovecot: auth(default): passwd(test@cdmz.ufk23.fsfk.local,127.0.0.1): unknown user
Apr 24 14:34:35 cdmz dovecot: auth(default): userdb(test@cdmz.ufk23.fsfk.local,127.0.0.1): user not found from userdb
Apr 24 14:34:35 cdmz dovecot: auth(default): master out: NOTFOUND^I1
Apr 24 14:34:35 cdmz dovecot: pop3-login: Internal login failure: user=<test@cdmz.ufk23.fsfk.local>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured


mysql> SELECT password FROM users WHERE login = 'test@cdmz.ufk23.fsfk.local' AND domain='cdmz.ufk23.fsfk.local' AND status = '1';
+----------+
| password |
+----------+
| PAwz3tYR |
+----------+
1 row in set (0.00 sec)


Apr 24 14:56:59 cdmz dovecot: auth(default): client in: AUTH^I1^IPLAIN^Iservice=POP3^Isecured^Ilip=127.0.0.1^Irip=127.0.0.1^Iresp=AHRlc3RAY2Rtei51ZmsyMy5mc2ZrLmxvY2FsAFBBd3ozdFlS
Apr 24 14:56:59 cdmz dovecot: auth-worker(default): sql(test@cdmz.ufk23.fsfk.local,127.0.0.1): query: SELECT password FROM users WHERE login = 'test@cdmz.ufk23.fsfk.local' AND domain = 'cdmz.ufk23.fsfk.local' AND status = '1'
Apr 24 14:56:59 cdmz dovecot: auth-worker(default): sql(test@cdmz.ufk23.fsfk.local,127.0.0.1): unknown user
Apr 24 14:57:00 cdmz dovecot: auth(default): client out: FAIL^I1^Iuser=test@cdmz.ufk23.fsfk.local
Apr 24 14:57:07 cdmz dovecot: auth(default): client in: AUTH^I2^IPLAIN^Iservice=POP3^Isecured^Ilip=127.0.0.1^Irip=127.0.0.1^Iresp=AHRlc3QAUEF3ejN0WVI=
Apr 24 14:57:07 cdmz dovecot: auth-worker(default): sql(test,127.0.0.1): query: SELECT password FROM users WHERE login = 'test@' AND domain = '' AND status = '1'
Apr 24 14:57:07 cdmz dovecot: auth-worker(default): sql(test,127.0.0.1): unknown user
Apr 24 14:57:08 cdmz dovecot: auth(default): client out: FAIL^I2^Iuser=test
Apr 24 14:57:12 cdmz dovecot: pop3-login: Disconnected: user=<test>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured


black@cdmz ~ $ telnet localhost 110
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
+OK Dovecot ready.
user test@cdmz.ufk23.fsfk.local
+OK
pass PAwz3tYR
-ERR Authentication failed.
user test
+OK
pass PAwz3tYR
-ERR Authentication failed.
^]
telnet> quit
Connection closed.


mysql> SELECT password FROM users WHERE login = 'test' AND domain='cdmz.ufk23.fsfk.local' AND status = '1';
+----------+
| password |
+----------+
| PAwz3tYR |
+----------+
1 row in set (0.00 sec)


dovecot.conf:
  # SQL database
  passdb sql {
      # Path for SQL configuration file, see doc/dovecot-sql.conf for example
      args = /etc/dovecot/dovecot-mysql.conf
  }

  userdb sql {
      # Path for SQL configuration file, see doc/dovecot-sql.conf for example
      args = /etc/dovecot/dovecot-mysql.conf
  }



dovecot-mysql.conf:
driver = mysql

connect = host=127.0.0.1 dbname=exim user=sqlmail password=XXXXXXXXX
password_query = SELECT password FROM users WHERE login = '%n@%d' AND domain = '%d' AND status = '1'
ðÒÏÂÏ×ÁÌ É ÔÁË:
password_query = SELECT password FROM users WHERE login = '%n' AND domain = '%d' AND status = '1'
user_query = SELECT uid, gid FROM users WHERE login = '%n' AND domain = '%d'