Postfix + Dovecot Connect failed to database

Question

I have installed a new Mailserver based on Postfix and Dovecot. The setup were fine while I configurated everything. No error outputs or someting.

The problem is: When i try to connect to a mail account over a client, its going to idle at the authentication.

Inside the /var/log/mail.log

dovecot: auth-worker(15467): Error: mysql(localhost): Connect failed to database (mail): Access denied for user 'mail'@'localhost' (using password: YES) - waiting for 1 seconds before retry

So, i would say Dovecot cant connect to the database / wont auth. But i have no idea why. The database user has the right privileges for that database / also tried to flush the privileges. I read that sometimes the auth failed because of the encrypt algo. So i set the mail user password with the OLD_PASSWORD function (found that here). Also wont work.

Here is my driver conf (dovecot-mysql.conf)

driver = mysql
connect = host=localhost dbname=mail user=mail password='mypassword'
default_pass_scheme = PLAIN-MD5
password_query = SELECT password FROM mailbox WHERE username = '%u'
user_query = SELECT CONCAT('maildir:/var/vmail/',maildir) AS mail, 6000 AS uid, 6000 AS gid FROM mailbox WHERE username = '%u'

(mypassword is changed here)

So i dont know why it wont give me the access to the database. Someone any idea?

I would be grateful for any help!

Annotation:
Server: Debian v7.9
Database: MySQL 5.6.25
Dovecot: 2.1.7
Postfix: 2.9.6

postconf -n

alias_database = hash:/etc/aliases
alias_maps = hash:/etc/aliases
append_dot_mydomain = no
biff = no
broken_sasl_auth_clients = yes
config_directory = /etc/postfix
disable_vrfy_command = yes
dovecot_destination_recipient_limit = 1
inet_interfaces = all
local_transport = virtual
mailbox_command = procmail -a "$EXTENSION"
mailbox_size_limit = 0
mydestination = markheumueller.de, localhost
myhostname = h2377359.stratoserver.net
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
myorigin = /etc/mailname
proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $smtpd_sender_login_maps
readme_directory = no
recipient_delimiter = +
relayhost =
smtp_tls_security_level = may
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
smtpd_banner = $myhostname ESMTP $mail_name (Debian/GNU)
smtpd_recipient_restrictions = permit_sasl_authenticated permit_mynetworks reject_unauth_destination
smtpd_sasl_auth_enable = yes
smtpd_sasl_authenticated_header = yes
smtpd_sasl_path = private/auth_dovecot
smtpd_sasl_type = dovecot
smtpd_sender_login_maps = proxy:mysql:/etc/postfix/mysql-sender-login-maps.cf
smtpd_sender_restrictions = reject_authenticated_sender_login_mismatch reject_unknown_sender_domain
smtpd_tls_cert_file = /etc/postfix/ssl/mailserver.crt
smtpd_tls_key_file = /etc/postfix/ssl/mailserver.key
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtpd_use_tls = yes
virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual-alias-maps.cf
virtual_gid_maps = static:6000
virtual_mailbox_base = /var/vmail/
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual-domains-maps.cf
virtual_mailbox_limit = 0
virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
virtual_minimum_uid = 104
virtual_transport = dovecot
virtual_uid_maps = static:6000

Do you have any special characters like a dollar sign in your password? — tarleb

satyr0909 satyr0909 · Accepted Answer · 2017-07-11T13:40:55

The error in the log file is clearly forwarded from Mysql Server. And it states that the user mail cannot connect to the localhost.

You need to check if mysql-server:

1.is really running.
2.really listens on localhost on the standard port(3306), i'd use "netstat -tnla"

Just look if there is really is an entry like:

Proto Recv-Q Send-Q Local Address           Foreign Address        State      
tcp        0      0 127.0.0.1:3306            0.0.0.0:*            LISTEN

If there is such a line then try changing your settings to:

driver = mysql
connect = host=127.0.0.1 dbname=mail user=mail password='mypassword'

Sometimes it helps.

If the "netstat" commando deliver no line containing 127.0.0.1:3306,

make sure that mysql server really listen on tcp socket as well as on unix socket, on Debian and co. look into /etc/my.cnf file.

If it runs and listens OK, but there is still no authentication through dovecot/postfix using this set by you mysql account and database, then try connecting with the same credentials over the commandline in the terminal window:

mysql -u mail -h localhost mail -p

You should succeed in such console login.

Should you receive a similar error as in your log file, doing this login attempt, then you didn't correctly set user access parameters in mysql. Just use the correct GRANT SQL Sentence for this, in the similar way as here by me:

~$ mysql -u root -p 
Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 505705
Server version: 5.5.49-0+deb8u1 (Debian)

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

mysql> GRANT ALL PRIVILEGES ON mail.* TO 'mail'@'localhost' IDENTIFIED BY 'your.password.here';

mysql> GRANT ALL PRIVILEGES ON mail.* TO 'mail'@'127.0.0.1' IDENTIFIED BY 'your.password.here';

Using GRANT sentences in mysql you don't need to FLASH PRIVILEGES.

You can restrict your user privileges and instead of GRANT ALL PRIVILEGES part, just use: GRANT usage on mail.* TO 'mail'@'127.0.0.1' IDENTIFIED BY 'your.password.here'; GRANT SELECT on mail.* TO 'mail'@'127.0.0.1' IDENTIFIED BY 'your.password.here';

Hope this helps.

Postfix + Dovecot Connect failed to database

2 Answers