Implement SHA1 hashing for PII data when migrating from SQL Server to PostgreSQL

Review SQL Server code.

To review SQL Server code that generates SHA1 hashes, do the following:

Document the hashing algorithm and data transformations.

To document the exact hashing algorithm and data transformations, do the following:

Create pgcrypto extension.

To create the pgcrypto extension, use pgAdmin/psql to run the following command:

CREATE EXTENSION pgcrypto;

Implement a PostgreSQL function.

Implement the following PostgreSQL function to replicate the SQL Server hashing logic. At a high level, this function uses the following steps:

CREATE OR REPLACE FUNCTION utility.hex_to_bigint ( 
     par_val character varying, 
     par_upper character varying DEFAULT 'lower'::character varying) 
RETURNS bigint 
LANGUAGE 'plpgsql' 
AS $BODY$ 
DECLARE 
    retnumber bigint; 
    digest_bytes bytea;
BEGIN 
    if lower(par_upper) = 'upper' 
    then 
        digest_bytes := digest(upper(par_val), 'sha1');
    else 
        digest_bytes := digest((par_val), 'sha1');
    end if; 
    retnumber := ('x' || encode(substring(digest_bytes, length(digest_bytes)-10+1), 'hex'))::bit(64)::bigint; 
    RETURN retnumber; 
END; 
$BODY$;

Test the function.

To test the function, use sample data from SQL Server to verify matching hash values. Run the following command:

select 'alejandro_rosalez@example.com' as Email, utility.hex_to_bigint('alejandro_rosalez@example.com','upper') as HashValue;

--OUTPUT
/*
email 	        hashvalue
"alejandro_rosalez@example.com"	451397011176045063
*/

Create triggers on relevant tables.

To create triggers on relevant tables to automatically generate hash values on insert or update, run the following command:

CREATE OR REPLACE FUNCTION update_email_hash() 
RETURNS TRIGGER 
AS $$ 
BEGIN 
    NEW.email_hash = utility.hex_to_bigint(NEW.email, 'upper'); 
    RETURN NEW; 
END; 
$$ LANGUAGE plpgsql;

CREATE TRIGGER email_hash_trigger BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_email_hash();

Develop a migration script or use AWS DMS.

Develop a migration script or use AWS DMS to populate hash values for existing data (including hash values stored as BIGINT in the source system.) Complete the following tasks:

Use the new PostgreSQL hashing function.

To use the new PostgreSQL hashing function to ensure consistency, do the following:

Identify application queries.

To identify application queries that use hashed values, do the following:

Modify queries.

If necessary, modify queries to use the new PostgreSQL hashing function. Do the following:

Perform testing.

To perform thorough testing with a subset of production data, do the following:

Validate that hash values match.

To validate that hash values match between SQL Server and PostgreSQL, do the following:

Verify application functionality.

To verify application functionality by using the migrated data and the new hashing implementation, do the following: