PostgreSQL 加密扩展使用

pgcrypto 扩展

pgcrypto 是 PostgreSQL 最常用的加密扩展，提供了多种加密算法和函数。

1. 安装与配置

-- 安装 pgcrypto 扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 验证扩展安装
SELECT extname, extversion, extrelocatable 
FROM pg_extension 
WHERE extname = 'pgcrypto';

2. 哈希函数

-- MD5 哈希
SELECT md5('secret_data');

-- SHA-1 哈希
SELECT sha1('secret_data');

-- SHA-256 哈希
SELECT sha256('secret_data');

-- SHA-512 哈希
SELECT sha512('secret_data');

-- HMAC 哈希（带密钥）
SELECT hmac('secret_data', 'key', 'sha256');

3. 对称加密

-- 使用 AES-256 加密数据
SELECT pgp_sym_encrypt('敏感数据', '加密密钥', 'cipher-algo=aes256');

-- 解密数据
SELECT pgp_sym_decrypt(
  pgp_sym_encrypt('敏感数据', '加密密钥', 'cipher-algo=aes256')::bytea, 
  '加密密钥'
);

-- 加密示例（带配置选项）
SELECT pgp_sym_encrypt(
  '信用卡号: 622202********1234', 
  'master_key', 
  'compress-algo=1, cipher-algo=aes256'
);

4. 非对称加密

-- 生成 RSA 密钥对
SELECT 
  dearmor(pgp_pub_encrypt('test', pgp_pub_key)) AS encrypted_data,
  pgp_pub_key, 
  pgp_priv_key
FROM (
  SELECT 
    pgp_generate_key_pair('rsa', 2048) AS (pgp_pub_key, pgp_priv_key)
) AS keys;

-- 非对称加密和解密示例
WITH keys AS (
  SELECT 
    pgp_generate_key_pair('rsa', 2048) AS (pgp_pub_key, pgp_priv_key)
),
encrypted AS (
  SELECT 
    pgp_pub_key, 
    pgp_priv_key,
    pgp_pub_encrypt('机密文档内容', pgp_pub_key) AS encrypted_data
  FROM keys
)
SELECT 
  encrypted_data,
  pgp_pub_decrypt(encrypted_data, pgp_priv_key) AS decrypted_data
FROM encrypted;

5. 密码哈希

-- 使用 Blowfish 算法加密密码
SELECT crypt('user_password', gen_salt('bf'));

-- 使用 MD5 算法加密密码（不推荐）
SELECT crypt('user_password', gen_salt('md5'));

-- 验证密码
SELECT 
  crypt('user_password', '$2a$06$QJ4h2j1e9u8y7i6o5p4a3s2d1f0g9h8') = 
  '$2a$06$QJ4h2j1e9u8y7i6o5p4a3s2d1f0g9h8i7j6k5l4m3n2o1p'
  AS password_valid;

pg_tde 扩展

pg_tde 是一个第三方透明数据加密扩展，提供了表级和列级的透明加密。

1. 安装与配置

# 编译安装 pg_tde 扩展
git clone https://github.com/ossc-db/pg_tde.git
cd pg_tde
make && make install

# 配置 postgresql.conf
echo "shared_preload_libraries = 'pg_tde'" >> /var/lib/postgresql/15/main/postgresql.conf
echo "pg_tde.key_file = '/pgdata/tde.key'" >> /var/lib/postgresql/15/main/postgresql.conf

# 生成加密密钥
openssl rand -hex 32 > /pgdata/tde.key
chown postgres:postgres /pgdata/tde.key
chmod 600 /pgdata/tde.key

# 重启 PostgreSQL
sudo systemctl restart postgresql

-- 创建 pg_tde 扩展
CREATE EXTENSION IF NOT EXISTS pg_tde;

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

2. 表级加密

-- 创建加密表
CREATE TABLE encrypted_table (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  sensitive_data TEXT
) WITH (ENCRYPTION = 'ON');

-- 插入数据（自动加密）
INSERT INTO encrypted_table (name, sensitive_data) 
VALUES ('测试用户', '机密信息');

-- 查询数据（自动解密）
SELECT * FROM encrypted_table;

