Content-Type: multipart/alternative;
 boundary="------------4Pzl9smxAqt05qkxLtzQmKAi"
Message-ID: <b65823d5-39e5-2fd1-7c91-ae3f1fa94e22@gmail.com>
Date: Wed, 15 Feb 2023 15:03:49 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.7.2
Subject: Re: BUG #17796: pgcrypto undecryptable blowfish data previous stored
 with openssl 1.1.1 with builtin decrypter one
To: Daniel Gustafsson <daniel@yesql.se>, pgsql-bugs@lists.postgresql.org
References: <17796-a35e6387be088a9b@postgresql.org>
 <AE5DB690-E8EE-4E4C-917D-10ED65418459@yesql.se>
Content-Language: it
From: "agharta82@gmail.com" <agharta82@gmail.com>
Disposition-Notification-To: "agharta82@gmail.com" <agharta82@gmail.com>
In-Reply-To: <AE5DB690-E8EE-4E4C-917D-10ED65418459@yesql.se>
Archived-At: 
 <https://www.postgresql.org/message-id/b65823d5-39e5-2fd1-7c91-ae3f1fa94e22%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------4Pzl9smxAqt05qkxLtzQmKAi
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hi Daniel,

Thanks for quick reply.

I've found the rapid way to get hash type: htpasswd. Correct me if I am 
wrong.

#openssl version
OpenSSL 1.1.1k  FIPS 25 Mar 2021

#htpasswd -nbBC 10 USER PASSWORD
USER:$2y$10$YZNMKHWMye0yY3vvWUK8bOTrJjSRdD.sS2kXythshRkr2l1Qrcfha


So, in my Rocky Linux 8 is $2y$

Now -as you writed- it's clear: hashes are incompatibles.

For me, actually, the better way is to move to aes256 instead of bf, due 
to openssl bf deprecation. This should make me sleep well for a long 
time (i hope).

Meanwhile, to solve the case:

  * in Linux is quite simple  (but insecure), enable legacy ciphers.
  * in Windows....uh....i've not found how to solve it because (if i am
    not wrong) PG  15  is built with openssl 3 internally and i don't
    known how to enable legacy mode in it.


Actually in Windows (i disagree but my customer wants to use it) the 
only solution i've found is:

  * decrypt data
  * backup db
  * update PG to a version with openssl 3
  * restore db
  * re-encrypt data


Many thanks for your clarification, it has been illuminating!

My best regards,

Agharta


Il 15/02/23 14:25, Daniel Gustafsson ha scritto:
>> On 15 Feb 2023, at 13:15, PG Bug reporting form<noreply@postgresql.org>  wrote:
>> So, the problem seems that the builtin bf cipher implementation cannot
>> decode the openssl one.
> The internal implementation only support $2a$ hashes, are the OpenSSL hashes
> $2b$ by any chance? The prefix of the hash includes this version number.
>
> There is a patch floating around for improving blowfish support in pgcrypto but
> it hasn't made its way in.
>
>> Side note: if I enable legacy mode in openssl3 all works fine and pgcrypto
>> use the openssl chiper, oblivious.
>>
>> Can it be solved?
> If you relied on Blowfish in OpenSSL 1.1.1 then enabling the legacy provider in
> OpenSSL 3.x, or downgrading to OpenSSL 1.1.1 is a solution.
>
> --
> Daniel Gustafsson
>
--------------4Pzl9smxAqt05qkxLtzQmKAi
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi Daniel,</p>
    <p>Thanks for quick reply.</p>
    <p>I've found the rapid way to get hash type: htpasswd. Correct me
      if I am wrong.</p>
    <p>#openssl version<br>
      OpenSSL 1.1.1k  FIPS 25 Mar 2021<br>
    </p>
    <p>#htpasswd -nbBC 10 USER PASSWORD<br>
      USER:$2y$10$YZNMKHWMye0yY3vvWUK8bOTrJjSRdD.sS2kXythshRkr2l1Qrcfha<br>
    </p>
    <p><br>
    </p>
    <p>So, in my Rocky Linux 8 is $2y$</p>
    <p>Now -as you writed- it's clear: hashes are incompatibles.</p>
    <p>For me, actually, the better way is to move to aes256 instead of
      bf, due to openssl bf deprecation. This should make me sleep well
      for a long time (i hope).</p>
    <p>Meanwhile, to solve the case:<br>
    </p>
    <ul>
      <li>in Linux is quite simple  (but insecure), enable legacy
        ciphers.</li>
      <li>in Windows....uh....i've not found how to solve it because (if
        i am not wrong) PG  15  is built with openssl 3 internally and i
        don't known how to enable legacy mode in it.</li>
    </ul>
    <p><br>
    </p>
    <p>Actually in Windows (i disagree but my customer wants to use it)
      the only solution i've found is:</p>
    <ul>
      <li>decrypt data</li>
      <li>backup db<br>
      </li>
      <li>update PG to a version with openssl 3</li>
      <li>restore db<br>
      </li>
      <li>re-encrypt data</li>
    </ul>
    <p><br>
    </p>
    <p>Many thanks for your clarification, it has been illuminating!<br>
    </p>
    <p>My best regards,</p>
    <p>Agharta<br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
    <div class="moz-cite-prefix">Il 15/02/23 14:25, Daniel Gustafsson ha
      scritto:<br>
    </div>
    <blockquote type="cite"
      cite="mid:AE5DB690-E8EE-4E4C-917D-10ED65418459@yesql.se">
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">On 15 Feb 2023, at 13:15, PG Bug reporting form <a class="moz-txt-link-rfc2396E" href="mailto:noreply@postgresql.org">&lt;noreply@postgresql.org&gt;</a> wrote:
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">So, the problem seems that the builtin bf cipher implementation cannot
decode the openssl one.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
The internal implementation only support $2a$ hashes, are the OpenSSL hashes
$2b$ by any chance? The prefix of the hash includes this version number.

There is a patch floating around for improving blowfish support in pgcrypto but
it hasn't made its way in.

</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">Side note: if I enable legacy mode in openssl3 all works fine and pgcrypto
use the openssl chiper, oblivious.

Can it be solved? 
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
If you relied on Blowfish in OpenSSL 1.1.1 then enabling the legacy provider in
OpenSSL 3.x, or downgrading to OpenSSL 1.1.1 is a solution.

--
Daniel Gustafsson

</pre>
    </blockquote>
  </body>
</html>

--------------4Pzl9smxAqt05qkxLtzQmKAi--