posftix, dovecot and mysql - auth failed after updating MySql
by ddaas from LinuxQuestions.org on (#5A8W8)
Hello,
I'm running Ubuntu 20.04 with Posftix, Dovecot and Mysql (latest versions).
The config worked, but with the latest version of MySQL the ENCRYPT function was deprecated.
Now I have in /etc/dovecot/dovecot-sql.conf:
Code:driver = mysql
connect = host=127.0.0.1 dbname=mail user=mail_admin password=mail_admin_password
default_pass_scheme = SHA512-CRYPT
password_query = SELECT email as user, password FROM users WHERE email='%u';I'm inserting domains and users like this:
Code:msyql> USE mail;
msyql>INSERT INTO domains (domain) VALUES ('mydomain.com');
mysql>INSERT INTO users (email,password) values('u1@mydomain.com',sha2('mypassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))));
mysql>quit;The user table was created like this:
Code: mysql> CREATE TABLE users (email varchar(80) NOT NULL, password varchar(256) NOT NULL, PRIMARY KEY (email) );
msyql> CREATE TABLE domains (domain varchar(50) NOT NULL, PRIMARY KEY (domain) );The only error I'm getting in the logs is:
dovecot: imap-login: Disconnected (auth failed, 2 attempts in 8 secs): user=<u1@mydomain.com>, method=PLAIN, rip=5.12.x.y, lip=206.81.x.y, TLS, session=<uBerVtuzNOkFDENS>
Any idea? What could I do?
I'm sure there is something related to the way I'm inserting the password in the db and how dovecot is reading it.
I've spent the entire day with this :((
I'm running Ubuntu 20.04 with Posftix, Dovecot and Mysql (latest versions).
The config worked, but with the latest version of MySQL the ENCRYPT function was deprecated.
Now I have in /etc/dovecot/dovecot-sql.conf:
Code:driver = mysql
connect = host=127.0.0.1 dbname=mail user=mail_admin password=mail_admin_password
default_pass_scheme = SHA512-CRYPT
password_query = SELECT email as user, password FROM users WHERE email='%u';I'm inserting domains and users like this:
Code:msyql> USE mail;
msyql>INSERT INTO domains (domain) VALUES ('mydomain.com');
mysql>INSERT INTO users (email,password) values('u1@mydomain.com',sha2('mypassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))));
mysql>quit;The user table was created like this:
Code: mysql> CREATE TABLE users (email varchar(80) NOT NULL, password varchar(256) NOT NULL, PRIMARY KEY (email) );
msyql> CREATE TABLE domains (domain varchar(50) NOT NULL, PRIMARY KEY (domain) );The only error I'm getting in the logs is:
dovecot: imap-login: Disconnected (auth failed, 2 attempts in 8 secs): user=<u1@mydomain.com>, method=PLAIN, rip=5.12.x.y, lip=206.81.x.y, TLS, session=<uBerVtuzNOkFDENS>
Any idea? What could I do?
I'm sure there is something related to the way I'm inserting the password in the db and how dovecot is reading it.
I've spent the entire day with this :((