Encrypt And Decrypt Hash sha256 in Postgresql

Question

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.

I just need to know the possibility of it, so far i have found it to be not possible, What i want is any two-way hashing or encryption that can hash/encrypt text to a smaller string and can be converted back ti the same text as well, That too in postgresSQL — Osaf Malik
– Osaf Malik, Commented Nov 13, 2020 at 15:45
If you are just looking for compression Postgres does that behind the scenes anyway for character types, see TOAST — user7070613
– user7070613, Commented Nov 13, 2020 at 15:54

Laurenz Albe · Accepted Answer · 2020-11-13 16:19:13Z

2

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 bytea
Computes a binary hash of the given data.

So you won't be able to recover the original string.

answered Nov 13, 2020 at 16:19

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

naseem · Accepted Answer · 2021-11-03 06:58:50Z

0

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;

answered Nov 3, 2021 at 6:58

naseem

132 silver badges12 bronze badges

Collectives™ on Stack Overflow

Encrypt And Decrypt Hash sha256 in Postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related