postgresqlpgcrypto

Postgres 11.4 crypt extension issue in postgres


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

Solution

  • 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)