印刻数据库

外观

PostgreSQL pgcrypto加密扩展 ​

pgcrypto安装与配置 ​

安装pgcrypto扩展 ​

pgcrypto扩展在大多数PostgreSQL安装中已经包含，可以通过简单的SQL命令启用：

sql
-- 检查扩展是否已安装
SELECT * FROM pg_extension WHERE extname = 'pgcrypto';

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

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

在某些系统上，可能需要单独安装包含pgcrypto的软件包：

bash
# Debian/Ubuntu系统
apt-get install postgresql-pgcrypto

# RHEL/CentOS系统
yum install postgresql-pgcrypto

# 重新加载PostgreSQL服务
systemctl reload postgresql

扩展版本与功能 ​

pgcrypto的不同版本支持不同的加密算法和功能：

  • pgcrypto 1.0：基本加密功能，包括对称加密和哈希
  • pgcrypto 1.1：增加了PGP加密功能
  • pgcrypto 1.2：改进了性能和安全性
  • pgcrypto 1.3：支持更多的加密算法
sql
-- 查看pgcrypto版本信息
SELECT pgcrypto_version();

-- 查看支持的加密算法
SELECT * FROM pgcrypto_internal();

哈希函数 ​

常用哈希算法 ​

pgcrypto提供了多种哈希算法，用于生成数据的哈希值。哈希函数是单向的，无法从哈希值还原原始数据，适用于密码存储、数据完整性验证等场景。

sql
-- 使用SHA-256哈希算法
SELECT digest('你好世界', 'sha256') AS sha256_hash;

-- 使用SHA-384哈希算法
SELECT digest('你好世界', 'sha384') AS sha384_hash;

-- 使用SHA-512哈希算法
SELECT digest('你好世界', 'sha512') AS sha512_hash;

-- 使用MD5哈希算法（不推荐用于安全场景）
SELECT md5('你好世界') AS md5_hash;

-- 生成十六进制格式的哈希
SELECT encode(digest('你好世界', 'sha256'), 'hex') AS sha256_hex;

-- 生成Base64格式的哈希
SELECT encode(digest('你好世界', 'sha256'), 'base64') AS sha256_base64;

密码哈希 ​

pgcrypto提供了专门的密码哈希函数，使用适合密码存储的算法：

sql
-- 使用crypt函数进行密码哈希
SELECT crypt('我的密码', gen_salt('bf')) AS password_hash;

-- 验证密码
SELECT crypt('输入的密码', '$2a$06$BDsS2AiG5o0A.X.y.z.u.t.i.n.g.s.a.l.t') = 
       crypt('我的密码', '$2a$06$BDsS2AiG5o0A.X.y.z.u.t.i.n.g.s.a.l.t') AS password_valid;

-- 使用默认算法生成盐
SELECT gen_salt('md5') AS md5_salt;
SELECT gen_salt('bf') AS bcrypt_salt;
SELECT gen_salt('xdes') AS xdes_salt;

-- 生成强密码哈希（推荐使用bcrypt）
CREATE OR REPLACE FUNCTION hash_password(password TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN crypt(password, gen_salt('bf', 12));
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 验证密码函数
CREATE OR REPLACE FUNCTION verify_password(
    password TEXT,
    password_hash TEXT
) RETURNS BOOLEAN AS $$
BEGIN
    RETURN crypt(password, password_hash) = password_hash;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

哈希应用场景 ​

哈希函数在实际应用中的典型场景：

sql
-- 存储用户密码（不要存储明文密码）
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username, password_hash)
VALUES ('张三', hash_password('安全密码123'));

-- 验证用户登录
CREATE OR REPLACE FUNCTION login_user(
    p_username VARCHAR(50),
    p_password TEXT
) RETURNS TABLE (user_id INTEGER, username VARCHAR(50)) AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.username
    FROM users u
    WHERE u.username = p_username
    AND u.password_hash = crypt(p_password, u.password_hash);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 数据完整性验证
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    content_hash TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO documents (content, content_hash)
VALUES ('重要文档内容', encode(digest('重要文档内容', 'sha256'), 'hex'));

