PostgreSQL【应用 04】加解密扩展 pgcrypto 使用实例（加密、导出、导入、解密流程说明）

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;

3.2 进行加密

SELECT 
  name, age, pgp_sym_encrypt ( phone, 'cryptogram' ) AS phone, address
FROM
	test_export;

这里仅对 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 表里：

数据解密：

SELECT
  name, age, pgp_sym_decrypt ( phone, 'cryptogram' ) AS phone, address
FROM
	test_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 导出的数据格式本身就不一样，看一下解密情况：

毫无疑问，无法解密。