dovecot - Aug 2017 - migrating 2.1 to 3.x, sql pass scheme, pass value?

On Tue, August 15, 2017 8:54 am, Noel Butler wrote:
> On 15/08/2017 08:18, voytek at 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 <what I do is copy and paste the password from the file
> FIRST, not being lazy, because it verifies and checks the exact password
> you have told dovecot to send>

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 at 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 at 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]>

On Tue, August 15, 2017 9:31 pm, Ivan Warren wrote:
> 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]>
>>
> A bit OT but...
>
>
> It's either identified by 'YourPassword';
> or Identified by password 'YourPasswordHash';
>
>
> https://dev.mysql.com/doc/refman/5.7/en/create-user.html
Ivan, thanks!

syntax fixed, I'll try as root

MariaDB [test]> GRANT SELECT on test to 'test'@'127.0.0.1'
identified by
'test1234';
ERROR 1142 (42000): GRANT command denied to user
'test'@'localhost' for
table 'test'
MariaDB [test]> GRANT SELECT on test to 'test'@'127.0.0.1'
identified by
password'*3D3B92F242033365AE5BC6A8E6FC3E1679F4140A';
ERROR 1142 (42000): GRANT command denied to user
'test'@'localhost' for
table 'test'