CODEWITHSUNDEEP
postgresqlhashsha
JamesMatson
JamesMatson

Reputation: 2932

PostgreSQL hash a column using SHA256 and a pre-defined salt

I'm using: PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit running on AWS as Aurora (RDS).

I have managed to get the below working

SELECT encode(digest('[email protected]', 'sha256'),'hex');

But I need to use a salt provided by a 3rd party. Let's say for arguments sake the salt is 'this_is_my_salt'. How can I use this? i could only find examples that generate the salt using algorithms.

The vendor wants us to use their hash to compare email addresses in their database with ours. They haven't specified their Database system, but showed me their query which is:

SELECT '[email protected]' as unhashed_email, sha2('shared_salt_value' || lower('[email protected]')) as hashed_email

And this produces a different hash to me trying the example in postgres using one of the answers below:

SELECT encode(digest('[email protected]' || 'shared_salt_value', 'sha256'),'hex');

My hash starts with db17e.... Their hash starts with b6c84....

Could it be encoding or something causing the difference?

Upvotes: 0

Views: 6078

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246848

That is trivial, just concatenate the string with the salt:

SELECT encode(digest('this_is_my_salt' || '[email protected]', 'sha256'),'hex');

Upvotes: 1

Related Questions