加解密扩展 pgcrypto 使用实例
- 1.需求说明
- 2.工具说明
- 2.1 环境说明
- 2.2 插件添加
- 3.实例分析
- 3.1 测试数据
- 3.2 进行加密
- 3.3 数据导出
- 3.3.1 Navicat 导出
- 3.3.2 copy 命令导出
- 3.4 数据解密
- 3.4.1 Navicat 导入
- 3.4.2 copy 导入
- 3.5 坑
1.需求说明
从内网导出敏感数据的时候，对数据进行加密是基本操作。就这么一个简单的加密函数我也遇到了一个坑。
2.工具说明
pgcrypto 是 PostgreSQL 的一个扩展模块，为数据库添加对数据进行加密/解密、哈希计算和随机数生成的支持，允许直接在 SQL 层面操作敏感信息（如密码存储、安全令牌生成等）。
2.1 环境说明
SELECT VERSION( )
-- 数据库版本信息
PostgreSQL 12.12 (Debian 12.12-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
pgcrypto 版本查询：
-- 查询可使用的插件
SELECT * FROM pg_available_extensions;pgcrypto 1.3 cryptographic functions
2.2 插件添加
-- 插件添加
CREATE extension pgcrypto;
插件是数据库级别的，需要在使用的数据库进行添加。使用 AES（高级加密标准）、DES、Blowfish 等算法加密/解密文本或二进制数据。
- 示例函数：
pgp_sym_encrypt(data, key),pgp_sym_decrypt(ciphertext, key)。
注意：密钥需妥善管理，丢失则无法解密！
3.实例分析
3.1 测试数据
SELECT * FROM test_export;
| name | age | phone | address |
|---|---|---|---|
| TOM | 4 | 1230666 | TOMHOME |
3.2 进行加密
SELECT name, age, pgp_sym_encrypt ( phone, 'cryptogram' ) AS phone, address
FROMtest_export;
| name | age | phone | address |
|---|---|---|---|
| TOM | 4 | (BLOB) 73 bytes | TOMHOME |
这里仅对 phone 进行了加密，加密后是 blob 类型的数据。
3.3 数据导出
3.3.1 Navicat 导出
"name" "age" "phone" "address"
"TOM" "4" "ww0EBwMC/PMcgtRyDvtq0jgBRaXN2VwZV0+Ho3TEEQbJTn1R8Q7zBA/gwnA09FUgmpkCxzeUjMshDTDs7e/UEYLkyMjqRQFOrA==" "TOMHOME"
3.3.2 copy 命令导出
大数据导出时还是使用数据库的 copy 命令比较稳定高效：
-- 数据导出
COPY ( SELECT NAME, age, pgp_sym_encrypt ( phone, 'cryptogram' ) AS phone, address FROM test_export ) TO '/var/lib/postgresql/data/20250828.txt';
TOM 4 \\xc30d04070302fc5b66be55ee75067ad2380151399eaf4174798b5a461113554ec1d5bc998167b5b118e69c979bc117ef53bc7e7b7dd1baddccaa3c09efe0d3c80014ddfe5801e8eefe TOMHOME
是不是发现了数据格式的不同。
3.4 数据解密
加密数据导入表的 phone 字段的类型是 bytea 的，需要注意：
CREATE TABLE "public"."test_import" ("name" varchar(255) COLLATE "pg_catalog"."default","age" int4,"phone" bytea,"address" varchar(255) COLLATE "pg_catalog"."default"
);
分别将 Navicat 和 copy 导出的数据导入到数据库，然后进行解密。
3.4.1 Navicat 导入
将 Navicat 导出的数据再使用 Navicat 导入到 test_import 表里：
数据解密：
SELECTname, age, pgp_sym_decrypt ( phone, 'cryptogram' ) AS phone, address
FROMtest_import;
解密结果：
3.4.2 copy 导入
COPY test_import ( name, age, phone, address ) FROM '/var/lib/postgresql/data/20250828.txt';
解密操作和结果都是一致的。
3.5 坑
将 copy 导出的数据用 Navicat 导入：
大家应该已经料到了吧，Navicat 和 copy 导出的数据格式本身就不一样，看一下解密情况：
毫无疑问，无法解密。
)
