I am trying to hash a string in postgresql using sha256 as follow:
select digest('This is a test','sha256');
this returns below hashed value:
\xc7be1ed902fb8dd4d48997c6452f5d7e509fbcdbe2808b16bcf4edce4c07d14e
Now i want to retrieve my initial string using this hashed value but unable to find anything in the postgres docs regarding it, Any help regarding it will be appreciable.
There is a difference between hashing and encryption:
an encrypted value can be descrypted to get the original back, so encryption is loss-free and two different clear text values will always result in different encrypted values
a hash cannot be decrypted, because information is lost; different values can result in the same hash, although it is desirable that these "collisions" do not happen too often
Hashing is irreversible, while encryption is reversible.
Now digest is a hashing function:
digest(data text, type text) returns bytea digest(data bytea, type text) returns byteaComputes a binary hash of the given
data.
So you won't be able to recover the original string.
You can use pgcrypto extension in Postgresql to save data after encryption.
For insertion,
INSERT INTO tablename (columnname1, columnname2) VALUES (PGP_SYM_ENCRYPT('value1', 'aes_key'), PGP_SYM_ENCRYPT('value2', 'aes_key'));
For fetching,
SELECT PGP_SYM_DECRYPT(columnname1::bytea, 'aes_key') as columnname1, PGP_SYM_DECRYPT(columnname2::bytea, 'aes_key') as columnname2 from tablename;
