moving from mysql to pgsql

[Aki Tuomi]

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-CRYPT

This 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 = CRYPT 
This 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