Use pgcrypto to verify passwords generated by password_hash

Question

I have password hashes stored in a Postgresql database generated with:

password_hash($password, PASSWORD_DEFAULT);

Now I would like to also be able to verify a user password with Postgresql and pgcrypto.

But pgcrypto's crypt() function is not able to verify the existing password hashes.

However - I can verify password hashes generated by Postgresql with PHP's password_verify.

For example:

password_hash('hello', PASSWORD_DEFAULT);
$2y$10$fD2cw7T6s4dPvk1SFHmiJeRRaegalE/Oa3zSD6.x5WncQJC9wtCAS

postgres=# SELECT crypt('hello', gen_salt('bf'));
                            crypt                             
--------------------------------------------------------------
 $2a$06$7/AGAXFSTCMu9r.08oD.UulYR0/05q7lmuCTC68Adyu/aNJkzpoIW

Verification:

// php_verify with the Postgresql hash
php > var_dump(password_verify('hello', '$2a$06$7/AGAXFSTCMu9r.08oD.UulYR0/05q7lmuCTC68Adyu/aNJkzpoIW'));
bool(true)

postgres=# SELECT crypt('hello', '$2y$10$fD2cw7T6s4dPvk1SFHmiJeRRaegalE/Oa3zSD6.x5WncQJC9wtCAS');
     crypt     
---------------
 $2JgKNLEdsV2E
(1 Zeile)

My questions are basically:

Am I doing it wrong?
If this is not possible: Is there a migration path to make this possible?

`$2y$` is a PHP specific algorithm, since its `$2a$` implementation had a bug, IIRC. The problem is probably that very few other systems support it. — deceze, Apr 07 '19 at 09:48
This way, the password [will be logged](https://stackoverflow.com/questions/64003333/rds-postgres-how-to-avoid-password-statement-into-log-file) in the logfile — Code4R7, May 23 '23 at 15:20

score 5 · Accepted Answer · answered Apr 07 '19 at 17:04

From the answer to: Where 2x prefix are used in BCrypt? which has all the gory details about the $2$ variants born from implementation bugs:

There is no difference between 2a, 2x, 2y, and 2b. If you wrote your implementation correctly, they all output the same result.

Based on that, one may take the hash generated by PHP's password_hash, replace the leading $2y$ by $2a$ and pass it as the second argument of pgcrypto's crypt().

Using the value from your example:

postgres=# \set hash '$2a$10$fD2cw7T6s4dPvk1SFHmiJeRRaegalE/Oa3zSD6.x5WncQJC9wtCAS'

postgres=# SELECT crypt('hello', :'hash') = :'hash'
 ?column? 
----------
 t

Wow... Thanks for sharing/investigating this! – madflow Apr 08 '19 at 09:54 — madflow, Apr 08 '19 at 09:54

Use pgcrypto to verify passwords generated by password_hash

1 Answers1

Linked