PostgreSQL的扩展 pgcrypto

PostgreSQL的扩展 pgcrypto

pgcrypto 是 PostgreSQL 提供的加密扩展，包含各种加密函数、哈希函数和随机数生成器，是数据库级加密的重要工具。

一、安装与启用

-- 安装扩展
CREATE EXTENSION pgcrypto;-- 验证安装
SELECT * FROM pg_extension WHERE extname = 'pgcrypto';

二、核心功能分类

1. 哈希函数

函数	描述	示例
digest()	计算二进制哈希	SELECT digest('data', 'sha256');
hmac()	带密钥的哈希	SELECT hmac('data', 'key', 'sha256');
crypt()	密码哈希(适合存储密码)	SELECT crypt('password', gen_salt('bf', 8));

密码存储最佳实践：

-- 存储密码
INSERT INTO users (username, password) 
VALUES ('user1', crypt('mypassword', gen_salt('bf')));-- 验证密码
SELECT (password = crypt('entered_password', password)) AS auth 
FROM users WHERE username = 'user1';

2. 加密/解密函数

对称加密

-- AES加密(需PG10+)
SELECT encode(encrypt(convert_to('secret data', 'utf8'), 'myencryptionkey', 'aes-cbc/pad:pkcs'
), 'base64');-- 解密
SELECT convert_from(decrypt(decode('...base64...', 'base64'),'myencryptionkey','aes-cbc/pad:pkcs'
), 'utf8');

支持的加密算法

• aes-cbc/pad:pkcs
• aes-ecb/pad:pkcs
• bf-cbc/pad:pkcs (Blowfish)
• des-cbc/pad:pkcs

3. 随机数据生成

函数	描述
gen_random_bytes()	生成加密安全的随机字节
gen_random_uuid()	生成随机UUID(v4)
gen_salt()	生成密码哈希用的随机盐

示例：

-- 生成随机token(32字节)
SELECT encode(gen_random_bytes(32), 'hex');-- 生成UUID
SELECT gen_random_uuid();

三、高级用法

1. 列级加密

-- 创建加密列的表
CREATE TABLE secure_data (id serial PRIMARY KEY,plain_text text,encrypted_text bytea
);-- 插入加密数据
INSERT INTO secure_data (plain_text, encrypted_text)
VALUES ('sensitive info',encrypt(convert_to('sensitive info', 'utf8'), 'mykey123', 'aes-cbc'
);-- 查询解密数据
SELECT id, plain_text,convert_from(decrypt(encrypted_text, 'mykey123', 'aes-cbc'), 'utf8') AS decrypted
FROM secure_data;

2. 密钥管理方案

方案1：应用层提供密钥

-- 使用参数化查询传递密钥
PREPARE decrypt_data(text) AS
SELECT convert_from(decrypt(encrypted_data, $1, 'aes-cbc'), 'utf8')
FROM secure_table;

方案2：使用pgcrypto+环境变量

-- 通过外部配置获取密钥
CREATE OR REPLACE FUNCTION get_encryption_key() RETURNS text AS $$SELECT current_setting('app.encryption_key');
$$ LANGUAGE sql;

四、性能与安全考虑

1. 性能影响

• 加密/解密操作会增加CPU开销
• 加密数据无法使用常规索引
• 建议只加密真正敏感的数据

2. 安全最佳实践

1. 密钥管理：不要将加密密钥存储在数据库中
2. 算法选择：优先使用AES-256等现代算法
3. 盐值使用：密码哈希必须使用随机盐
4. 传输安全：确保应用与数据库的连接加密(SSL)

五、与其他扩展的集成

1. 与pg_partman结合

-- 创建加密的分区表
CREATE TABLE encrypted_logs (log_id bigserial,log_data bytea,created_at timestamptz
) PARTITION BY RANGE (created_at);-- 添加加密分区
CREATE TABLE encrypted_logs_2023 PARTITION OF encrypted_logs
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

2. 与PostGIS结合

-- 存储加密的地理位置数据
CREATE TABLE secure_locations (id uuid PRIMARY KEY DEFAULT gen_random_uuid(),encrypted_coords bytea,public_name text
);-- 插入时加密坐标
INSERT INTO secure_locations (encrypted_coords, public_name)
VALUES (encrypt(convert_to(ST_AsBinary(ST_Point(-71.104, 42.315)), 'utf8'),'mykey123','aes-cbc'
),

六、故障排查

常见错误处理

1. 解密失败：

   -- 检查加密密钥和算法是否匹配
   SELECT decrypt(encrypted_data, 'wrongkey', 'aes-cbc'); -- 会报错
   

2. 编码问题：

   -- 确保编解码方式一致
   SELECT convert_from(decrypt(decode(encode(encrypt_data, 'base64'), 'base64'),'mykey', 'aes-cbc'
   ), 'utf8');
   

3. 权限问题：

   -- 确保角色有权限使用pgcrypto
   GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;
   

pgcrypto为PostgreSQL提供了强大的加密能力，合理使用可以显著提升数据安全性，但需要注意密钥管理和性能影响。