从 SQL Server 迁移到 PostgreSQL 时，对 PII 数据实施 SHA1 哈希处理

聚焦模式

从 SQL Server 迁移到 PostgreSQL 时，对 PII 数据实施 SHA1 哈希处理 - AWS Prescriptive Guidance

由 Rajkumar Raghuwanshi (AWS) 和 Jagadish Kantubugata (AWS) 创作

摘要

此模式描述了在从 SQL Server 迁移到适用于 PostgreSQL 的亚马逊 RDS 或兼容 Amazon Aurora PostgreSQL 时，如何对电子邮件地址实施安全哈希算法 1 (SHA1) 哈希。电子邮件地址是个人身份信息 (PII) 的一个示例。PII 是直接查看或与其他相关数据配对时可用于合理推断个人身份的信息。

这种模式涵盖了在不同的数据库归类和字符编码中保持一致的哈希值所面临的挑战，并提供了使用 PostgreSQL 函数和触发器的解决方案。尽管这种模式侧重于 SHA1 哈希，但它可以适应PostgreSQL模块支持的其他哈希算法。pgcrypto请务必考虑哈希策略的安全影响，如果要处理敏感数据，请咨询安全专家。

先决条件和限制

先决条件

活跃的 AWS 账户
源 SQL 服务器数据库
目标 PostgreSQL 数据库（适用于 PostgreSQL 的亚马逊 RDS 或兼容 Aurora PostgreSQL）
PL/pgSQL 编码专业知识

限制

这种模式需要根据用例更改数据库级别的排序规则。
尚未评估对大型数据集的性能影响。
有些 AWS 服务并非全部可用 AWS 区域。有关区域可用性，请参阅按地区划分的AWS 服务。有关特定终端节点，请参阅服务终端节点和配额，然后选择服务的链接。

产品版本

Microsoft SQL Server 2012 或更高版本

架构

源技术堆栈

SQL Server
NET Framework。

目标技术堆栈

PostgreSQL
pgcrypto延期

自动化和扩缩

为了便于维护，可以考虑将哈希函数作为存储过程来实现。
对于大型数据集，请评估性能并考虑批处理或索引策略。

工具

AWS 服务

兼容 Amazon Aurora PostgreSQL 是一款完全托管、符合 ACID 标准的关系数据库引擎，可帮助您设置、操作和扩展 PostgreSQL 部署。
AWS Database Migration Service (AWS DMS) 可帮助您将数据存储迁移到云和本地设置的组合中， AWS Cloud 或者迁移到云端和本地设置的组合之间。
亚马逊关系数据库服务 Amazon RDS f or PostgreSQL 可帮助你在中设置、操作和扩展 PostgreSQL 关系数据库。 AWS Cloud
AWS Schema Conversion Tool (AWS SCT) 通过自动将源数据库架构和大部分自定义代码转换为与目标数据库兼容的格式来支持异构数据库迁移。

其他工具

pgAdmin 是一种适用于 PostgreSQL 的开源管理工具。它提供了一个图形界面，可帮助您创建、维护和使用数据库对象。
SQL Server 管理工作室 (SSMS) 是一个用于管理任何 SQL 基础架构的集成环境。

最佳实践

使用适当的归类设置来处理目标数据库端的特殊字符。
使用各种电子邮件地址（包括带有非 ASCII 字符的地址）进行彻底测试。
保持应用程序层和数据库层之间大写和小写处理的一致性。
使用哈希值对查询的性能进行基准测试。

操作说明

Task	描述	所需技能
查看 SQL 服务器代码。	要查看生成 SHA1 哈希值的 SQL Server 代码，请执行以下操作：分析现有的 SQL Server SHA1 哈希实现。确定用于生成哈希值的确切方法。记录输入参数和输出格式。查看所有数据类型转换或转换。检查归类设置及其影响。	数据工程师、数据库管理员、应用程序开发人员
记录哈希算法和数据转换。	要记录确切的哈希算法和数据转换，请执行以下操作：创建哈希过程的详细技术文档。记录 step-by-step转换逻辑。指定输入和输出格式以及数据类型。包括边缘案例和特殊字符处理。	应用程序开发人员、数据工程师、数据库管理员

分析源代码哈希实现

Task	描述	所需技能
查看 SQL 服务器代码。	要查看生成 SHA1 哈希值的 SQL Server 代码，请执行以下操作：分析现有的 SQL Server SHA1 哈希实现。确定用于生成哈希值的确切方法。记录输入参数和输出格式。查看所有数据类型转换或转换。检查归类设置及其影响。	数据工程师、数据库管理员、应用程序开发人员
记录哈希算法和数据转换。	要记录确切的哈希算法和数据转换，请执行以下操作：创建哈希过程的详细技术文档。记录 step-by-step转换逻辑。指定输入和输出格式以及数据类型。包括边缘案例和特殊字符处理。	应用程序开发人员、数据工程师、数据库管理员

Task 描述所需技能

