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