PostgreSQL 静态数据加密

透明数据加密（TDE）

透明数据加密（Transparent Data Encryption，TDE）是指在数据写入磁盘时自动加密，读取时自动解密，对应用程序完全透明。

1. pgcrypto 扩展安装

sql

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

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

2. 表空间加密

PostgreSQL 14+ 支持表空间加密，以下是配置方法：

bash

# 1. 创建加密表空间目录
mkdir -p /pgdata/encrypted_tablespace
chown postgres:postgres /pgdata/encrypted_tablespace
chmod 700 /pgdata/encrypted_tablespace

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

sql

-- 3. 创建加密表空间
CREATE TABLESPACE encrypted_tbs 
  LOCATION '/pgdata/encrypted_tablespace' 
  ENCRYPTION 'AES256' 
  WITH KEY ID 'file:/pgdata/encrypt.key';

-- 4. 在加密表空间创建表
CREATE TABLE sensitive_data (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  credit_card VARCHAR(16),
  salary NUMERIC(10,2)
) TABLESPACE encrypted_tbs;

-- 5. 验证表空间加密状态
SELECT spcname, spcoptions 
FROM pg_tablespace 
WHERE spcname = 'encrypted_tbs';

列级加密

列级加密允许对表中的特定敏感列进行加密，提供更细粒度的安全控制。

1. 使用 pgcrypto 进行列加密

sql

-- 创建带加密列的表
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  -- 使用 pgcrypto 加密密码列
  password TEXT NOT NULL,
  -- 使用 pgcrypto 加密邮箱列
  email TEXT,
  -- 使用 pgcrypto 加密手机号列
  phone TEXT
);

-- 插入加密数据
INSERT INTO users (username, password, email, phone) 
VALUES (
  'john_doe',
  crypt('secret_password', gen_salt('bf')), -- 使用 blowfish 算法加密密码
  pgp_sym_encrypt('john@example.com', 'encryption_key'), -- 对称加密邮箱
  pgp_sym_encrypt('13800138000', 'encryption_key') -- 对称加密手机号
);

-- 查询解密数据
SELECT 
  id, 
  username,
  -- 验证密码（不需要解密）
  password = crypt('secret_password', password) AS password_correct,
  -- 解密邮箱
  pgp_sym_decrypt(email::bytea, 'encryption_key') AS email,
  -- 解密手机号
  pgp_sym_decrypt(phone::bytea, 'encryption_key') AS phone
FROM users 
WHERE username = 'john_doe';

2. 使用内置函数加密

PostgreSQL 12+ 提供了内置的哈希函数：

sql

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

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

-- 使用内置的密码哈希函数
SELECT gen_random_uuid(); -- 生成随机 UUID
SELECT gen_salt('bf'); -- 生成 blowfish 盐值
SELECT gen_salt('md5'); -- 生成 MD5 盐值

加密数据类型

1. 密码类型

sql

-- 创建带密码列的表
CREATE TABLE user_credentials (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password_hash TEXT NOT NULL
);

-- 插入密码数据
INSERT INTO user_credentials (username, password_hash) 
VALUES (
  'alice',
  crypt('alice_password', gen_salt('bf', 12)) -- 使用 blowfish 算法，成本因子 12
);

-- 验证密码
SELECT * FROM user_credentials 
WHERE username = 'alice' 
AND password_hash = crypt('alice_password', password_hash);

2. 敏感数据类型

sql

