如何在PostgreSQL中实现透明数据加密(TDE)？含pgcrypto方案咨询

为什么表空间加密方案不可行 #

社区版PostgreSQL原生不支持表空间级别的透明数据加密(TDE)，你提到的keystore_location和tablespace_encryption_algorithm是商业PostgreSQL发行版（如EDB Postgres Advanced Server）独有的配置参数，社区版没有相关实现，因此添加后会报错。

用pgcrypto实现类TDE效果的步骤 #

pgcrypto是PostgreSQL官方提供的加密扩展，虽为列级/表级加密，无法做到完全无需修改应用的透明，但可通过视图、触发器等方式尽可能降低应用层改造量，实现近似TDE的效果。

1. 安装pgcrypto扩展 #

先在目标数据库中启用pgcrypto：

CREATE EXTENSION IF NOT EXISTS pgcrypto;

2. 创建加密密钥体系 #

建议采用两级密钥：用**数据加密密钥(DEK)加密业务数据，再用密钥加密密钥(KEK)**保护DEK，避免密钥直接暴露在数据库中。

-- 创建存储加密后DEK的表
CREATE TABLE IF NOT EXISTS encryption_keys (
    key_id SERIAL PRIMARY KEY,
    encrypted_dek BYTEA NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 生成随机DEK，用KEK加密后存入表（替换为你的强KEK密码）
INSERT INTO encryption_keys (encrypted_dek)
VALUES (pgp_sym_encrypt(gen_random_bytes(32), 'your_strong_kek_password', 'cipher-algo=aes256'));

3. 构建加密存储表与访问视图 #

以加密用户敏感字段（如phone、address）为例：

• 创建底层加密存储表：

CREATE TABLE users_encrypted (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    encrypted_phone BYTEA NOT NULL,
    encrypted_address BYTEA NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• 创建供应用直接访问的视图，自动处理解密逻辑：

CREATE OR REPLACE VIEW users AS
SELECT
    user_id,
    username,
    pgp_sym_decrypt(encrypted_phone, 'your_strong_kek_password', 'cipher-algo=aes256') AS phone,
    pgp_sym_decrypt(encrypted_address, 'your_strong_kek_password', 'cipher-algo=aes256') AS address,
    created_at
FROM users_encrypted;

4. 用触发器自动加密写入数据 #

创建触发器函数，让应用对视图的插入/更新操作自动转为加密存储：

CREATE OR REPLACE FUNCTION encrypt_user_data()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO users_encrypted (username, encrypted_phone, encrypted_address)
        VALUES (
            NEW.username,
            pgp_sym_encrypt(NEW.phone, 'your_strong_kek_password', 'cipher-algo=aes256'),
            pgp_sym_encrypt(NEW.address, 'your_strong_kek_password', 'cipher-algo=aes256')
        )
        RETURNING user_id INTO NEW.user_id;
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE users_encrypted
        SET
            username = NEW.username,
            encrypted_phone = pgp_sym_encrypt(NEW.phone, 'your_strong_kek_password', 'cipher-algo=aes256'),
            encrypted_address = pgp_sym_encrypt(NEW.address, 'your_strong_kek_password', 'cipher-algo=aes256')
        WHERE user_id = OLD.user_id;
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 为视图绑定触发器
CREATE TRIGGER trigger_users_encrypt
INSTEAD OF INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION encrypt_user_data();

5. 严格控制访问权限 #

确保应用用户只能访问视图，无法直接接触底层加密表和密钥表：

REVOKE ALL ON users_encrypted FROM public;
REVOKE ALL ON encryption_keys FROM public;
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user; -- 替换为你的应用业务用户

关键注意事项 #

• 密钥管理：KEK绝对不能硬编码在代码或触发器中，建议用外部密钥管理服务（如HashiCorp Vault、云厂商KMS）存储和调用，避免密钥泄露。
• 性能影响：加密解密会产生一定性能开销，需结合业务场景评估后落地。
• 备份安全：备份加密表时，必须同步安全备份密钥，否则无法恢复数据。

内容的提问来源于stack exchange，提问作者sandesh Jadhav