Postgres 11.4 crypt extension issue in postgres

Question

i am using pgcrypto extension for password encryption in my PostgreSQL db. i am using same key to encrypt all passwords. When i use same key in different passwords(different strings) it gives same output.

Samples:

db=# select crypt('Sharon_1','alpha');
     crypt     
---------------
 aljp4LCkDT1k.
(1 row)

Time: 2.025 ms
db=# select crypt('Sharon_1trgstysa','alpha');
     crypt     
---------------
 aljp4LCkDT1k.
(1 row)

why is it like that?. When i pass two different strings it should give different encrypted strings as output.Is this a bug?. How can i solve this ? i can't change the key. The key should be always same.

Postgres version:

db=# select version();

 PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28),
 64-bit

Extension version:

db=# \dx pgcrypto

             List of installed extensions
   Name   | Version | Schema |       Description       
----------+---------+--------+-------------------------

 pgcrypto | 1.3     | public    | cryptographic functions

Jim Jones · Accepted Answer · 2020-02-11T12:59:49.100

pgcrypt is meant for something else:

Calculates a crypt(3)-style hash of password. When storing a new password, you need to use gen_salt() to generate a new salt value. To check a password, pass the stored hash value as salt, and test whether the result matches the stored value.

The following CTE encrypts the passwords using a md5 salt algorithm and the select compares a given password with the ones in the CTE:

WITH j (val) AS (
  VALUES 
    (crypt('Sharon_1',gen_salt('md5'))),
    (crypt('Sharon_1trgstysa',gen_salt('md5')))
) 
SELECT 
  val = crypt('Sharon_1',val), -- entered password to compare!
  val -- stored password
FROM j;

 ?column? |                val                 
----------+------------------------------------
 t        | $1$XpqL58HA$k2G55BjtVFQxHVe/jpu.2.
 f        | $1$0OIuDMkZ$PH2cDjG.aRzUAvtUtvf3E1
(2 Zeilen)

To encrypt and decrypt with symmetric PGP keys try pgp_sym_encrypt and pgp_sym_decrypt, e.g.

WITH j (val) AS (
  VALUES 
    (pgp_sym_encrypt('Sharon_1','alpha')),
    (pgp_sym_encrypt('Sharon_1trgstysa','alpha'))     
) 
SELECT pgp_sym_decrypt(val,'alpha') FROM j;


 pgp_sym_decrypt  
------------------
 Sharon_1
 Sharon_1trgstysa
(2 Zeilen)

score 2 · Answer 2 · answered Feb 11 '20 at 15:44

The algorithm to be used by crypt is embedded in the format of the salt. Your salt "alpha" doesn't specify an algorithm, so crypt uses des. des only looks at the first 8 characters of the password (and the first 2 characters of the salt), and your two passwords don't differ in the first 8 characters.

i can't change the key.

Then your system is broken by design.

Postgres 11.4 crypt extension issue in postgres

2 Answers2