pgcrypto encode decode encrypt decrypt convert_from-实时数仓 Hologres(Hologres)-阿里云帮助中心

概述

从 Hologres V4.0 版本开始，系统支持 PostgreSQL 的 pgcrypto 扩展中的部分函数。这些函数提供数据加密、解密和编码能力，可用于保护敏感数据。使用前需先创建扩展：CREATE EXTENSION IF NOT EXISTS pgcrypto;

使用限制

仅 Hologres V4.0 及以上版本支持本文所述的 pgcrypto 函数。

支持的函数列表

Hologres V4.0 及以上版本支持的 pgcrypto 函数如下：

函数	功能说明
encode(data, format)	将二进制数据按指定格式编码为文本。
decode(str, format)	将指定格式的字符串解码为二进制数据。
encrypt(data, key, algorithm)	使用指定算法和密钥加密数据。
decrypt(data, key, algorithm)	使用指定算法和密钥解密数据。
convert_from(data, encoding)	将二进制数据从指定字符编码转换为内部表示（如 UTF-8）。

函数详细说明

encode(data, format)

描述：将二进制数据（bytea）按指定格式编码为文本字符串。
语法
```
encode(data bytea, format text)
```
参数说明
- data：要编码的二进制数据。
- format：编码格式，支持 hex、base64、escape。
返回值：编码后的文本字符串。

示例

-- 将二进制数据编码为十六进制
SELECT encode('\xDEADBEEF'::bytea, 'hex');
-- 结果: deadbeef

-- 将二进制数据编码为 Base64
SELECT encode('\x12345678'::bytea, 'base64');
-- 结果: EjRWeA==

decode(str, format)

描述：将指定格式的字符串解码为二进制数据（bytea）。
语法
```
decode(str text, format text)
```
参数说明
- str：要解码的字符串。
- format：解码格式，支持 hex、base64、escape。
返回值：解码后的二进制数据（bytea）。

示例

-- 将十六进制字符串解码为二进制
SELECT decode('deadbeef', 'hex');
-- 结果: \xdeadbeef

-- 将 Base64 字符串解码为二进制
SELECT decode('EjRWeA==', 'base64');
-- 结果: \x12345678

encrypt(data, key, algorithm)

描述：使用指定的密钥和算法对数据进行加密。

语法

encrypt(data bytea, key bytea, algorithm text)

参数说明
- data：要加密的原始数据。
- key：加密密钥。
- algorithm：加密算法，如 aes、aes-ecb/pad:pkcs 等。
返回值：加密后的二进制数据（bytea）。

示例

-- 使用 AES 算法加密并输出为 Base64
SELECT encode(encrypt('hello world'::bytea, 'mysecretpassword'::bytea, 'aes-ecb/pad:pkcs'), 'base64');
-- 结果: 加密后的 Base64 字符串（每次运行可能不同，因含随机因子）

decrypt(data, key, algorithm)

描述：使用指定的密钥和算法对加密数据进行解密。密钥与算法需与加密时一致。

语法

decrypt(data bytea, key bytea, algorithm text)

参数说明
- data：要解密的数据。
- key：解密密钥（与加密时相同）。
- algorithm：解密算法（与加密时相同）。
返回值：解密后的二进制数据（bytea）。常与 convert_from 配合得到明文文本。

示例

-- 解密 Base64 密文并转为 UTF-8 文本（将 <加密后的Base64字符串> 替换为实际密文）
SELECT convert_from(
    decrypt(
        decode('<加密后的Base64字符串>', 'base64'),
        'mysecretpassword'::bytea,
        'aes-ecb/pad:pkcs'
    ),
    'utf8'
);
-- 结果: 解密后的明文文本

convert_from(data, encoding)

描述：将二进制数据从指定的字符编码转换为内部表示（如 UTF-8）。
语法
```
convert_from(data bytea, encoding name)
```
参数说明
- data：包含特定编码的二进制数据。
- encoding：源数据的字符编码，如 utf8、latin1、gbk 等。
返回值：转换后的文本字符串。

示例

-- 将 GBK 编码的二进制转换为文本
SELECT convert_from('\xb0\xc5\xba\xcd'::bytea, 'gbk');
-- 结果: 中文

实际应用示例

以下示例展示如何使用 pgcrypto 函数对敏感字段（如身份证号）进行加密存储与解密查询：

-- 启用 pgcrypto 扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 创建用户信息表（含敏感数据）
CREATE TABLE mf_user_info (
    id bigint,
    name text,
    gender text,
    id_card_no text,
    tel text
);

INSERT INTO mf_user_info VALUES
(1, 'bob', 'male', '0001', '13900001234'),
(2, 'allen', 'male', '0011', '13900001111'),
(3, 'kate', 'female', '0111', '13900002222'),
(4, 'annie', 'female', '1111', '13900003333');

-- 创建加密存储表
CREATE TABLE mf_user_info_encrypt (
    id bigint,
    name text,
    gender text,
    id_card_no_encrypt text,
    tel text
);

-- 将敏感数据加密后写入
INSERT INTO mf_user_info_encrypt
SELECT
    id, name, gender,
    encode(encrypt(id_card_no::text::bytea, 'myencryptionkey'::bytea, 'aes-ecb/pad:pkcs'), 'base64') AS id_card_no_encrypt,
    tel
FROM mf_user_info;

-- 解密并查询身份证号
SELECT id, name, gender,
    convert_from(decrypt(decode(id_card_no_encrypt, 'base64'), 'myencryptionkey'::bytea, 'aes-ecb/pad:pkcs'), 'utf8') AS id_card_no,
    tel
FROM mf_user_info_encrypt;

注意事项

安全性：选用足够强度的加密算法和密钥长度，确保数据安全。
性能：加解密会消耗额外计算资源，请在性能与安全性之间权衡。
密钥管理：妥善管理加密密钥，避免在代码中硬编码。
编码：处理多字节字符时，注意字符编码一致性（如加密前转为 bytea 与解密后用 convert_from 指定编码）。

参考资料

更多 pgcrypto 说明请参见 PostgreSQL 官方文档：PostgreSQL pgcrypto Documentation。

兼容性说明

Hologres 的 pgcrypto 实现与 PostgreSQL 兼容，但当前仅支持本文列出的上述函数，并非 PostgreSQL pgcrypto 扩展中的全部函数。