Dovecot. Connect failed to database

[mariuszad]

Witam
Potrzebuję pomocy ze zdiagnozowaniem przyczyny problemu. Postawiłem na domenie lokalnej serwer poczty (postfix+dovecot+mariadb+centos7). Podczas sesji próbnej teletem niby wszystko przechodzi ok ale mail nie dociera do odbiorcy. W logach pojawia się komunikat:

Kod: [Zaznacz]

# telnet poczta.local.tld smtp
Trying 192.168.0.114...
Connected to poczta.local.tld.
Escape character is '^]'.
ehlo poczta.local.tld
250-poczta.local.tld
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-STARTTLS
250-ENHANCEDSTATUSCODES
250-8BITMIME
250 DSN
mail from:<user1@local.tld>
250 2.1.0 Ok
rcpt to:<user2@local.tld>
250 2.1.5 Ok
data
354 End data with <CR><LF>.<CR><LF>
.
250 2.0.0 Ok: queued as CFF06607E3D5
quit
221 2.0.0 Bye
Connection closed by foreign host.

Mail wisi w kolejce

Kod: [Zaznacz]

# mailq
-Queue ID- --Size-- ----Arrival Time---- -Sender/Recipient-------
CFF06607E3D5*     316 Tue Oct 31 09:33:01  user1@local.tld
                                         user2@local.tld

-- 0 Kbytes in 1 Request.

W logach mam błąd autoryzacji w bazie mysql:

Kod: [Zaznacz]

Oct 31 09:31:50 zeus postfix/smtpd[1586]: disconnect from poczta.local.tld[192.168.0.114]
Oct 31 09:32:43 zeus postfix/smtpd[1586]: connect from web2.local.tld[192.168.0.114]
Oct 31 09:33:07 zeus postfix/smtpd[1586]: CFF06607E3D5: client=web2.local.tld[192.168.0.114]
Oct 31 09:33:16 zeus postfix/cleanup[1600]: CFF06607E3D5: message-id=<20231031083307.CFF06607E3D5@poczta.local.tld>
Oct 31 09:33:16 zeus postfix/qmgr[1274]: CFF06607E3D5: from=<user1@local.tld>, size=316, nrcpt=1 (queue active)
Oct 31 09:33:17 zeus dovecot: lmtp(1604): Connect from local
Oct 31 09:33:17 zeus dovecot: auth-worker(1606): Error: mysql(127.0.0.1): Connect failed to database (dbmail): Access denied for user 'dbaccess'@'localhost' (using password: YES) - waiting for 1 seconds before retry
Oct 31 09:33:17 zeus dovecot: auth-worker(1606): Error: mysql(127.0.0.1): Connect failed to database (dbmail): Access denied for user 'dbaccess'@'localhost' (using password: YES) - waiting for 1 seconds before retry
Oct 31 09:33:18 zeus dovecot: auth-worker(1606): Error: mysql(127.0.0.1): Connect failed to database (dbmail): Access denied for user 'dbaccess'@'localhost' (using password: YES) - waiting for 5 seconds before retry
Oct 31 09:33:18 zeus dovecot: auth-worker(1606): Error: mysql(127.0.0.1): Connect failed to database (dbmail): Access denied for user 'dbaccess'@'localhost' (using password: YES) - waiting for 5 seconds before retry
Oct 31 09:33:23 zeus dovecot: auth-worker(1606): Error: mysql(127.0.0.1): Connect failed to database (dbmail): Access denied for user 'dbaccess'@'localhost' (using password: YES) - waiting for 25 seconds before retry
Oct 31 09:33:23 zeus dovecot: auth-worker(1606): Error: mysql(127.0.0.1): Connect failed to database (dbmail): Access denied for user 'dbaccess'@'localhost' (using password: YES) - waiting for 25 seconds before retry
Oct 31 09:33:23 zeus postfix/smtpd[1586]: disconnect from web2.local.tld[192.168.0.114]
Oct 31 09:33:48 zeus dovecot: auth-worker(1606): Error: mysql(127.0.0.1): Connect failed to database (dbmail): Access denied for user 'dbaccess'@'localhost' (using password: YES) - waiting for 125 seconds before retry
Oct 31 09:33:48 zeus dovecot: auth-worker(1606): Error: mysql(127.0.0.1): Connect failed to database (dbmail): Access denied for user 'dbaccess'@'localhost' (using password: YES) - waiting for 125 seconds before retry

Gdy próbuję zalogować się na uzytkownika dbaccess z lini poleceń udaje się to bez problemu:

Kod: [Zaznacz]

# mariadb -u dbaccess -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 360
Server version: 11.1.2-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> \quit
Bye

Moje słabe doświadczenie podpowiada, że to problem z konfiguracją dovecot w sekcji dovecot-sql.conf.ext?

Kod: [Zaznacz]

driver = mysql
connect = host=127.0.0.1 dbname=dbmail user=dbaccess password='my-password'
default_pass_scheme = SHA512-CRYPT
password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';

Jeżeli będzie trzeba to wrzucę konfig dovecot i postfixa czy tabele z bazy dbmail ale na razie nie chcę zagęszczać wiadomości. Będę wdzięczny za każdą pomoc.

Sprawdziłbym:
SHOW GRANTS FOR 'dbaccess'@'%';
SHOW GRANTS FOR 'dbaccess'@'127.0.0.1';
SHOW GRANTS FOR 'dbaccess'@'localhost';
Może mają inne hasła.
Jeśli się nie różnią albo ich nie ma, to w pliku dovecot-sql.conf.ext hasło bez apostrofów.

[Paweł Kraszewski]

Sprawdź, bo o ile pamiętam polecenie mariadb łączy się przez socket Unix, nie przez IP(localhost), a to idzie przez inne ACL-ki.

[mariuszad]

Sprawdziłem :

Kod: [Zaznacz]

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 11.1.2-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> sHOW GRANTS FOR 'dbaccess'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'dbaccess' on host '%'
MariaDB [(none)]> SHOW GRANTS FOR 'dbaccess'@'127.0.0.1';
ERROR 1141 (42000): There is no such grant defined for user 'dbaccess' on host '127.0.0.1'
MariaDB [(none)]> SHOW GRANTS FOR 'dbaccess'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for dbaccess@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `dbaccess`@`localhost` IDENTIFIED BY PASSWORD '*4A93C29414FDB853A547732B1A97757D8D4D6376' |
| GRANT ALL PRIVILEGES ON `dbmail`.* TO `dbaccess`@`localhost`                                                    |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0,000 sec)

Zmieniłem w dovecot-sql.conf.ext na:

Kod: [Zaznacz]

driver = mysql
connect = host=localhost dbname=dbmail user=dbaccess password=my-password
default_pass_scheme = SHA512-CRYPT
password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';

i działa!

Wielkie dzięki!