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:pkcsaes-ecb/pad:pkcsbf-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. 安全最佳实践
- 密钥管理：不要将加密密钥存储在数据库中
- 算法选择：优先使用AES-256等现代算法
- 盐值使用：密码哈希必须使用随机盐
- 传输安全：确保应用与数据库的连接加密(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'
),
六、故障排查
常见错误处理
-
解密失败：
-- 检查加密密钥和算法是否匹配 SELECT decrypt(encrypted_data, 'wrongkey', 'aes-cbc'); -- 会报错 -
编码问题：
-- 确保编解码方式一致 SELECT convert_from(decrypt(decode(encode(encrypt_data, 'base64'), 'base64'),'mykey', 'aes-cbc' ), 'utf8'); -
权限问题：
-- 确保角色有权限使用pgcrypto GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;
pgcrypto为PostgreSQL提供了强大的加密能力，合理使用可以显著提升数据安全性，但需要注意密钥管理和性能影响。