加密和解密（PGCRYPTO）擴充函數

概述

從 Hologres V4.0 版本開始，系統支援 PostgreSQL 的 pgcrypto 擴充中的部分函數。這些函數提供資料加密、解密和編碼能力，可用於保護敏感性資料。使用前需先建立擴充：CREATE EXTENSION IF NOT EXISTS pgcrypto;

使用限制

僅Hologres V4.0及以上版本支援本文所述的pgcrypto函數。
pgcrypto函數僅支援在PQE引擎中執行，不支援與僅在HQE引擎中執行的函數（如Remote UDF）在同一SQL中混用。混用會導致執行引擎衝突，報錯資訊如下。
```
ERROR: ORCA failed to produce a plan : No plan has been computed for required properties
```
如需同時使用pgcrypto函數和Remote UDF，請將操作拆分為多個獨立的SQL語句分別執行。

支援的函數列表

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 擴充中的全部函數。