加解密擴展 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 導出的數據格式本身就不一樣,看一下解密情況:
毫無疑問,無法解密。