SQL Server から PostgreSQL に移行する際に、PII データに SHA1 ハッシュを実装する

SQL Server コードを確認します。

SHA1 ハッシュを生成する SQL Server コードを確認するには、以下を行います。

ハッシュアルゴリズムとデータ変換を文書化します。

ハッシュアルゴリズムとデータ変換を正確に文書化するには、以下を行います。

pgcrypto エクステンションを作成します。

pgcrypto エクステンションを作成するには、pgAdmin/psql を使用して次のコマンドを実行します。

CREATE EXTENSION pgcrypto;

PostgreSQL 関数を実装します。

SQL Server のハッシュロジックをレプリケートするには、次の PostgreSQL 関数を実装します。大まかに言うと、この関数では次のステップを使用します。

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$;

関数をテストします。

関数をテストするには、SQL Server のサンプルデータを使用して、一致するハッシュ値を検証します。次のコマンドを実行します。

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

移行スクリプトを開発するか、 を使用します AWS DMS。

移行スクリプトを作成するか AWS DMS 、 を使用して既存のデータのハッシュ値 (BIGINTソースシステムに として保存されているハッシュ値を含む) を入力します。以下のタスクを実行します。

新しい PostgreSQL ハッシュ関数を使用します。

新しい PostgreSQL ハッシュ関数を使用して一貫性を確保するには、以下を行います。

アプリケーションクエリを特定します。

ハッシュ値を使用するアプリケーションクエリを特定するには、以下を行います。

クエリを変更します。

必要に応じて、新しい PostgreSQL ハッシュ関数を使用するようにクエリを変更します。以下の操作を実行します。

テストを実行します。

本番データのサブセットを使用して徹底的なテストを実行するには、以下を行います。

ハッシュ値が一致することを検証します。

SQL Server と PostgreSQL 間でハッシュ値が一致することを検証するには、以下を行います。

アプリケーションの機能を検証します。

移行されたデータと新しいハッシュ実装を使用してアプリケーションの機能を検証するには、以下を行います。