咨询PostgreSQL中使用UPDATE语句实现AES256字段加密的方法

阿华AIGC实验室

2026-5-13

使用PostgreSQL的UPDATE语句实现AES256字段加密

当然可以用UPDATE语句完成这个加密操作！不过在动手之前，我们需要先启用PostgreSQL的加密扩展，因为原生PostgreSQL并没有内置AES256的加密函数。下面是一步步的操作指南，包含示例代码和注意事项：

1. 启用pgcrypto扩展

PostgreSQL的pgcrypto扩展提供了我们需要的AES加密/解密函数，先执行这条语句启用它：

CREATE EXTENSION IF NOT EXISTS pgcrypto;

2. 准备AES256密钥

AES256要求密钥必须是32字节（256位），你可以通过两种方式生成：

生成随机安全密钥（推荐）：

-- 生成一个32字节的随机密钥，输出为64字符的HEX字符串，务必保存好这个密钥！
SELECT encode(gen_random_bytes(32), 'hex') AS aes256_secret_key;

用自定义密码生成32字节密钥：
如果想用自己的密码，可以通过SHA256哈希转换为32字节密钥：
```
SELECT digest('your-custom-passphrase', 'sha256') AS aes256_secret_key;
```

3. 执行UPDATE加密字段

首先强烈建议先备份表，避免加密过程中出现意外导致数据丢失：

-- 备份address表到address_backup
CREATE TABLE address_backup AS SELECT * FROM address;

接下来根据你想要的存储方式选择对应的UPDATE语句：

方式1：加密后存储为BYTEA类型（更节省空间）

如果你的customer_address字段原本是TEXT类型，我们可以直接加密为BYTEA类型：

UPDATE address
SET customer_address = aes_encrypt(
    customer_address::bytea, 
    decode('your-64-char-hex-key', 'hex'), -- 替换为你生成的密钥
    'aes256'
)
WHERE customer_address IS NOT NULL; -- 只加密非空字段

方式2：加密后存储为HEX字符串（TEXT类型）

如果希望继续用TEXT类型存储加密后的数据，可以把加密结果转成HEX字符串：

UPDATE address
SET customer_address = encode(
    aes_encrypt(
        customer_address::bytea, 
        decode('your-64-char-hex-key', 'hex'), -- 替换为你生成的密钥
        'aes256'
    ),
    'hex'
)
WHERE customer_address IS NOT NULL;

针对大表的分批更新技巧

如果你的address表数据量很大，一次性UPDATE会长时间锁表影响业务。可以用分批更新的方式，每次处理1000行：

-- 重复执行这条语句直到返回0行更新
WITH batch AS (
    SELECT id 
    FROM address 
    WHERE customer_address IS NOT NULL 
      -- 假设加密后的是HEX字符串，过滤还未加密的行
      AND customer_address !~ '^[0-9a-fA-F]+$'
    LIMIT 1000
)
UPDATE address
SET customer_address = encode(
    aes_encrypt(customer_address::bytea, decode('your-64-char-hex-key', 'hex'), 'aes256'),
    'hex'
)
WHERE id IN (SELECT id FROM batch);

4. 解密查询与耗时对比

加密完成后，你需要解密才能查看原始数据，同时可以用EXPLAIN ANALYZE对比加密前后的查询耗时：

解密查询示例

如果是BYTEA存储：

SELECT 
  id,
  aes_decrypt(
      customer_address, 
      decode('your-64-char-hex-key', 'hex'), 
      'aes256'
  )::text AS decrypted_customer_address
FROM address;

如果是HEX字符串存储：

SELECT 
  id,
  aes_decrypt(
      decode(customer_address, 'hex'), 
      decode('your-64-char-hex-key', 'hex'), 
      'aes256'
  )::text AS decrypted_customer_address
FROM address;

耗时对比方法

用EXPLAIN ANALYZE分别执行加密前后的查询，对比执行时间：

-- 加密前的查询耗时（示例：按地址模糊搜索）
EXPLAIN ANALYZE SELECT * FROM address WHERE customer_address LIKE '%Main Street%';

-- 加密后的查询耗时（需要解密后再过滤）
EXPLAIN ANALYZE SELECT * FROM address 
WHERE aes_decrypt(customer_address, decode('your-64-char-hex-key', 'hex'), 'aes256')::text LIKE '%Main Street%';

重要注意事项

密钥保管：密钥是解密数据的唯一凭证，一旦丢失，加密的数据将永久无法恢复。建议用专业的密钥管理系统存储，不要硬编码在SQL脚本或代码中。
性能影响：加密和解密都会带来额外的性能开销，尤其是大表查询时，解密过滤的速度会比明文查询慢很多。如果需要频繁查询加密字段，可以考虑使用确定性加密（但安全性会有所降低）。
事务安全：如果是生产环境，建议在事务中执行UPDATE，万一出现问题可以回滚。

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