-- 验证数据未被篡改
CREATE OR REPLACE FUNCTION verify_document(doc_id INTEGER)
RETURNS BOOLEAN AS $$
DECLARE
    v_content TEXT;
    v_stored_hash TEXT;
    v_current_hash TEXT;
BEGIN
    SELECT content, content_hash INTO v_content, v_stored_hash
    FROM documents WHERE id = doc_id;
    
    IF v_content IS NULL THEN
        RETURN FALSE;
    END IF;
    
    v_current_hash := encode(digest(v_content, 'sha256'), 'hex');
    RETURN v_stored_hash = v_current_hash;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

对称加密 ​

加密与解密函数 ​

pgcrypto的对称加密功能支持多种算法，包括AES、Blowfish等：

sql
-- 使用AES-128加密
SELECT pgp_sym_encrypt('机密消息', '我的密钥') AS encrypted_data;

-- 解密数据
SELECT pgp_sym_decrypt(
    pgp_sym_encrypt('机密消息', '我的密钥'),
    '我的密钥'
) AS decrypted_data;

-- 使用AES-256加密
SELECT pgp_sym_encrypt('机密消息', '我的密钥', 'cipher-algo=AES256') AS encrypted_data;

-- 指定压缩算法
SELECT pgp_sym_encrypt('机密消息', '我的密钥', 'cipher-algo=AES256, compress-algo=zip, compress-level=9') AS encrypted_data;

应用场景 ​

对称加密适用于需要加密存储敏感数据的场景：

