Hello,
I hope that this mailing list is "alive", since I am looking for a
solution for my problem for a long time.
I would like to migrate my existing dovecot installation from mysql to
pgsql. But I have problems with the passwords when using pgsql.
The existing and working mysql-based installation looks like this:
dovecot-sql.conf.ext:
driver = mysql
default_pass_scheme = SHA512-CRYPT
Users are created like this:
INSERT INTO mls_user (idx,domain,password,email)
VALUES (1,99,ENCRYPT('Test'),'mws at alpenjodel.de');
This setup is working, which I can verify like this:
$ telnet localhost 143
* OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID
ENABLE IDLE AUTH=PLAIN AUTH=LOGIN AUTH=DIGEST-MD5
AUTH=CRAM-MD5] Dovecot ready.
a login mws at alpenjodel.de Test
OK
Now let's take a look at the pgsql version of the setup:
dovecot-sql.conf.ext:
driver = pgsql
default_pass_scheme = SHA512-CRYPT
Users are created like this:
INSERT INTO mls_user (idx,domain,password,email)
VALUES (1,99,crypt('Test',gen_salt('des')),'mws at
alpenjodel.de');
This setup is not working:
$ telnet localhost 143
* OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID
ENABLE IDLE AUTH=PLAIN AUTH=LOGIN AUTH=DIGEST-MD5
AUTH=CRAM-MD5] Dovecot ready.
a login mws at alpenjodel.de Test
a NO [AUTHENTICATIONFAILED] Authentication failed.
Assumptions:
- I believe that the mysql encrypt function uses the crypt system call,
which in turn uses the DES algorithm with a random salt.
- I believe that the same is done with the pgsql function call
crypt('Test',gen_salt('des')).
But obviously some of these assumptions must be wrong.
Besides that, the variable "default_pass_scheme" is set to
"SHA512-CRYPT" in both cases. But obviously, not SHA but DES is used
by
the working mysql-based setup. I don't understand that. Could someone
please explain the relationship between the default_pass_scheme variable
and the encryption/hashing algorithm used to store the user passwords?
And finally: What can I do to migrate to pgsql?
Thank you
Magnus
On 05/10/2017 02:06, Magnus wrote:> Hello, > > I hope that this mailing list is "alive", since I am looking for a solution for my problem for a long time. > > I would like to migrate my existing dovecot installation from mysql to pgsql. But I have problems with the passwords when using pgsql. > > The existing and working mysql-based installation looks like this: > > dovecot-sql.conf.ext: > > driver = mysql > default_pass_scheme = SHA512-CRYPT > > Users are created like this: > > INSERT INTO mls_user (idx,domain,password,email) > VALUES (1,99,ENCRYPT('Test'),'mws at alpenjodel.de'); > > This setup is working, which I can verify like this: > > $ telnet localhost 143 > * OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID > ENABLE IDLE AUTH=PLAIN AUTH=LOGIN AUTH=DIGEST-MD5 > AUTH=CRAM-MD5] Dovecot ready. > > a login mws at alpenjodel.de Test > OK > > Now let's take a look at the pgsql version of the setup: > > dovecot-sql.conf.ext: > > driver = pgsql > default_pass_scheme = SHA512-CRYPT > > Users are created like this: > > INSERT INTO mls_user (idx,domain,password,email) > VALUES (1,99,crypt('Test',gen_salt('des')),'mws at alpenjodel.de'); > > This setup is not working: > > $ telnet localhost 143 > * OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID > ENABLE IDLE AUTH=PLAIN AUTH=LOGIN AUTH=DIGEST-MD5 > AUTH=CRAM-MD5] Dovecot ready. > > a login mws at alpenjodel.de Test > a NO [AUTHENTICATIONFAILED] Authentication failed. > > Assumptions: > > - I believe that the mysql encrypt function uses the crypt system call, > which in turn uses the DES algorithm with a random salt. > > - I believe that the same is done with the pgsql function call > crypt('Test',gen_salt('des')). > > But obviously some of these assumptions must be wrong. > > Besides that, the variable "default_pass_scheme" is set to "SHA512-CRYPT" in both cases. But obviously, not SHA but DES is used by the working mysql-based setup. I don't understand that. Could someone please explain the relationship between the default_pass_scheme variable and the encryption/hashing algorithm used to store the user passwords? > > And finally: What can I do to migrate to pgsql? > > Thank you > MagnusMigrate? if the passwords are truly as designed already, it shouldnt matter, it should read them, be it for mail, ftp, or httpd, they all read the same thing mysql, or anything that reads sha512. What are you using to insert users, php? perl? , what does the database entry look like? We use a perl backend to add members and hosts, in mysql mypassword field is populated as crypt($password, '$6$' . $salt) I can't help you if its php, i'll leave that for someone who knows php and my php guru is off sick this week with the flu But does your database password field entry start with $6$ ? perhaps your mysql isnt using what you think? As a test, this is testing123 in sha512 $6$Z6I5oyWUed.tmNUs$0ScF2w3ejPWFAX/3F6DgMyWpbXLq0DD6blL8rwBpSHGWaZ9RiXlpo5PPZFoJPZWIuQMETELsXG2YtbsAc8K3q/ copy and paste that into a test users mysql password field directly, and your pgsql directly and see if it works. incidentally, we use default_pass_scheme = CRYPT Which handles all the subsystems crypt options including sha's - providing your system is half modern, if its ten years old dont use that, it'll be likely using the old 8 char limited crypt :) (and dont laugh the number of antique debian and RH boxes I've come across is scary) anyway, so even as a fallback for testing you could insert even an md5 hash into a password field and it will work as well, I wont tell you not to do this in production because of course you know better ;) -- 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] 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 05.10.2017 00:42, Noel Butler wrote:> On 05/10/2017 02:06, Magnus wrote: > >> Hello, >> >> I hope that this mailing list is "alive", since I am looking for a solution for my problem for a long time. >> >> I would like to migrate my existing dovecot installation from mysql to pgsql. But I have problems with the passwords when using pgsql. >> >> The existing and working mysql-based installation looks like this: >> >> dovecot-sql.conf.ext: >> >> driver = mysql >> default_pass_scheme = SHA512-CRYPTThis only means the assumed credentials scheme, not the one that gets created.>> Users are created like this: >> >> INSERT INTO mls_user (idx,domain,password,email) >> VALUES (1,99,ENCRYPT('Test'),'mws at alpenjodel.de'); >> >> This setup is working, which I can verify like this: >> >> $ telnet localhost 143 >> * OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID >> ENABLE IDLE AUTH=PLAIN AUTH=LOGIN AUTH=DIGEST-MD5 >> AUTH=CRAM-MD5] Dovecot ready. >> >> a login mws at alpenjodel.de Test >> OK >> >> Now let's take a look at the pgsql version of the setup: >> >> dovecot-sql.conf.ext: >> >> driver = pgsql >> default_pass_scheme = SHA512-CRYPT >> >> Users are created like this: >> >> INSERT INTO mls_user (idx,domain,password,email) >> VALUES (1,99,crypt('Test',gen_salt('des')),'mws at alpenjodel.de');This does indeed generate a DES based password, which isn't SHA512-CRYPT.>> This setup is not working: >> >> $ telnet localhost 143 >> * OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID >> ENABLE IDLE AUTH=PLAIN AUTH=LOGIN AUTH=DIGEST-MD5 >> AUTH=CRAM-MD5] Dovecot ready. >> >> a login mws at alpenjodel.de Test >> a NO [AUTHENTICATIONFAILED] Authentication failed. >> >> Assumptions: >> >> - I believe that the mysql encrypt function uses the crypt system call, >> which in turn uses the DES algorithm with a random salt. >> >> - I believe that the same is done with the pgsql function call >> crypt('Test',gen_salt('des')). >> >> But obviously some of these assumptions must be wrong. >> >> Besides that, the variable "default_pass_scheme" is set to "SHA512-CRYPT" in both cases. But obviously, not SHA but DES is used by the working mysql-based setup. I don't understand that. Could someone please explain the relationship between the default_pass_scheme variable and the encryption/hashing algorithm used to store the user passwords? >> >> And finally: What can I do to migrate to pgsql? >> >> Thank you >> Magnus > Migrate? if the passwords are truly as designed already, it shouldnt > matter, it should read them, be it for mail, ftp, or httpd, they all > read the same thing mysql, or anything that reads sha512. > > What are you using to insert users, php? perl? , what does the database > entry look like? > > We use a perl backend to add members and hosts, in mysql mypassword > field is populated as crypt($password, '$6$' . $salt) > > I can't help you if its php, i'll leave that for someone who knows php > and my php guru is off sick this week with the flu > > But does your database password field entry start with $6$ ? > perhaps your mysql isnt using what you think? > > As a test, this is testing123 in sha512 > > $6$Z6I5oyWUed.tmNUs$0ScF2w3ejPWFAX/3F6DgMyWpbXLq0DD6blL8rwBpSHGWaZ9RiXlpo5PPZFoJPZWIuQMETELsXG2YtbsAc8K3q/ > > > copy and paste that into a test users mysql password field directly, and > your pgsql directly and see if it works. > > incidentally, we use > > default_pass_scheme = CRYPTThis is good way to support various versions what crypt(3) produces.> Which handles all the subsystems crypt options including sha's - > providing your system is half modern, if its ten years old dont use > that, it'll be likely using the old 8 char limited crypt :) (and dont > laugh the number of antique debian and RH boxes I've come across is > scary) > > anyway, so even as a fallback for testing you could insert even an md5 > hash into a password field and it will work as well, I wont tell you not > to do this in production because of course you know better ;) >Aki
Hello Noel! On 04.10.2017 23:42, Noel Butler wrote:> As a test, this is testing123 in sha512 > $6$Z6I5oyWUed.tmNUs$0ScF2w3ejPWFAX/3F6DgMyWpbXLq0DD6blL8rwBpSHGWaZ9RiXlpo5PPZFoJPZWIuQMETELsXG2YtbsAc8K3q/Again, after inserting this String as is into the pgsql database, I can authenticate with dovecot. Now the next step for me would be to be able to generate such SHA512 hashes with pgsql myself. I made two attempts: 1) select digest('testing123','sha512'); result: ?\x4120117b3190ba5e24044732b0b09aa9ed50eb1567705abcbfa78431a4e0a96b1152ed7f4925966b1c82325e186a8100e692e6d2fcb6702572765820d25c7e9e login fails 2) select encode(digest('testing123','sha512'),'hex'); result: ?4120117b3190ba5e24044732b0b09aa9ed50eb1567705abcbfa78431a4e0a96b1152ed7f4925966b1c82325e186a8100e692e6d2fcb6702572765820d25c7e9e login fails How did you generate your hash and how can I do the same with pgsql? Thanks Magnus