pgcrypto 是 PostgreSQL 的一个扩展模块，它提供了丰富的加密函数，可以有效保护数据库内的敏感数据。其功能主要分为两类：单向加密和双向加密。
- 单向加密：属于不可逆加密，无法根据密文解密出明文，适用于数据的验证，例如登录密码验证。常用的单向加密算法有MD5、SHA、HMAC等。
- 双向加密：属于可逆加密，根据密文和密钥可解密出明文，适用于数据的安全传输与存储，例如电子支付、数字签名等。常用的双向加密算法有AES、DES、RSA、ECC等。
安装扩展
安装过程非常简单，只需在目标数据库中执行 CREATE EXTENSION 命令即可。
[highgo@hgdb~]$ psql hgdb highgo -p 1521
hgdb=>create extension pgcrypto;
hgdb=> \dx
List of installed extensions
Name | Version | Schema | Description
-------------------+---------+------------+-------------------------------------------------------
credcheck |2.8.0| public | credcheck - postgresql plain text credential checker
gb18030_2022 |1.0| pg_catalog | support gb18030 2022with extension
hg_misc |1.0| public | miscellaneous feature of highgo database
ivorysql_ora |1.0| sys | Oracle Compatible extenison on Postgres Database
pg_audit |1.1| public | hgdb audit
pg_object |1.0| pg_catalog | pg_object systemtable extenison on Postgres Database
pg_track_settings |2.1.2| public | Track settings changes
pgagent |4.2| pgagent | A PostgreSQL job scheduler
pgcrypto |1.3| public | cryptographic functions
plisql |1.0| pg_catalog | PL/iSQL procedural language
plpgsql |1.0| pg_catalog | PL/pgSQL procedural language
postgres_fdw |1.1| public |foreign-data wrapper for remote PostgreSQL servers
restricted_dba |1.0| public | restricted_dba
sqlite_fdw |1.1| public | SQLite Foreign Data Wrapper
uuid-ossp |1.1| sys | generate universally unique identifiers (UUIDs)
(15 rows)
随机数生成函数
在加密场景中，生成高强度的、加密安全的随机数至关重要，常用于生成密钥、盐值、初始化向量（IV）、验证码等。
- 安全性：区别于 PostgreSQL 原生的
random() 函数（伪随机数，可预测），pgcrypto 的随机数函数基于密码学安全的随机源，不可预测。
- 常用函数：
gen_random_bytes(n int) returns bytea：生成N个字节的随机二进制数据。最常用，例如生成16位IV向量、随机密钥。gen_random_uuid() returns uuid：生成标准的随机版本UUID（UUIDv4），全局唯一，可替代原生的 uuid_generate_v4()。
hgdb=>SELECT gen_random_bytes(8);
gen_random_bytes
--------------------
\x830e9ead0792945c
hgdb=>SELECT gen_random_bytes(16);
gen_random_bytes
------------------------------------
\x9514f32f18e7764f9f31c272a9224eaa
hgdb=>select gen_random_uuid();
gen_random_uuid
--------------------------------------
e31a32c9-d85f-43ab-9e05-65672e7d0d25
hgdb=>select uuid_generate_v4();
uuid_generate_v4
--------------------------------------
b366354c-c743-4b83-8482-6cf45632f606
单向加密
单向加密（哈希）用于生成数据的唯一指纹，常用于数据完整性校验、密码存储（需加盐）、文件去重等场景。其核心特点是不可逆。
通用哈希函数 (无盐)
这类函数为数据生成固定哈希值，速度快，适合校验和去重，但因无盐值，不适合直接存储密码（易被彩虹表攻击）。
digest(data text/bytea, type text) returns bytea：根据指定算法生成数据的二进制哈希值。
type：算法类型，包括 md5, sha1, sha224, sha256, sha384, sha512。推荐使用 sha256。
-- 查看函数定义
hgdb=> \df digest
List of functions
Schema | Name |Result data type | Argument data types | Type
--------+--------+------------------+---------------------+------
public | digest | bytea | bytea, text | func
public | digest | bytea | text, text | func
(2 rows)
-- 使用示例
hgdb=>SELECT digest('199****1885','md5');
digest
------------------------------------
\xd3845a9920b4a3bf985e71d19cfcb998
hgdb=>select md5('199****1885');
md5
----------------------------------
d3845a9920b4a3bf985e71d19cfcb998
hgdb=>SELECT digest('199****1885','sha256');
digest
--------------------------------------------------------------------
\x1fc49c2b67368455943466cf7ae00f8e3caab063393fcff9b518a1b2fa00ec47
hgdb=>SELECT encode(digest('199****1885','sha256'),'hex');
encode
------------------------------------------------------------------
1fc49c2b67368455943466cf7ae00f8e3caab063393fcff9b518a1b2fa00ec47
-- encode 函数用于将二进制字符串转换为十六进制的文本
使用场景示例：存储用户密码
-- 创建用户表
hgdb=# create table users (id serial primary key, username varchar2(20) not null unique, password text not null);
hgdb=# \d+ users
Table "public.users"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------+--------------+-----------+----------+-----------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('users_id_seq'::regclass) | plain | | |
username | varchar2(20) | | not null | | extended | | |
password | text | | not null | | extended | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_username_key" UNIQUE CONSTRAINT, btree (username)
Access method: heap
-- 插入用户时，对密码进行哈希存储
hgdb=# insert into users (username, password) values ('tony', encode(digest('123456', 'sha256'), 'hex'));
hgdb=# insert into users (username, password) values ('anne', encode(digest('654321', 'sha256'), 'hex'));
hgdb=# select * from users;
id | username | password
----+----------+------------------------------------------------------------------
1 | tony | 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
2 | anne | 481f6cc0511143ccdd7e2d1b1b94faf0a700a8b49cd13922a70b5ae28acaa8c5
-- 登录验证时，对输入的密码进行相同哈希运算后比对
hgdb=# select * from users where password = encode(digest('123456', 'sha256'), 'hex');
id | username | password
----+----------+------------------------------------------------------------------
1 | tony | 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
PostgreSQL 也内置了 md5(), sha224(), sha256(), sha384(), sha512() 等函数，功能与 digest() 类似。
hgdb=# \df sha*
List of functions
Schema | Name |Result data type | Argument data types | Type
------------+--------+------------------+---------------------+------
pg_catalog | sha224 | bytea | bytea | func
pg_catalog | sha256 | bytea | bytea | func
pg_catalog | sha384 | bytea | bytea | func
pg_catalog | sha512 | bytea | bytea | func
(4 rows)
hgdb=# select sha256('123456');
sha256
--------------------------------------------------------------------
\x8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
然而，以上方法的共同问题是：相同数据产生的哈希值相同。这意味着如果两个用户密码相同，存储的密文也相同，降低了安全性。解决方法之一是使用 hmac() 函数。
HMAC 哈希函数 (带密钥)
hmac() 函数在计算哈希时需要一个密钥（Key），因此即使原始数据相同，只要密钥不同，生成的哈希值就不同。这极大地增强了防篡改能力。
- 语法：
hmac(data text, key text, type text) returns bytea
- 特点：必须有密钥才能生成或验证相同的哈希值，安全性更高。
-- 查看函数定义
hgdb=# \df hmac
List of functions
Schema | Name |Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
public | hmac | bytea | bytea, bytea, text | func
public | hmac | bytea | text, text, text | func
-- 使用示例
hgdb=>SELECT hmac('199****1885', 'key', 'sha256');
hmac
--------------------------------------------------------------------
\xa3b6ff6e1315494ad058e3ce39d7a0b021ed97c0dfe1a2e55b302c9364cd7e72
使用场景示例：用用户名作为密钥加密密码
-- 使用 hmac() 函数，并以用户名作为密钥更新用户密码
hgdb=# update users set password = encode(hmac('123456', username, 'sha256'), 'hex') where username ='tony';
hgdb=# update users set password = encode(hmac('654321', username, 'sha256'), 'hex') where username ='anne';
-- 此时，相同的密码（123456）因密钥（tony vs anne）不同，密文也不同
hgdb=# select * from users;
id | username | password
----+----------+------------------------------------------------------------------
1 | tony | 72677f591c16c1dbeb1e9ae2da5c43e6e2e9a6130b08b6ee6fdfb6949638903e
2 | anne | a73108669e778fdd84533af9685b9b3b15ffccfbb29ac66e9d66f1608000bb94
-- 登录验证时，需使用相同的密钥（用户名）进行验证
hgdb=# select * from users where password = encode(hmac('123456', username, 'sha256'), 'hex');
id | username | password
----+----------+------------------------------------------------------------------
1 | tony | 72677f591c16c1dbeb1e9ae2da5c43e6e2e9a6130b08b6ee6fdfb6949638903e
密码哈希函数 (加盐)
这是存储用户密码的最佳实践。pgcrypto 提供了专门的 crypt() 和 gen_salt() 函数。
crypt(password, salt): 对密码进行加盐哈希。gen_salt(type): 生成随机盐值，并决定加密算法。- 核心优势：
- 加盐：相同密码每次加密结果不同，有效抵御彩虹表攻击。
- 慢哈希：算法经过特意设计，计算较慢，增加暴力破解成本。
- 自适应性：部分算法（如bf）可调整计算强度以对抗硬件进步。
支持的算法
| 算法 |
密码最大长度 |
自适应性 |
盐值比特位数 |
输出结果长度 |
描述 |
| bf |
72 |
是 |
128 |
60 |
基于 Blowfish 的 2a 变种算法，生产首选，安全性最高 |
| md5 |
无限 |
否 |
48 |
34 |
基于 MD5，安全性一般，兼容旧系统 |
| xdes |
8 |
是 |
24 |
20 |
扩展DES，老式算法，不推荐 |
| des |
8 |
否 |
12 |
13 |
原始 UNIX 加密算法，不推荐 |
-- 函数定义
hgdb=# \df crypt
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-------+------------------+---------------------+------
public | crypt | text | text, text | func
hgdb=# \df gen_salt
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+----------+------------------+---------------------+------
public | gen_salt | text | text | func
public | gen_salt | text | text, integer | func
使用示例
-- 生成不同算法的盐值
hgdb=# select gen_salt('des') as des, gen_salt('xdes') as xdes, gen_salt('md5') as md5, gen_salt('bf') as bf6, gen_salt('bf', 8) as bf8;
des | xdes | md5 | bf6 | bf8
-----+-----------+-------------+-------------------------------+-------------------------------
i/| _J9..myEz | $1$MnIx4Cv. | $2a$06$mtBh2r6cD20Xm5sSMYKEAO | $2a$08$kYf0wOHRfhngCN22kEPwHe
(1 row)
-- bf算法结果中的 `$2a$06$`，`2a` 表示 Blowfish 的 2a 变种算法，`06` 表示迭代的次数。
-- 使用 crypt() 加密密码
hgdb=# SELECT crypt('199****1885', gen_salt('bf'));
crypt
--------------------------------------------------------------
$2a$06$3WoCD8mIArFuQbcBPIpvy.Bwou7dCYVTOyB5Mmi7wYHmCGiY8sZDi
hgdb=# SELECT crypt('199****1885', gen_salt('bf')); -- 再次执行，结果不同
crypt
--------------------------------------------------------------
$2a$06$Syb3NpQCxVQ9O4Ff.gdSE.T/t.7GTD7ckZKdzTXWwwHUCMVibpX.K
生产场景示例
-- 使用 crypt() 函数安全地存储用户密码
hgdb=# update users set password = crypt('123456', gen_salt('bf')) where username ='tony';
hgdb=# update users set password = crypt('654321', gen_salt('bf')) where username ='anne';
hgdb=# select * from users;
id | username | password
----+----------+--------------------------------------------------------------
1 | tony | $2a$06$hjAC4zuoPA5NZADT2tmE3ehA0PcrxiEinr2OuZU6YFWQzbO7h0td.
2 | anne | $2a$06$K5p64PQPNp2pu1B.cJSDqOivM3YleUr4CIGZWt7xgyynl30IIzYiK
-- 验证密码时，将用户输入的明文与存储的密文（已包含算法和盐值）传入 crypt() 进行比对
-- 注意：不能直接 crypt(明文, gen_salt())，必须使用存储的完整密文作为盐值参数
hgdb=# select * from users where password = crypt('123456', password);
id | username | password
----+----------+--------------------------------------------------------------
1 | tony | $2a$06$hjAC4zuoPA5NZADT2tmE3ehA0PcrxiEinr2OuZU6YFWQzbO7h0td.
双向加密
双向加密是可逆的，适用于需要对密文进行解密查询的场景，例如加密存储手机号、身份证号等敏感信息，在授权情况下可以解密使用。
对称加密/解密函数
最常用的双向加密方式，加密和解密使用同一个密钥。
- 核心用途：对业务敏感数据做加密存储、解密查询。
- 核心特点：一把钥匙开一把锁，加密后可逆，支持多种算法（AES, DES, Blowfish等）。
语法
-- 加密函数
encrypt(data text/bytea, key text, type text) returns bytea
-- 解密函数
decrypt(data bytea, key text, type text) returns text/bytea
-- data: 要加密/解密的原文
-- key: 加密/解密的密钥（必须妥善保管，丢失则数据无法恢复）
-- type: 加密算法，格式为 `算法-模式/填充`
-- 常用组合：`aes-cbc/pad:pkcs`（AES256, CBC模式，PKCS填充，推荐）
示例
-- 查看函数
hgdb=# \df *crypt
List of functions
Schema | Name |Result data type | Argument data types | Type
--------+-----------------+------------------+--------------------------------------------+------
public | decrypt | bytea | bytea, bytea, text | func
public | encrypt | bytea | bytea, bytea, text | func
-- 加密并 Base64 编码
hgdb=# select encode(encrypt(convert_to('199****1885', 'utf8'), 'zylong_key', 'aes-cbc/pad:pkcs'), 'base64');
encode
--------------------------
JMxYTsJpeUgSSC/ot/mePA==
-- 解密（先Base64解码）
hgdb=# select convert_from(decrypt(decode('JMxYTsJpeUgSSC/ot/mePA==', 'base64'), 'zylong_key', 'aes-cbc/pad:pkcs'), 'utf8');
convert_from
--------------
199****1885
-- 加密并 Hex 编码
hgdb=# select encode(encrypt(convert_to('199****1885', 'utf8'), 'zylong_key', 'aes-cbc/pad:pkcs'), 'hex');
encode
----------------------------------
24cc584ec269794812482fe8b7f99e3c
带初始化向量 (IV) 的对称加密
比基础的 encrypt/decrypt 安全性更高，生产更推荐使用。IV 能确保即使相同明文、相同密钥，每次加密产生的密文也不同。
hgdb=# select encode(encrypt_iv(convert_to('199****1885', 'utf8'), 'zylong_key', 'qwer', 'aes-cbc/pad:pkcs'), 'base64');
encode
--------------------------
eumnU5rC4DMrq1Mw4IAHcg==
hgdb=# select convert_from(decrypt_iv(decode('eumnU5rC4DMrq1Mw4IAHcg==', 'base64'), 'zylong_key', 'qwer', 'aes-cbc/pad:pkcs'), 'utf8');
convert_from
--------------
199****1885
(1 row)
其他加密函数
- 基于 PGP 标准的对称加密 (
pgp_sym_encrypt, pgp_sym_decrypt)：支持压缩、签名等更复杂选项，适合加密大文本，但性能开销较大。
- 基于 PGP 标准的非对称加密 (
pgp_pub_encrypt, pgp_pub_decrypt)：使用公钥加密、私钥解密，适合跨平台安全数据传输。
- 硬件安全模块 (HSM) 相关函数 (
hsm_encrypt, hsm_decrypt 等)：用于与外部HSM硬件集成，提供更高等级的安全密钥管理。
加解密函数的性能测试
在实际应用中，除了安全性，加解密操作的性能也是需要考虑的重要因素。下面通过一个包含 52 万行数据的测试表，对比不同算法的加解密速度。
加密和解密的速度测试
测试准备
-- 创建一个测试表，约52万行数据
hgdb=>droptable HIGHGO_PGCRYPTO_BAK;
hgdb=> create table HIGHGO_PGCRYPTO_BAK asselect* from HIGHGO_PGCRYPTO;
SELECT 131003
-- 新增字段用于存储密文和解密后的文本
hgdb=> alter table HIGHGO_PGCRYPTO_BAK addcolumn CALLERNOJM text;
ALTER TABLE
hgdb=> alter table HIGHGO_PGCRYPTO_BAK addcolumn CALLERNOJW text;
ALTER TABLE
hgdb=> \timing
Timing is on.
-- 扩增数据量至约52万行
...
hgdb=>selectcount(*) from HIGHGO_PGCRYPTO_BAK;
count
--------
524012
性能对比结果 (52万行数据更新耗时)
| 操作 (算法) |
加密耗时 |
解密耗时 |
备注 |
aes-cbc/pad:pkcs (Base64) |
~2.4 秒 |
~2.5 秒 |
推荐，安全与性能平衡 |
aes (简写，Base64) |
~2.4 秒 |
~2.5 秒 |
与上一条几乎相同 |
bf-cbc/pad:pkcs (Base64) |
~23.8 秒 |
~23.5 秒 |
Blowfish 算法明显更慢 |
aes-cbc/pad:pkcs (Hex) |
~2.6 秒 |
~2.65 秒 |
Hex编码略慢于Base64 |
encrypt_iv (带IV, Base64) |
~2.7 秒 |
~2.8 秒 |
推荐的高安全选项，性能损失很小 |
pgp_sym_encrypt (PGP标准) |
~213 秒 |
~213 秒 |
PGP函数性能开销巨大，不适合大批量数据 |
结论：
- 对于大批量数据的字段级加密，
AES (encrypt/encrypt_iv) 是性能最佳选择。
Blowfish (bf) 算法由于其设计特点，计算速度显著慢于 AES。PGP 系列函数功能强大但极其耗时，仅适用于小数据量或非频繁操作场景。- 为提升安全性，强烈建议使用带初始化向量 (IV) 的
encrypt_iv/decrypt_iv 函数，其带来的性能损耗微乎其微。
加密字段的查询性能
数据加密后，如何高效地查询是一个挑战。因为密文本身是随机的，无法直接创建有效索引。解决方案是创建一个基于解密函数的表达式索引。
1. 创建可索引的解密函数
首先，需要创建一个 IMMUTABLE 函数来包裹解密逻辑，这样 PostgreSQL 才能基于它创建索引。
CREATE OR REPLACE FUNCTION my_decrypt(ciphertext_encode text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
RETURN convert_from(decrypt(decode(ciphertext_encode, 'base64'), 'zylong_key', 'aes-cbc/pad:pkcs'), 'utf8');
EXCEPTION
WHEN others THEN
RETURN ciphertext_encode; -- 解密失败返回 原文
END;
$function$ IMMUTABLE
;
2. 在加密字段上创建表达式索引
hgdb=> create index HIGHGO_PGCRYPTO_BAK_CALLERNOJM on HIGHGO_PGCRYPTO_BAK (my_decrypt(CALLERNOJM));
CREATE INDEX
3. 查询性能对比
- 无索引全表扫描：查询耗时约 1821 ms。
- 使用表达式索引：查询耗时约 0.186 ms。
-- 使用表达式索引查询（快速）
hgdb=> explain analyze selectcount(*) from HIGHGO_PGCRYPTO_bak where my_decrypt(CALLERNOJM) ='13800008888';
... (执行计划显示使用了索引)
Execution Time: 0.186 ms
-- 强制全表扫描（缓慢）
hgdb=> /*+ SeqScan(HIGHGO_PGCRYPTO_bak) */explain analyze selectcount(*) from HIGHGO_PGCRYPTO_bak where my_decrypt(CALLERNOJM) ='13800008888';
... (执行计划显示全表扫描)
Execution Time: 1821.681 ms
4. 加密字段的关联查询
当加密表需要与非加密表关联时，表达式索引同样能发挥巨大作用。
-- 高效的关联查询（使用了加密字段上的表达式索引）
hgdb=> explain analyze
SELECT b.flowid,b.CALLERNOJM
FROM HIGHGO_PGCRYPTO c LEFTJOIN HIGHGO_PGCRYPTO_BAK b ON my_decrypt(b.CALLERNOJM) = c.CALLERNO
WHERE c.CALLERNO ='13800008888';
Execution Time: 3.065 ms -- 性能优异
关键点：为加密列创建基于解密函数的表达式索引，是将加密数据投入生产查询的必备步骤，否则任何条件查询都会导致全表解密，性能不可接受。
替换 Oracle 的 DBMS_CRYPTO
对于从 Oracle 迁移到 PostgreSQL 的项目，一个常见需求是替代 Oracle 中的 DBMS_CRYPTO 包。pgcrypto 提供了对等的加密能力。
Oracle 中的加密示例
CREATE OR REPLACE FUNCTION ENCRYPT_REAL(INPUT_STRING VARCHAR2) RETURN VARCHAR2 IS
ENCRYPTED_RAW RAW(2000);
KEY_BYTES_RAW RAW(16);
IV_BYTES_RAW RAW(16);
ENCRYPTION_TYPE PLS_INTEGER:=DBMS_CRYPTO.ENCRYPT_AES128+DBMS_CRYPTO.CHAIN_CBC+DBMS_CRYPTO.PAD_PKCS5;
BEGIN
SELECT KEYCODE INTO KEY_BYTES_RAW FROM KEYTABLE;
SELECT IV INTO IV_BYTES_RAW FROM KEYTABLE;
ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT
(
SRC => UTL_I18N.STRING_TO_RAW (INPUT_STRING,'AL32UTF8'),
TYP => ENCRYPTION_TYPE,
KEY => KEY_BYTES_RAW,
IV => IV_BYTES_RAW
);
RETURN utl_raw.cast_to_varchar2(utl_encode.base64_encode(ENCRYPTED_RAW));
END;
/
SQL>select IRMS_OM.ENCRYPT_REAL('199****1885') from dual;
IRMS_OM.ENCRYPT_REAL('199****1885')
--------------------------------------------------------------------------------
3zagMvYUEjBjLQNluT/FJA==
重要迁移提示
Oracle 加密后的数据无法直接在 PostgreSQL 中用 pgcrypto 解密，因为两者在密钥处理、填充方式等底层实现上可能存在差异。
正确的迁移步骤：
- 在 Oracle 端，使用原有的密钥和函数对已加密的数据进行解密，得到明文。
- 将解密后的明文数据迁移到 PostgreSQL 数据库中。
- 在 PostgreSQL 端，使用
pgcrypto 的 encrypt_iv 或 encrypt 函数，以及新的、独立管理的密钥，对明文数据进行重新加密。
试图用 pgcrypto 直接解密 Oracle 的密文通常会失败：
hgdb=> select convert_from(decrypt_iv(decode('3zagMvYUEjBjLQNluT/FJA==', 'base64'), 'xxx', 'xxx', 'aes-cbc/pad:pkcs'), 'utf8');
ERROR: invalid byte sequence for encoding "UTF8": 0xf70xb50x900x48
综上所述，PostgreSQL 的 pgcrypto 扩展是一个功能强大且完备的数据库安全工具包。它不仅涵盖了从密码存储（单向加盐哈希）到敏感数据加密（双向对称加密）的各种场景，还提供了性能优异的算法实现（如AES）。通过创建表达式索引，可以解决加密数据查询的性能瓶颈。对于从 Oracle 迁移而来的加密需求，pgcrypto 是替代 DBMS_CRYPTO 的绝佳选择，但需要注意在迁移过程中进行数据的解密和再加密。在云栈社区的实践中，合理运用 pgcrypto 能极大增强应用的数据安全水位。
发表于 1 小时前
|
查看: 3|
回复: 0