Task	描述	所需技能
创建`pgcrypto`扩展。	要创建`pgcrypto`扩展，`pgAdmin/psql`请使用运行以下命令： `CREATE EXTENSION pgcrypto;`	数据库管理员、数据工程师
实现一个 PostgreSQL 函数。	实现以下 PostgreSQL 函数来复制 SQL Server 的哈希逻辑。简而言之，此函数使用以下步骤：（可选）将输入转换为大写。创建输入的 SHA1 哈希值。取此哈希值的最后 10 个字节（80 位）。将这些字节转换为 64 位整数。 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 */`	应用程序开发人员、数据库管理员、数据工程师

创建pgcrypto扩展。

要创建pgcrypto扩展，pgAdmin/psql请使用运行以下命令：


CREATE EXTENSION pgcrypto;

数据库管理员、数据工程师

实现一个 PostgreSQL 函数。

实现以下 PostgreSQL 函数来复制 SQL Server 的哈希逻辑。简而言之，此函数使用以下步骤：

（可选）将输入转换为大写。
创建输入的 SHA1 哈希值。
取此哈希值的最后 10 个字节（80 位）。
将这些字节转换为 64 位整数。


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
*/

应用程序开发人员、数据库管理员、数据工程师

创建 PostgreSQL 哈希函数

Task 描述所需技能

Task	描述	所需技能
创建`pgcrypto`扩展。	要创建`pgcrypto`扩展，`pgAdmin/psql`请使用运行以下命令： `CREATE EXTENSION pgcrypto;`	数据库管理员、数据工程师
实现一个 PostgreSQL 函数。	实现以下 PostgreSQL 函数来复制 SQL Server 的哈希逻辑。简而言之，此函数使用以下步骤：（可选）将输入转换为大写。创建输入的 SHA1 哈希值。取此哈希值的最后 10 个字节（80 位）。将这些字节转换为 64 位整数。 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 */`	应用程序开发人员、数据库管理员、数据工程师

创建pgcrypto扩展。

要创建pgcrypto扩展，pgAdmin/psql请使用运行以下命令：


CREATE EXTENSION pgcrypto;

数据库管理员、数据工程师

实现一个 PostgreSQL 函数。

实现以下 PostgreSQL 函数来复制 SQL Server 的哈希逻辑。简而言之，此函数使用以下步骤：

（可选）将输入转换为大写。
创建输入的 SHA1 哈希值。
取此哈希值的最后 10 个字节（80 位）。
将这些字节转换为 64 位整数。


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
*/

应用程序开发人员、数据库管理员、数据工程师

Task 描述所需技能

Task	描述	所需技能
在相关表上创建触发器。	要在相关表上创建触发器以在插入或更新时自动生成哈希值，请运行以下命令： `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();`	应用程序开发人员、数据工程师、数据库管理员

在相关表上创建触发器。

要在相关表上创建触发器以在插入或更新时自动生成哈希值，请运行以下命令：


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

应用程序开发人员、数据工程师、数据库管理员

实现自动哈希的触发器

Task 描述所需技能

Task	描述	所需技能
在相关表上创建触发器。	要在相关表上创建触发器以在插入或更新时自动生成哈希值，请运行以下命令： `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();`	应用程序开发人员、数据工程师、数据库管理员

在相关表上创建触发器。

要在相关表上创建触发器以在插入或更新时自动生成哈希值，请运行以下命令：


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

应用程序开发人员、数据工程师、数据库管理员

Task	描述	所需技能
开发迁移脚本或使用 AWS DMS。	开发迁移脚本或 AWS DMS 用于填充现有数据的哈希值（包括存储在源系统`BIGINT`中的哈希值）。完成以下任务：使用哈希值为数据传输创建迁移脚本。使用适当的转换规则配置 AWS DMS 任务。在中设置源端点和目标端点 AWS DMS。实现错误处理和日志记录机制。为大型数据集设计批处理策略。创建用于数据验证的验证查询。	数据工程师、应用程序开发人员、数据库管理员
使用新的 PostgreSQL 哈希函数。	要使用新的 PostgreSQL 哈希函数来确保一致性，请执行以下操作：实施验证程序以验证哈希一致性。在源系统和目标系统之间创建比较脚本。为哈希值验证设置自动测试。记录任何差异和解决步骤。	应用程序开发人员、数据库管理员、 DevOps 工程师

迁移现有数据

Task	描述	所需技能
开发迁移脚本或使用 AWS DMS。	开发迁移脚本或 AWS DMS 用于填充现有数据的哈希值（包括存储在源系统`BIGINT`中的哈希值）。完成以下任务：使用哈希值为数据传输创建迁移脚本。使用适当的转换规则配置 AWS DMS 任务。在中设置源端点和目标端点 AWS DMS。实现错误处理和日志记录机制。为大型数据集设计批处理策略。创建用于数据验证的验证查询。	数据工程师、应用程序开发人员、数据库管理员
使用新的 PostgreSQL 哈希函数。	要使用新的 PostgreSQL 哈希函数来确保一致性，请执行以下操作：实施验证程序以验证哈希一致性。在源系统和目标系统之间创建比较脚本。为哈希值验证设置自动测试。记录任何差异和解决步骤。	应用程序开发人员、数据库管理员、 DevOps 工程师