-- 创建带多种加密列的表
CREATE TABLE customer_data (
  id SERIAL PRIMARY KEY,
  customer_id UUID DEFAULT gen_random_uuid(),
  full_name TEXT,
  ssn TEXT, -- 社会安全号
  bank_account TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入加密数据
INSERT INTO customer_data (full_name, ssn, bank_account) 
VALUES (
  pgp_sym_encrypt('张三', 'master_key'),
  pgp_sym_encrypt('123-45-6789', 'master_key'),
  pgp_sym_encrypt('622202********1234', 'master_key')
);

加密密钥管理

1. 密钥存储

bash

# 1. 使用文件系统存储密钥
mkdir -p /etc/postgresql/keys
chown postgres:postgres /etc/postgresql/keys
chmod 700 /etc/postgresql/keys

# 2. 生成主密钥
openssl rand -base64 32 > /etc/postgresql/keys/master.key
chown postgres:postgres /etc/postgresql/keys/master.key
chmod 600 /etc/postgresql/keys/master.key

# 3. 生成数据加密密钥（DEK）
openssl rand -base64 32 > /etc/postgresql/keys/data.key
chown postgres:postgres /etc/postgresql/keys/data.key
chmod 600 /etc/postgresql/keys/data.key

2. 使用密钥管理服务

sql

-- 使用 AWS KMS 集成示例（需要安装 aws_encryption_sdk 扩展）
-- 注意：此扩展需要额外安装

-- 创建 KMS 加密密钥
-- aws kms create-key --description "PostgreSQL Encryption Key"

-- 使用 KMS 加密数据
-- SELECT aws_encrypt('sensitive_data', 'arn:aws:kms:region:account-id:key/key-id');

加密性能优化

1. 选择合适的加密算法

sql

-- 比较不同加密算法的性能
EXPLAIN ANALYZE 
SELECT pgp_sym_encrypt('test_data', 'key') FROM generate_series(1, 1000);

EXPLAIN ANALYZE 
SELECT crypt('test_password', gen_salt('bf')) FROM generate_series(1, 1000);

EXPLAIN ANALYZE 
SELECT sha256('test_data') FROM generate_series(1, 1000);

2. 优化加密查询

sql

-- 不推荐：在 WHERE 子句中使用解密函数
SELECT * FROM users 
WHERE pgp_sym_decrypt(email::bytea, 'key') = 'john@example.com';

-- 推荐：添加索引列或使用哈希索引
ALTER TABLE users ADD COLUMN email_hash TEXT;
UPDATE users 
SET email_hash = md5(pgp_sym_decrypt(email::bytea, 'key'));

-- 创建索引
CREATE INDEX idx_users_email_hash ON users(email_hash);

-- 使用哈希索引查询
SELECT * FROM users 
WHERE email_hash = md5('john@example.com');

静态数据加密最佳实践

1. 加密策略设计

数据分类：根据数据敏感度分类，仅加密敏感数据
加密级别：
- 高敏感数据：使用列级加密 + 强算法
- 中敏感数据：使用表空间加密
- 低敏感数据：使用透明数据加密

2. 密钥管理

密钥分离：将加密密钥与数据存储在不同位置
定期轮换：定期轮换加密密钥
密钥备份：安全备份加密密钥，防止密钥丢失
访问控制：严格控制密钥访问权限

3. 性能优化

选择合适的加密算法：根据性能需求选择加密算法
避免在查询中解密：使用哈希索引优化查询性能
批量操作：减少加密/解密操作次数
硬件加速：使用支持 AES-NI 的硬件

4. 合规性考虑

GDPR：加密个人数据，保护用户隐私
PCI DSS：加密信用卡等支付数据
HIPAA：加密医疗健康数据
SOX：确保财务数据的完整性和保密性

常见问题（FAQ）

Q1：PostgreSQL 支持透明数据加密（TDE）吗？

A1：PostgreSQL 14+ 支持表空间加密，可以实现透明数据加密效果。对于更早版本，可以使用第三方扩展如 pg_tde 或文件系统级加密（如 LUKS、BitLocker）。

Q2：列级加密会影响查询性能吗？

A2：是的，列级加密会增加 CPU 开销，特别是在大量数据查询时。建议：

仅对敏感列加密
使用高效的加密算法
避免在 WHERE 子句中使用解密函数
使用哈希索引优化查询

Q3：如何安全备份加密数据？

A3：

确保备份包含加密数据和必要的密钥文件
备份文件本身也应加密
密钥和备份数据应存储在不同位置
定期测试恢复过程，确保能够正确恢复加密数据

Q4：如何处理加密密钥丢失？

A4：

密钥丢失意味着加密数据无法恢复，因此必须：
- 安全备份密钥
- 实现密钥轮换机制
- 使用密钥管理服务（KMS）
- 建立密钥恢复流程

Q5：表空间加密和列级加密有什么区别？

A5：

表空间加密：对整个表空间的所有数据进行加密，透明性好，性能开销小
列级加密：对表中的特定列进行加密，粒度更细，安全性更高，但性能开销较大
可以结合使用：表空间加密保护所有数据，列级加密保护最敏感的列

Q6：如何选择合适的加密算法？

A6：

密码存储：使用 blowfish（bcrypt）或 argon2
对称加密：使用 AES-256
哈希函数：使用 SHA-256 或 SHA-512
非对称加密：使用 RSA-2048 或 ECC（椭圆曲线加密）

PostgreSQL 静态数据加密 ​

透明数据加密（TDE） ​

1. pgcrypto 扩展安装 ​

2. 表空间加密 ​

列级加密 ​

1. 使用 pgcrypto 进行列加密 ​

2. 使用内置函数加密 ​

加密数据类型 ​

1. 密码类型 ​

2. 敏感数据类型 ​

加密密钥管理 ​

1. 密钥存储 ​

2. 使用密钥管理服务 ​

加密性能优化 ​

1. 选择合适的加密算法 ​

2. 优化加密查询 ​

静态数据加密最佳实践 ​

1. 加密策略设计 ​

2. 密钥管理 ​

3. 性能优化 ​

4. 合规性考虑 ​

常见问题（FAQ） ​

Q1：PostgreSQL 支持透明数据加密（TDE）吗？ ​

Q2：列级加密会影响查询性能吗？ ​

Q3：如何安全备份加密数据？ ​

Q4：如何处理加密密钥丢失？ ​

Q5：表空间加密和列级加密有什么区别？ ​

Q6：如何选择合适的加密算法？ ​