sql
-- 创建加密存储表
CREATE TABLE sensitive_data (
    id SERIAL PRIMARY KEY,
    data_type VARCHAR(50) NOT NULL,
    encrypted_content BYTEA NOT NULL,
    encryption_key_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 加密数据函数
CREATE OR REPLACE FUNCTION encrypt_sensitive_data(
    p_data TEXT,
    p_key TEXT
) RETURNS BYTEA AS $$
BEGIN
    RETURN pgp_sym_encrypt(p_data, p_key);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 解密数据函数
CREATE OR REPLACE FUNCTION decrypt_sensitive_data(
    p_encrypted BYTEA,
    p_key TEXT
) RETURNS TEXT AS $$
BEGIN
    RETURN pgp_sym_decrypt(p_encrypted, p_key);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 存储加密数据
INSERT INTO sensitive_data (data_type, encrypted_content, encryption_key_id)
VALUES (
    '信用卡',
    encrypt_sensitive_data('4532-1234-5678-9012', '加密密钥2024'),
    1
);

-- 查询解密数据
SELECT 
    data_type,
    decrypt_sensitive_data(encrypted_content, '加密密钥2024') AS decrypted_content,
    created_at
FROM sensitive_data;

非对称加密 ​

密钥生成与管理 ​

pgcrypto支持非对称加密，需要生成公钥和私钥对：

sql
-- 生成RSA密钥对（2048位，推荐用于生产环境）
SELECT pgp_key_pair_gen(
    'rsa',
    2048,
    '我的密钥标识'
) AS key_pair;

-- 查看生成的公钥
SELECT pgp_pub_decrypt(
    pgp_pub_encrypt('测试', key),
    key
) FROM pgp_public_key WHERE key_id = '我的密钥标识';

非对称加密应用 ​

非对称加密适用于密钥交换、数字签名等场景：

sql
-- 使用公钥加密（私钥解密）
CREATE OR REPLACE FUNCTION encrypt_with_public_key(
    p_data TEXT,
    p_public_key BYTEA
) RETURNS BYTEA AS $$
BEGIN
    RETURN pgp_pub_encrypt(p_data, p_public_key);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION decrypt_with_private_key(
    p_encrypted BYTEA,
    p_private_key BYTEA,
    p_passphrase TEXT
) RETURNS TEXT AS $$
BEGIN
    RETURN pgp_pub_decrypt(p_encrypted, p_private_key, p_passphrase);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 使用私钥签名（公钥验证）
CREATE OR REPLACE FUNCTION create_signature(
    p_data TEXT,
    p_private_key BYTEA,
    p_passphrase TEXT
) RETURNS BYTEA AS $$
BEGIN
    RETURN pgp_sign(p_data, p_private_key, p_passphrase);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION verify_signature(
    p_data TEXT,
    p_signature BYTEA,
    p_public_key BYTEA
) RETURNS BOOLEAN AS $$
BEGIN
    RETURN pgp_verify(p_data, p_signature, p_public_key);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

消息认证码 ​

HMAC函数 ​

消息认证码（MAC）用于验证消息的完整性和真实性：

sql
-- 生成HMAC
SELECT hmac('消息内容', '密钥', 'sha256') AS hmac_signature;

-- 验证HMAC
CREATE OR REPLACE FUNCTION verify_hmac(
    p_message TEXT,
    p_signature TEXT,
    p_secret_key TEXT
) RETURNS BOOLEAN AS $$
BEGIN
    RETURN p_signature = encode(hmac(p_message, p_secret_key, 'sha256'), 'hex');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 使用HMAC保护API请求
CREATE TABLE api_requests (
    id SERIAL PRIMARY KEY,
    request_data TEXT NOT NULL,
    request_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    hmac_signature TEXT NOT NULL
);

CREATE OR REPLACE FUNCTION create_api_request(p_request_data TEXT, p_secret_key TEXT)
RETURNS api_requests AS $$
DECLARE
    v_request api_requests;
BEGIN
    INSERT INTO api_requests (request_data, hmac_signature)
    VALUES (
        p_request_data,
        encode(hmac(p_request_data || NOW()::TEXT, p_secret_key, 'sha256'), 'hex')
    )
    RETURNING * INTO v_request;
    
    RETURN v_request;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

安全最佳实践 ​

密钥管理 ​

密钥管理是加密系统安全的核心：

sql
-- 使用专门的密钥表存储加密密钥
CREATE TABLE encryption_keys (
    id SERIAL PRIMARY KEY,
    key_name VARCHAR(100) UNIQUE NOT NULL,
    encrypted_key BYTEA NOT NULL,
    key_version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

-- 主密钥（应该存储在外部密钥管理系统中）
-- 这里使用两层加密：主密钥加密数据密钥

CREATE OR REPLACE FUNCTION get_current_key_id()
RETURNS INTEGER AS $$
BEGIN
    SELECT id FROM encryption_keys WHERE is_active = TRUE ORDER BY key_version DESC LIMIT 1;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 密钥轮换函数
CREATE OR REPLACE FUNCTION rotate_encryption_key(
    p_old_key_id INTEGER,
    p_new_encrypted_key BYTEA
) RETURNS VOID AS $$
BEGIN
    -- 停用旧密钥
    UPDATE encryption_keys SET is_active = FALSE WHERE id = p_old_key_id;
    
    -- 插入新密钥
    INSERT INTO encryption_keys (key_name, encrypted_key, key_version)
    VALUES ('默认密钥', p_new_encrypted_key, 
            (SELECT COALESCE(MAX(key_version), 0) + 1 FROM encryption_keys));
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

性能考虑 ​

加密操作会影响数据库性能，需要合理规划：

sql
-- 批量加密时使用事务
BEGIN;
INSERT INTO encrypted_records (data)
SELECT encrypt_sensitive_data(data_column, '批量密钥')
FROM large_table;
COMMIT;

-- 创建加密索引需要特殊处理
CREATE INDEX idx_encrypted_data ON encrypted_records 
USING hash (substring(encrypted_data, 1, 8));

-- 考虑使用延迟加密策略
CREATE OR REPLACE FUNCTION lazy_encrypt()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.requires_encryption THEN
        NEW.encrypted_data := encrypt_sensitive_data(NEW.raw_data, current_setting('app.encryption_key'));
        NEW.requires_encryption := FALSE;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

审计与监控 ​

加密操作的审计和监控对于安全至关重要：

sql
-- 创建加密操作审计表
CREATE TABLE encryption_audit (
    id SERIAL PRIMARY KEY,
    operation_type VARCHAR(50) NOT NULL,
    table_name VARCHAR(100),
    record_id BIGINT,
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    operation_by VARCHAR(100)
);

-- 加密操作的审计触发器
CREATE OR REPLACE FUNCTION audit_encryption_operation()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO encryption_audit (operation_type, table_name, record_id, operation_by)
    SELECT 
        TG_OP,
        TG_TABLE_NAME,
        NEW.id,
        current_setting('app.current_user', TRUE)
    WHERE TG_OP IN ('INSERT', 'UPDATE', 'DELETE');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trg_encryption_audit
AFTER INSERT OR UPDATE OR DELETE ON sensitive_data
FOR EACH ROW EXECUTE FUNCTION audit_encryption_operation();

常见问题与解决方案 ​

加密数据索引问题 ​

加密后的数据无法直接建立有意义的索引。解决方案包括：

sql
-- 方案1：存储明文哈希用于查询
ALTER TABLE sensitive_data ADD COLUMN search_hash TEXT;
UPDATE sensitive_data SET search_hash = encode(digest(encrypted_content, 'sha256'), 'hex');
CREATE INDEX idx_search_hash ON sensitive_data(search_hash);

-- 方案2：使用确定性加密（注意安全风险）
-- 仅适用于不需要随机访问的场景

-- 方案3：应用层处理查询
-- 在应用层解密数据后进行查询处理

密钥丢失处理 ​

密钥丢失会导致数据无法恢复。预防措施包括：

  • 实施完善的密钥备份策略
  • 使用密钥管理系统（KMS）
  • 实施密钥轮换策略
  • 记录密钥使用历史
sql
-- 紧急恢复：使用备份密钥解密
CREATE OR REPLACE FUNCTION emergency_decrypt(
    p_encrypted BYTEA,
    p_backup_key TEXT
) RETURNS TEXT AS $$
BEGIN
    RETURN decrypt_with_private_key(p_encrypted, p_backup_key, '');
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

常见问题（FAQ） ​

Q1: pgcrypto与透明数据加密（TDE）有什么区别？ ​

A1: pgcrypto提供应用级别的数据加密，需要在SQL中显式调用加密函数；TDE是数据库引擎级别的加密，对应用透明。pgcrypto更适合保护特定敏感字段，TDE更适合保护整个数据库。

Q2: 加密后的数据可以压缩吗？ ​

A2: 加密后的数据通常无法有效压缩。建议在加密前进行压缩，以节省存储空间：

sql
SELECT pgp_sym_encrypt(
    compress('要加密的长文本内容'),
    '加密密钥'
);

Q3: 如何选择加密算法？ ​

A3: 加密算法选择建议：

  • 对称加密：AES-256（推荐）
  • 非对称加密：RSA-2048或更高（推荐），或ECC
  • 哈希算法：SHA-256或更高（推荐）
  • 密码哈希：bcrypt（推荐）

Q4: 加密会影响数据库性能吗？ ​

A4: 加密会增加CPU开销，影响写入性能。性能影响取决于加密算法、数据量和系统配置。建议：

  • 避免加密不需要保护的数据
  • 使用适当的加密级别
  • 考虑使用硬件加速

Q5: 如何迁移加密数据？ ​

A5: 迁移加密数据时需要注意：

  • 确保迁移过程中密钥可用
  • 使用安全的方式传输加密数据
  • 迁移后验证数据完整性
  • 记录迁移日志

Q6: pgcrypto支持哪些操作系统？ ​

A6: pgcrypto是PostgreSQL的官方扩展，支持所有PostgreSQL支持的操作系统，包括Linux、Windows、macOS等。

Q7: 如何验证加密实现的安全性？ ​

A7: 验证加密实现安全性的方法：

  • 代码审查加密逻辑
  • 使用专业的安全扫描工具
  • 聘请第三方安全审计
  • 遵循安全编码最佳实践