Task	描述	所需技能
识别应用程序查询。	要识别使用哈希值的应用程序查询，请执行以下操作：使用哈希值分析应用程序代码库中的查询。查看引用哈希操作的存储过程和函数。记录查询性能指标和执行计划。确定对基于哈希的查找的依赖关系。绘制受影响的应用程序组件。	应用程序开发人员、数据库管理员、数据工程师
修改查询。	如有必要，可以修改查询以使用新的 PostgreSQL 哈希函数。执行以下操作：重构现有查询以使用 PostgreSQL 哈希函数。更新存储过程和函数。实施和测试新的查询模式。优化修改后的查询以提高性能。	应用程序开发人员、数据库管理员、数据工程师

更新应用程序查询

Task	描述	所需技能
识别应用程序查询。	要识别使用哈希值的应用程序查询，请执行以下操作：使用哈希值分析应用程序代码库中的查询。查看引用哈希操作的存储过程和函数。记录查询性能指标和执行计划。确定对基于哈希的查找的依赖关系。绘制受影响的应用程序组件。	应用程序开发人员、数据库管理员、数据工程师
修改查询。	如有必要，可以修改查询以使用新的 PostgreSQL 哈希函数。执行以下操作：重构现有查询以使用 PostgreSQL 哈希函数。更新存储过程和函数。实施和测试新的查询模式。优化修改后的查询以提高性能。	应用程序开发人员、数据库管理员、数据工程师

Task	描述	所需技能
执行测试。	要对生产数据的子集进行全面测试，请执行以下操作：为数据子集验证创建测试计划。提取具有代表性的生产数据样本。使用适当的配置设置测试环境。执行数据加载和转换测试。执行音量和压力测试。	应用程序开发人员、数据工程师、数据库管理员
验证哈希值是否匹配。	要验证 SQL Server 和 PostgreSQL 之间的哈希值是否匹配，请执行以下操作：为哈希值开发比较脚本。为哈希匹配创建验证报告。实施自动验证程序。记录发现的任何差异。分析并解决哈希不匹配的问题。	应用程序开发人员、数据工程师、数据库管理员
验证应用程序的功能。	要使用迁移的数据和新的哈希实现来验证应用程序的功能，请执行以下操作：执行 end-to-end应用程序测试。使用哈希数据验证所有应用程序功能。使用新的实现来测试应用程序性能。验证 API 集成和依赖关系。	应用程序开发人员、数据库管理员、数据工程师

测试和验证

Task	描述	所需技能
执行测试。	要对生产数据的子集进行全面测试，请执行以下操作：为数据子集验证创建测试计划。提取具有代表性的生产数据样本。使用适当的配置设置测试环境。执行数据加载和转换测试。执行音量和压力测试。	应用程序开发人员、数据工程师、数据库管理员
验证哈希值是否匹配。	要验证 SQL Server 和 PostgreSQL 之间的哈希值是否匹配，请执行以下操作：为哈希值开发比较脚本。为哈希匹配创建验证报告。实施自动验证程序。记录发现的任何差异。分析并解决哈希不匹配的问题。	应用程序开发人员、数据工程师、数据库管理员
验证应用程序的功能。	要使用迁移的数据和新的哈希实现来验证应用程序的功能，请执行以下操作：执行 end-to-end应用程序测试。使用哈希数据验证所有应用程序功能。使用新的实现来测试应用程序性能。验证 API 集成和依赖关系。	应用程序开发人员、数据库管理员、数据工程师

故障排除

事务	解决方案
哈希值不匹配。	验证源和目标之间的字符编码和排序规则。有关更多信息，请参阅在 Amazon Aurora 和 Amazon RDS 上管理 PostgreSQL 中的排序规则更改（博客）。AWS

其他信息

这种模式侧重于 SHA1 哈希，但可以适应 PostgreSQL 的 pgcrypto 模块支持的其他哈希算法。请务必考虑哈希策略的安全影响，如果要处理敏感数据，请咨询安全专家。

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

使用 Aurora 全局数据库模拟 Oracle DR

以增量方式从 Amazon RDS for Oracle 迁移至 Amazon RDS for PostgreSQL

下一主题：

以增量方式从 Amazon RDS for Oracle 迁移至 Amazon RDS for PostgreSQL

上一主题：

使用 Aurora 全局数据库模拟 Oracle DR

需要帮助吗？

本页内容

此页内容对您是否有帮助？

提供反馈

隐私网站条款 Cookie 首选项

选择您的 Cookie 首选项

自定义 Cookie 首选项

关键

性能

功能

广告

无法保存 Cookie 首选项

从 SQL Server 迁移到 PostgreSQL 时，对 PII 数据实施 SHA1 哈希处理

摘要

先决条件和限制

架构

工具

最佳实践

操作说明

分析源代码哈希实现

创建 PostgreSQL 哈希函数

实现自动哈希的触发器

迁移现有数据

更新应用程序查询

测试和验证

故障排除

相关资源

其他信息

下一主题：

上一主题：

需要帮助吗？

本页内容

此页内容对您是否有帮助？