3. 列级加密

-- 创建带加密列的表
CREATE TABLE column_encrypted_table (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  -- 指定特定列加密
  sensitive_data TEXT ENCRYPTED,
  regular_data TEXT
);

-- 插入数据
INSERT INTO column_encrypted_table (name, sensitive_data, regular_data) 
VALUES ('测试用户', '列加密数据', '普通数据');

-- 查询数据
SELECT * FROM column_encrypted_table;

pgsodium 扩展

pgsodium 是基于libsodium库的加密扩展，提供了现代化的加密算法。

1. 安装与配置

-- 安装 pgsodium 扩展
CREATE EXTENSION IF NOT EXISTS pgsodium;

-- 验证扩展安装
SELECT extname, extversion FROM pg_extension WHERE extname = 'pgsodium';

2. 密钥管理

-- 生成数据加密密钥
SELECT pgsodium.create_key() AS key_id;

-- 列出所有密钥
SELECT * FROM pgsodium.key;

-- 创建命名密钥
SELECT pgsodium.create_key('my_app_key') AS key_id;

3. 加密使用

-- 加密数据
SELECT pgsodium.encrypt(
  '敏感数据'::bytea, 
  pgsodium.get_key('my_app_key')
) AS encrypted_data;

-- 解密数据
SELECT pgsodium.decrypt(
  pgsodium.encrypt('敏感数据'::bytea, pgsodium.get_key('my_app_key')),
  pgsodium.get_key('my_app_key')
)::text AS decrypted_data;

加密扩展最佳实践

1. 选择合适的扩展

扩展名称	特点	适用场景
pgcrypto	内置扩展，功能全面	大多数加密场景，包括哈希、对称加密、非对称加密
pg_tde	透明数据加密	需要透明加密的场景，对应用透明
pgsodium	现代化加密算法	需要使用现代加密算法的场景

2. 密钥管理

• 密钥分离：将加密密钥与数据存储在不同位置
• 定期轮换：定期轮换加密密钥
• 安全备份：安全备份加密密钥
• 最小权限：限制密钥访问权限

3. 性能优化

-- 优化 pgcrypto 性能
-- 1. 使用合适的加密算法
SELECT pgp_sym_encrypt('数据', '密钥', 'cipher-algo=aes256, compress-algo=0');

-- 2. 避免在查询中频繁加密解密
-- 3. 对加密列使用表达式索引
CREATE INDEX idx_encrypted_col ON users 
USING btree (pgp_sym_decrypt(email::bytea, '密钥'));

4. 安全配置

-- 限制 pgcrypto 函数访问
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgp_sym_encrypt(text, text) TO app_user;
GRANT EXECUTE ON FUNCTION pgp_sym_decrypt(bytea, text) TO app_user;

常见问题（FAQ）

Q1：pgcrypto 扩展是否安全？

A1：pgcrypto 扩展提供了安全的加密算法，但安全性取决于正确的使用方式：

• 使用强密钥
• 定期轮换密钥
• 保护密钥安全
• 选择合适的加密算法

Q2：加密扩展会影响性能吗？

A2：是的，加密扩展会带来一定的性能开销，特别是在大量数据处理时。建议：

• 仅对敏感数据加密
• 使用高效的加密算法
• 避免在查询中频繁加密解密
• 优化查询，使用合适的索引

Q3：如何备份加密数据？

A3：

• 备份加密数据本身
• 备份加密密钥
• 确保密钥与数据分开存储
• 定期测试恢复过程

Q4：如何迁移加密数据？

A4：

• 确保目标数据库安装了相同的加密扩展
• 迁移加密数据
• 迁移加密密钥
• 验证数据可以正常解密

Q5：如何选择加密算法？

A5：

• 密码存储：使用 bcrypt 或 argon2
• 对称加密：使用 AES-256
• 哈希函数：使用 SHA-256 或 SHA-512
• 非对称加密：使用 RSA-2048 或 ECC

Q6：如何监控加密扩展的使用？

A6：

• 监控加密函数的调用频率：使用 pg_stat_statements
• 监控密钥的使用情况：定期审计密钥访问
• 监控加密数据的大小：确保存储规划合理