migrating 2.1 to 3.x, sql pass scheme, pass value?
I'm trying to migrate from Centos 6/dovecot 2.1.17/mysql to new server on Centos 7/dovecot 2.2.31 (65cde28)/mariadb
I've dumped on oldserver 'dovecot.conf' and used it on newserver doveconf -n > dovecot.conf also, transferred /etc/doveconf/*.conf
I've also dumped MySQL 'vmail' and imported database, created user vmail, vmailadmin
dumb question: should the password hash from oldserver work 'as is' on newserver..? (different machine, different SQL..)
I've tried 'as is', then, attepmted to change both "default_pass_scheme" and 'password=' values, but, no luck
postfixadmin works, I can login and see domains/mailboxes
what default_pass_scheme should I use ? where/how do I take password= value ?
when I attempt to login to pop, I see this:
# doveadm stop # dovecot # telnet 127.0.0.1 110 Trying 127.0.0.1... Connected to 127.0.0.1. Escape character is '^]'. +OK Dovecot ready. USER voytek@sbt.net.au +OK PASS mypass -ERR [SYS/TEMP] Temporary authentication failure. [voytek-web01:2017-08-14 22:06:33] -ERR Unknown command. -ERR Disconnected for inactivity. Connection closed by foreign host.
log has:
Aug 15 08:04:33 master: Warning: Killed with signal 15 (by pid=9743 uid=0 code=kill) Aug 15 08:04:44 master: Info: Dovecot v2.2.31 (65cde28) starting up for pop3, imap, sieve (core dumps disabled) Aug 15 08:05:31 auth-worker(9763): Error: mysql(127.0.0.1): Connect failed to database (vmail): Access denied for user 'vmail'@'localhost' (using password: YES) - waiting for 1 seconds before retry Aug 15 08:05:31 auth-worker(9763): Error: mysql(127.0.0.1): Connect failed to database (vmail): Access denied for user 'vmail'@'localhost' (using password: YES) - waiting for 1 seconds before retry //6 more repeats// Aug 15 08:06:31 auth: Error: auth worker: Aborted PASSV request for voytek@sbt.net.au: Lookup timed out Aug 15 08:06:31 auth-worker(9763): Error: sql(voytek@sbt.net.au,127.0.0.1,<9/h93L1WqLt/AAAB>): Password query failed: Not connected to database Aug 15 08:06:31 auth-worker(9763): Warning: Auth master disconnected us while handling request for voytek@sbt.net.au for 60 secs (result=FAIL) Aug 15 08:06:31 auth-worker(9780): Error: mysql(127.0.0.1): Connect failed to database (vmail): Access denied for user 'vmail'@'localhost' (using password: YES) - waiting for 1 seconds before retry Aug 15 08:06:32 auth-worker(9780): Error: mysql(127.0.0.1): Connect failed to database (vmail): Access denied for user 'vmail'@'localhost' (using password: YES) - waiting for 5 seconds before retry //2 more repeats//
oldserver & newserver
# cat dovecot-mysql.conf
driver = mysql
default_pass_scheme = CRYPT
connect = host=127.0.0.1 dbname=vmail user=vmail
password=etXwz...trimmmed...........
# Required by 'doveadm mailbox ...'.
iterate_query = SELECT username AS user FROM mailbox
password_query = SELECT password FROM mailbox WHERE username='%u' AND
active='1'
user_query = SELECT
'%u' AS master_user,
CONCAT(mailbox.storagebasedirectory, '/', mailbox.storagenode, '/',
mailbox.maildir) AS home,
CONCAT('*:bytes=', mailbox.quota*1048576) AS quota_rule
FROM mailbox,domain
WHERE mailbox.username='%u'
AND mailbox.domain='%d'
AND mailbox.enable%Ls%Lc=1
AND mailbox.domain=domain.domain
AND domain.backupmx=0
AND domain.active=1
AND mailbox.active=1
#
newserver # cat dovecot.conf
# 2.1.17: /etc/dovecot/dovecot.conf # OS: Linux 2.6.32-642.15.1.el6.x86_64 x86_64 CentOS release 6.8 (Final) auth_master_user_separator = * auth_mechanisms = PLAIN LOGIN dict { acl = mysql:/etc/dovecot/dovecot-share-folder.conf quotadict = mysql:/etc/dovecot/dovecot-used-quota.conf } first_valid_uid = 2000 last_valid_uid = 2000 listen = * log_path = /var/log/dovecot.log mail_gid = 2000 mail_location = maildir:/%Lh/Maildir/:INDEX=/%Lh/Maildir/ mail_plugins = quota mail_log notify mail_uid = 2000 managesieve_notify_capability = mailto managesieve_sieve_capability = fileinto reject envelope encoded-character vacation subaddress comparator-i;ascii-numeric relational regex imap4flags copy include variables body enotify environment mailbox date ihave namespace { inbox = yes location = prefix = separator = / type = private } namespace { list = children location = maildir:/%%Lh/Maildir/:INDEX=/%%Lh/Maildir/Shared/%%u prefix = Shared/%%u/ separator = / subscriptions = yes type = shared } passdb { args = /etc/dovecot/dovecot-mysql.conf driver = sql } passdb { args = /etc/dovecot/dovecot-master-users-password driver = passwd-file master = yes } plugin { acl = vfile acl_shared_dict = proxy::acl auth_socket_path = /var/run/dovecot/auth-master autocreate = INBOX autocreate2 = Sent autocreate3 = Trash autocreate4 = Drafts autocreate5 = Junk autosubscribe = INBOX autosubscribe2 = Sent autosubscribe3 = Trash autosubscribe4 = Drafts autosubscribe5 = Junk mail_log_events = delete undelete expunge copy mailbox_delete mailbox_rename mail_log_fields = uid box msgid size quota = dict:user::proxy::quotadict quota_rule = *:storage=1G quota_warning = storage=85%% quota-warning 85 %u quota_warning2 = storage=90%% quota-warning 90 %u quota_warning3 = storage=95%% quota-warning 95 %u sieve = /%Lh/sieve/dovecot.sieve sieve_dir = /%Lh/sieve sieve_global_dir = /var/vmail/sieve sieve_global_path = /var/vmail/sieve/dovecot.sieve } protocols = pop3 imap sieve service auth { unix_listener /var/spool/postfix/dovecot-auth { group = postfix mode = 0666 user = postfix } unix_listener auth-master { group = vmail mode = 0666 user = vmail } unix_listener auth-userdb { group = vmail mode = 0660 user = vmail } } service dict { unix_listener dict { group = vmail mode = 0660 user = vmail } } service imap-login { process_limit = 500 service_count = 1 } service pop3-login { service_count = 1 } service quota-warning { executable = script /usr/local/bin/dovecot-quota-warning.sh unix_listener quota-warning { group = vmail mode = 0660 user = vmail } } ssl = required ssl_cert =
protocol lda { auth_socket_path = /var/run/dovecot/auth-master lda_mailbox_autocreate = yes log_path = /var/log/sieve.log mail_plugins = quota sieve autocreate postmaster_address = root } protocol imap { imap_client_workarounds = tb-extra-mailbox-sep mail_max_userip_connections = 60 mail_plugins = quota imap_quota autocreate } protocol pop3 { mail_plugins = quota pop3_client_workarounds = outlook-no-nuls oe-ns-eoh pop3_uidl_format = %08Xu%08Xv }
On 15/08/2017 08:18, voytek@sbt.net.au wrote:
I've also dumped MySQL 'vmail' and imported database, created user vmail, vmailadmin
Aug 15 08:05:31 auth-worker(9763): Error: mysql(127.0.0.1): Connect failed to database (vmail): Access denied for user 'vmail'@'localhost' (using password: YES) - waiting for 1 seconds before retry
Forget looking at dovecot at teh moment, your problem maybe mysql (I'd hope you meant mariadb but either way...)
use command line mysql as vmail user from your dovecot machine to test password further BEFORE tinkering with dovecot.
~$ mysql -p -v vmail
enter password
If it's all good you'll see :
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 20970 Server version: 5.5.57-MariaDB Source distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
....
If not, look into mysql db and verifiy vmail user and perms (especially for localhost), ensure you have reloaded privs as well.
Set mysql debugging on if need be.
-- Kind Regards,
Noel Butler
This Email, including any attachments, may contain legally privilegedinformation, therefore remains confidential and subject to copyright protected under international law. You may not disseminate, discuss, or reveal, any part, to anyone, without the authors express written authority to do so. If you are not the intended recipient, please notify the sender then delete all copies of this message including attachments, immediately. Confidentiality, copyright, and legal privilege are not waived or lost by reason of the mistaken delivery of this message. Only PDF [1] and ODF [2] documents accepted, please do not send proprietary formatted documents
Links:
[1] http://www.adobe.com/ [2] http://en.wikipedia.org/wiki/OpenDocument
hit enter too quickly (I've had one coffee all morning hehe)
On 15/08/2017 08:54, Noel Butler wrote:
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
At this point issue > show databases;
the out put should include vmail
-- Kind Regards,
Noel Butler
This Email, including any attachments, may contain legally privilegedinformation, therefore remains confidential and subject to copyright protected under international law. You may not disseminate, discuss, or reveal, any part, to anyone, without the authors express written authority to do so. If you are not the intended recipient, please notify the sender then delete all copies of this message including attachments, immediately. Confidentiality, copyright, and legal privilege are not waived or lost by reason of the mistaken delivery of this message. Only PDF [1] and ODF [2] documents accepted, please do not send proprietary formatted documents
Links:
[1] http://www.adobe.com/ [2] http://en.wikipedia.org/wiki/OpenDocument
On Tue, August 15, 2017 8:54 am, Noel Butler wrote:
On 15/08/2017 08:18, voytek@sbt.net.au wrote:
Forget looking at dovecot at teh moment, your problem maybe mysql (I'd hope you meant mariadb but either way...)
use command line mysql as vmail user from your dovecot machine to test password further BEFORE tinkering with dovecot.
~$ mysql -p -v vmail
Noel, thanks
MySQL on old, MariaDB on new
OK, KEYING in password as I have:
(you meant -u, no?)
]# mysql -p -u vmail Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1245 Server version: 10.1.19-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | postfix | | vmail | +--------------------+ 3 rows in set (0.00 sec)
MariaDB [(none)]>
I've used the password i've made, NOT the string I have in /etc/dovecot/mysql file
enter password
BUT, when I use OLDSERVER hashed string
on OLD server, it's OK
on NEW server, fail:
# mysql -p -u vmail Enter password: ERROR 1045 (28000): Access denied for user 'vmail'@'localhost' (using password: YES)
so, the hashed string I've copied /transfered from OLD server 'as is' doesn't work on NEW
how do I generate hashed string from my password ?
On 15 Aug 2017, at 2.50, voytek@sbt.net.au wrote:
how do I generate hashed string from my password ?
use this sql command:
GRANT SELECT ON vmail TO ‘vmail’@‘127.0.0.1’ IDENTIFIED BY PASSWORD(‘yourpassword’);
or if you just want to see the hash:
SELECT PASSWORD(‘yourpassword’);
Sami
On Tue, August 15, 2017 8:03 pm, Sami Ketola wrote:
On 15 Aug 2017, at 2.50, voytek@sbt.net.au wrote:
how do I generate hashed string from my password ?
use this sql command:
GRANT SELECT ON vmail TO ‘vmail’@‘127.0.0.1’ IDENTIFIED BY PASSWORD(‘yourpassword’);
or if you just want to see the hash:
SELECT PASSWORD(‘yourpassword’);
Sami, thanks
I'm running in circles here.. I thought it worked once, but, couldn't repeat it after
OK, I've made user 'test' with pw 'test1234'
using keyborad to enter test1234 I get:
# mysql -u test -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1377 Server version: 10.1.19-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec)
MariaDB [(none)]>
NOW:
MariaDB [(none)]> SELECT PASSWORD('test1234'); +-------------------------------------------+ | PASSWORD('test1234') | +-------------------------------------------+ | *3D3B92F242033365AE5BC6A8E6FC3E1679F4140A | +-------------------------------------------+ 1 row in set (0.00 sec)
MariaDB [(none)]> quit
copied '*3D3B92F242033365AE5BC6A8E6FC3E1679F4140A' to buffer
paste from buffer below, fail
# mysql -u test -p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
HMMM, maybe Putty copy/paste is screwed up...? ...? tried shift/insert, still fails ?
the other one gives syntax error:
MariaDB [(none)]> GRANT SELECT on test to 'test'@'127.0.0.1' identified by pasword('test1234'); ERROR 1046 (3D000): No database selected MariaDB [(none)]> use test; Database changed MariaDB [test]> GRANT SELECT on test to 'test'@'127.0.0.1' identified by pasword('test1234'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'pasword('test1234')' at line 1 MariaDB [test]>
the other one gives syntax error:
MariaDB [(none)]> GRANT SELECT on test to 'test'@'127.0.0.1' identified by pasword('test1234'); ERROR 1046 (3D000): No database selected MariaDB [(none)]> use test; Database changed MariaDB [test]> GRANT SELECT on test to 'test'@'127.0.0.1' identified by pasword('test1234'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'pasword('test1234')' at line 1 MariaDB [test]>
OOPS, type, single 's' in password, but, still error:
MariaDB [(none)]> GRANT SELECT on test to 'test'@'127.0.0.1' identified by password('test1234'); ERROR 1046 (3D000): No database selected MariaDB [(none)]> use test; Database changed MariaDB [test]> GRANT SELECT on test to 'test'@'127.0.0.1' identified by password('test1234'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '('test1234')' at line 1 MariaDB [test]>
On 15/08/2017 21:25, voytek@sbt.net.au wrote:
On Tue, August 15, 2017 8:03 pm, Sami Ketola wrote: On 15 Aug 2017, at 2.50, voytek@sbt.net.au wrote:
how do I generate hashed string from my password ? use this sql command:
GRANT SELECT ON vmail TO 'vmail'@'127.0.0.1' IDENTIFIED BY PASSWORD('yourpassword');
or if you just want to see the hash:
SELECT PASSWORD('yourpassword');
Sami, thanks
I'm running in circles here.. I thought it worked once, but, couldn't repeat it after
OK, I've made user 'test' with pw 'test1234'
using keyborad to enter test1234 I get:
# mysql -u test -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1377 Server version: 10.1.19-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec)
MariaDB [(none)]>
NOW:
MariaDB [(none)]> SELECT PASSWORD('test1234'); +-------------------------------------------+ | PASSWORD('test1234') | +-------------------------------------------+ | *3D3B92F242033365AE5BC6A8E6FC3E1679F4140A | +-------------------------------------------+ 1 row in set (0.00 sec)
MariaDB [(none)]> quit
copied '*3D3B92F242033365AE5BC6A8E6FC3E1679F4140A' to buffer
paste from buffer below, fail
# mysql -u test -p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
HUH?
Are you trying to login to mysql using the hash itself?
That wont work, and its not what you are supposed to be doing as evident by fact you can login using plain password, you're looking in the wrong area, since the database stores passwords hashed, you enter it in, in plain text, the database them does its magic to convert what you entered in, into a hash and does the matching in its own backend, so to speak.
if you put in your dovecot sql file, the vmail password in plain text and not hashed output, it should work, you have to make sure the sql file is chmod 600 so any normal users with access cant read the file(s).
-- Kind Regards,
Noel Butler
This Email, including any attachments, may contain legally privilegedinformation, therefore remains confidential and subject to copyright protected under international law. You may not disseminate, discuss, or reveal, any part, to anyone, without the authors express written authority to do so. If you are not the intended recipient, please notify the sender then delete all copies of this message including attachments, immediately. Confidentiality, copyright, and legal privilege are not waived or lost by reason of the mistaken delivery of this message. Only PDF [1] and ODF [2] documents accepted, please do not send proprietary formatted documents
Links:
[1] http://www.adobe.com/ [2] http://en.wikipedia.org/wiki/OpenDocument
On 15/08/2017 22:23, Noel Butler wrote:
On 15/08/2017 21:25, voytek@sbt.net.au wrote:
On Tue, August 15, 2017 8:03 pm, Sami Ketola wrote: On 15 Aug 2017, at 2.50, voytek@sbt.net.au wrote:
how do I generate hashed string from my password ? use this sql command:
GRANT SELECT ON vmail TO 'vmail'@'127.0.0.1' IDENTIFIED BY PASSWORD('yourpassword');
or if you just want to see the hash:
SELECT PASSWORD('yourpassword');
Sami, thanks
I'm running in circles here.. I thought it worked once, but, couldn't repeat it after
OK, I've made user 'test' with pw 'test1234'
using keyborad to enter test1234 I get:
# mysql -u test -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1377 Server version: 10.1.19-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec)
MariaDB [(none)]>
NOW:
MariaDB [(none)]> SELECT PASSWORD('test1234'); +-------------------------------------------+ | PASSWORD('test1234') | +-------------------------------------------+ | *3D3B92F242033365AE5BC6A8E6FC3E1679F4140A | +-------------------------------------------+ 1 row in set (0.00 sec)
MariaDB [(none)]> quit
copied '*3D3B92F242033365AE5BC6A8E6FC3E1679F4140A' to buffer
paste from buffer below, fail
# mysql -u test -p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
HUH?
Are you trying to login to mysql using the hash itself?
That wont work, and its not what you are supposed to be doing as evident by fact you can login using plain password, you're looking in the wrong area, since the database stores passwords hashed, you enter it in, in plain text, the database them does its magic to convert what you entered in, into a hash and does the matching in its own backend, so to speak.
if you put in your dovecot sql file, the vmail password in plain text and not hashed output, it should work, you have to make sure the sql file is chmod 600 so any normal users with access cant read the file(s).
-- Kind Regards,
Noel Butler
This Email, including any attachments, may contain legally privileged information, therefore remains confidential and subject to copyright protected under international law. You may not disseminate, discuss, or reveal, any part, to anyone, without the authors express written authority to do so. If you are not the intended recipient, please notify the sender then delete all copies of this message including attachments, immediately. Confidentiality, copyright, and legal privilege are not waived or lost by reason of the mistaken delivery of this message. Only PDF [1 [1]] and ODF [2 [2]] documents accepted, please do not send proprietary formatted documents
Links:
[1] http://www.adobe.com/ [2] http://en.wikipedia.org/wiki/OpenDocument
OK dunno wjhat happend with format but to simplify it:
HUH?
Are you trying to login to mysql using the hash itself?
That wont work, and its not what you are supposed to be doing as evident by fact you can login using plain password, you're looking in the wrong area, since the database stores passwords hashed, you enter it in, in plain text, the database them does its magic to convert what you entered in, into a hash and does the matching in its own backend, so to speak.
if you put in your dovecot sql file, the vmail password in plain text and not hashed output, it should work, you have to make sure the sql file is chmod 600 so any normal users with access cant read the file(s).
-- Kind Regards,
Noel Butler
This Email, including any attachments, may contain legally privilegedinformation, therefore remains confidential and subject to copyright protected under international law. You may not disseminate, discuss, or reveal, any part, to anyone, without the authors express written authority to do so. If you are not the intended recipient, please notify the sender then delete all copies of this message including attachments, immediately. Confidentiality, copyright, and legal privilege are not waived or lost by reason of the mistaken delivery of this message. Only PDF [1] and ODF [2] documents accepted, please do not send proprietary formatted documents
Links:
[1] http://www.adobe.com/ [2] http://en.wikipedia.org/wiki/OpenDocument
On Tue, August 15, 2017 10:27 pm, Noel Butler wrote:
HUH? Are you trying to login to mysql using the hash itself?
Noel, thanks!!
oops, misunderstood instruction...
this is better:
USER voytek@xxxxx.tld +OK PASS ********** +OK Logged in. LIST +OK 0 messages:
That wont work, and its not what you are supposed to be doing as evident by fact you can login using plain password, you're looking in the wrong area, since the database stores passwords hashed, you enter it in, in plain text, the database them does its magic to convert what you entered in, into a hash and does the matching in its own backend, so to speak.
what value should I have in /etc/dovecot/dovecot-mysql.conf in default_pass_scheme = ???
V
On 15/08/2017 22:58, voytek@sbt.net.au wrote:
On Tue, August 15, 2017 10:27 pm, Noel Butler wrote:
HUH? Are you trying to login to mysql using the hash itself?
Noel, thanks!!
oops, misunderstood instruction...
this is better:
USER voytek@xxxxx.tld +OK PASS ********** +OK Logged in. LIST +OK 0 messages:
That wont work, and its not what you are supposed to be doing as evident by fact you can login using plain password, you're looking in the wrong area, since the database stores passwords hashed, you enter it in, in plain text, the database them does its magic to convert what you entered in, into a hash and does the matching in its own backend, so to speak.
what value should I have in /etc/dovecot/dovecot-mysql.conf in default_pass_scheme = ???
V
Use: CRYPT
This allows you to use whatever your system supports in your database password fields, with modern OS's thats anything from md5 (shudder the thought) to salted sha512 and probably more these days depending on what other goodies your distro adds, dovecot will send it to the underlying OS crypt function that does all the hard work.
-- Kind Regards,
Noel Butler
This Email, including any attachments, may contain legally privilegedinformation, therefore remains confidential and subject to copyright protected under international law. You may not disseminate, discuss, or reveal, any part, to anyone, without the authors express written authority to do so. If you are not the intended recipient, please notify the sender then delete all copies of this message including attachments, immediately. Confidentiality, copyright, and legal privilege are not waived or lost by reason of the mistaken delivery of this message. Only PDF [1] and ODF [2] documents accepted, please do not send proprietary formatted documents
Links:
[1] http://www.adobe.com/ [2] http://en.wikipedia.org/wiki/OpenDocument
participants (3)
-
Noel Butler
-
Sami Ketola
-
voytek＠sbt.net.au