PHP小码农

Appearance

列级加密（pgcrypto） ​

卷级加密（上一页的 TDE 类方案）防的是「介质被拿走」，但只要一个人、一个账号、一条注入的 SQL 能连上数据库，内存里的数据对它就是明文。当威胁模型是「怕看到」而不是「怕拿走」——比如身份证号、手机号这类即使 DBA 也不应随便看到的字段——就需要列级加密（column-level encryption）：把加密下沉到字段层，密文落库，只有持有密钥的一方能解。

PostgreSQL 实现列级加密的标准工具是 pgcrypto 扩展：它随 contrib 发行，提供对称加密、非对称加密、哈希与口令散列四族函数，全部在 SQL 层调用。应用只需在写入时加密、读取时解密，其余查询路径不感知。它不改动内核、不需要预加载，CREATE EXTENSION 即用，是社区版用户最现实的字段级保护手段。

列级加密也是四族方案里约束最多的一层：密钥怎么传、密文怎么建索引、密钥怎么轮换、每行加解密要花多少 CPU，每一个都直接影响架构。本页按函数族逐个讲解，重点讨论密钥传递的泄漏面、确定性加密与索引的取舍、轮换迁移方案，最后以「手机号 / 身份证字段加密」的完整实战收尾。

前置知识

建议先阅读：透明数据加密（TDE）概述

基础概念 ​

pgcrypto 的函数按用途分四族，先建立全景再逐族展开：

函数族代表函数用途可逆性
对称加密（原生格式）encrypt / decrypt、encrypt_iv / decrypt_iv字段值加密，bytea 进出可逆
对称加密（OpenPGP 格式）pgp_sym_encrypt / pgp_sym_decrypt带完整性保护的字段加密（推荐）可逆
非对称加密pgp_pub_encrypt / pgp_pub_decrypt公钥加密、私钥解密可逆
哈希与 MACdigest / hmac摘要、校验、等值查找列不可逆
口令散列crypt / gen_salt口令存储（加盐+慢哈希）不可逆

加密、哈希与 MAC 的分工 ​

三者经常被混用，但在列级方案里分工明确：

  • 加密负责保密：除了持钥者，任何人都读不出内容（手机号、证件号、银行卡）。
  • 哈希（digest）负责等值检索：加密后的字段没法直接建索引查询，通常并存一列 hmac(值, 密钥) 作为查找键——不可逆，但同一明文得到同一摘要。
  • MAC（hmac）与裸哈希的区别在于带密钥：攻击者即使拿到全部密文与摘要，没有密钥也无法对候选明文做彩虹表式的比对（裸 digest('13800138000') 可以被手机号字典秒爆）。
  • 口令散列（crypt + gen_salt）是给「应用代管口令」的场景用的，加盐与迭代参数由 gen_salt 生成；现代实践更推荐口令散列在应用侧完成（bcrypt/Argon2 类），pgcrypto 的口令散列作为数据库侧兜底手段。

确定性加密与随机加密 ​

这是列级加密最重要的一个概念分叉，决定了「能不能建索引、能不能做等值查询」：

  • 确定性加密（deterministic）：同一密钥 + 同一明文 → 每次得到相同密文。pgcrypto 里 encrypt()（CBC 模式使用全零 IV）属于这一类。优点是密文列可以建索引、可以等值比较；缺点是「相等模式」暴露——攻击者不需要解密就能看出哪些行该字段值相同。
  • 随机加密（randomized）：每次加密引入随机成分（IV / 会话密钥），同一明文密文各不相同。encrypt_iv()（每次给新 IV）与 pgp_sym_encrypt()（每次生成随机会话密钥）属于这一类。安全性更好，但密文列无法等值索引——查询必须解密逐行比对，或借助并存的哈希列。

工程结论先放在这里：敏感字段用随机加密存储 + hmac 哈希列做查找，是兼顾安全与查询的默认姿势；确定性加密只在「必须用密文列本身做 join/唯一约束」时才考虑。

语法与用法 ​

启用扩展 ​

sql
-- 超级用户或有 CREATE 权限的角色执行
CREATE EXTENSION pgcrypto;

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

对称加密：原生格式 ​

sql
-- encrypt/decrypt：不传 IV（CBC 模式使用全零 IV），确定性
SELECT encode(
         encrypt(convert_to('13800138000', 'UTF8'),
                 convert_to('my-secret-key-32bytes-xxxxxxx', 'UTF8'),
                 'aes-cbc/pad:pkcs'),
         'base64') AS ciphertext;

-- 解密
SELECT convert_from(
         decrypt(decode('上一步的 base64', 'base64'),
                 convert_to('my-secret-key-32bytes-xxxxxxx', 'UTF8'),
                 'aes-cbc/pad:pkcs'),
         'UTF8') AS plaintext;

-- encrypt_iv/decrypt_iv：显式 IV，随机加密
SELECT encode(
         encrypt_iv(convert_to('13800138000', 'UTF8'),
                    convert_to('my-secret-key-32bytes-xxxxxxx', 'UTF8'),
                    decode('0123456789abcdef0123456789abcdef', 'hex'),  -- 16 字节 IV
                    'aes-cbc/pad:pkcs'),
         'base64');

type 字符串的格式是 算法-模式/填充，可选算法与默认值：

字段可选值默认
算法aes（可写 aes128 / aes192 / aes256）、bf（Blowfish）、des、3des、cast5aes
模式cbc、ecbcbc
填充pkcs、nonepkcs

编码与类型转换的配套函数 ​

pgcrypto 的函数进出都是 bytea，与业务字段之间的转换靠几个配套函数衔接，写加密 SQL 前先把它们理顺：

函数方向用途
convert_to(text, encoding)text → bytea把明文文本转成加密函数需要的字节串
convert_from(bytea, encoding)bytea → text把解密出的字节串还原为文本（编码须与写入时一致）
encode(bytea, 'base64' / 'hex')bytea → text密文以文本形态存储 / 展示（base64 比 hex 省约四分之一空间）
decode(text, 'base64' / 'hex')text → bytea从文本形态还原密文字节串
armor(bytea) / dearmor(text)bytea ↔ ASCII-armoredOpenPGP 报文与密钥的文本封装（PEM 风格多行）

存储形态二选一：密文列直接用 bytea 类型（省空间、免转换），或用 text 存 base64（ORM 与部分中间件对 bytea 支持不佳时的妥协）。选定后全表统一——混用会让每个查询都要先判断存储形态，是维护灾难。

对称加密：OpenPGP 格式（推荐） ​

sql
-- 加密：每次产生随机会话密钥 + MDC 完整性保护，输出 OpenPGP 报文格式
SELECT pgp_sym_encrypt('13800138000', 'my-passphrase', 'cipher-algo=aes256');

-- 解密
SELECT pgp_sym_decrypt(
         pgp_sym_encrypt('13800138000', 'my-passphrase', 'cipher-algo=aes256'),
         'my-passphrase') AS plaintext;
text
   plaintext
---------------
 13800138000

常用选项：cipher-algo（bf / aes / aes128 / aes192 / aes256 / 3des / cast5）、compress-algo、mdc（完整性保护开关，生成的报文默认启用；pgp_sym_decrypt / pgp_pub_decrypt 要求报文带 MDC，对无 MDC 数据拒绝解密）。

非对称加密与密钥对 ​

sql
-- pgcrypto 不提供密钥对生成函数；官方做法是用 gpg 生成、导入
-- （shell 中）gpg --gen-key 生成后导出：
--   gpg --armor --export <keyid>   → 公钥
--   gpg --armor --export-secret-keys <keyid> → 私钥
-- 数据库里用 dearmor() 把 ASCII-armored 密钥转成 bytea 存表：

CREATE TABLE app_keys (name text PRIMARY KEY, pub_key bytea, priv_key bytea);
INSERT INTO app_keys VALUES (
  'customer_pii',
  dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----...'),
  dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----...')
);

-- 公钥加密
SELECT pgp_pub_encrypt('110101199001011234',
                       (SELECT pub_key FROM app_keys WHERE name = 'customer_pii'));

-- 私钥解密（私钥本身有口令时通过 psw 参数提供）
SELECT pgp_pub_decrypt(ciphertext_column,
                       (SELECT priv_key FROM app_keys WHERE name = 'customer_pii'),
                       'key-passphrase');

armor() / dearmor() 是 ASCII-armored 文本与 bytea 的互转函数；pgp_key_id() 可以识别一段密钥或报文对应的密钥 ID，排查多密钥环境时很有用。

哈希与口令散列 ​

sql
-- digest：裸哈希（md5/sha1/sha224/sha256/sha384/sha512）
SELECT encode(digest('13800138000', 'sha256'), 'hex');

-- hmac：带密钥的哈希（等值查找列的标准选择）
SELECT encode(hmac('13800138000', convert_to('app-mac-key', 'UTF8'), 'sha256'), 'hex');

-- 口令散列：gen_salt 生成盐（含算法与迭代参数），crypt 计算散列
SELECT crypt('S3cret!', gen_salt('bf', 10));
text
              crypt
-----------------------------------
 $2a$10$N9qo8uLOickgx2ZMRZoMye...

校验口令时把「存库的散列串」整体作为盐传回 crypt：

sql
SELECT crypt('S3cret!', stored_hash) = stored_hash AS password_ok;

详细说明 ​

encrypt 族与 pgp 族怎么选 ​

两族对称加密的差别不只是「格式」：

维度encrypt / encrypt_ivpgp_sym_encrypt
输出格式裸密文字节OpenPGP 报文（带包头、盐、会话密钥、MDC）
随机性encrypt 确定性（零 IV）；encrypt_iv 随机天然随机（每次随机会话密钥）
完整性保护无（只保密，不改不了密文无从校验）MDC 默认开启，可发现篡改
密钥派生密钥直接使用口令经 S2K 派生，可加盐
开销低较高（随机数、派生、MDC、可选压缩）
密文长度膨胀块对齐开销报文结构开销更大

推荐次序：默认用 pgp_sym_encrypt（安全属性完整）；对热点大表、写入吞吐敏感的场景，用 encrypt_iv（自管 IV、保留随机性）或按架构评审后使用 encrypt（确定性）。裸 ecb 模式与 encrypt 零 IV 一样会暴露相等模式，任何情况下不要用 ECB。

算法选择与不安全算法警示 ​

des / bf 不要用于新代码

des（56 位密钥）早已可被暴力破解；bf（Blowfish）是 64 位分组算法，在大量数据下存在生日界攻击（Sweet32 类）风险，且非标准化的实现历史包袱多。两者存在于 pgcrypto 是为了兼容历史数据。新建字段一律 AES 系（aes / aes256）；存量 bf/des 数据纳入轮换迁移计划。

3des 的 112 位有效密钥强度同样低于现代标准，仅作为兼容选项存在。哈希函数中，md5 / sha1 只用于非安全用途（普通校验）；涉及安全比对的一律 sha256 及以上，并且优先 hmac 而不是裸 digest。

非对称加密的使用场景 ​

pgp_pub_encrypt / pgp_pub_decrypt 解决的是对称方案最大的痛点——加密方不需要知道解密密钥：

  • 应用只需要公钥就能写；解密需要私钥 + 口令，私钥可以只在取数侧（如导数平台、分析师终端）出现；
  • 私钥轮换可以只影响解密侧，不需要通知所有写入方换密钥；
  • 代价是性能明显更差（RSA/ElGamal 运算），且私钥管理本身成为新的敏感资产（存哪、谁能读、怎么轮换）。

典型用法是「写入侧用对称加密保吞吐，极敏感字段用公钥加密」，或者「应用侧写、人工审批后才能解密读」的取数流程。

密钥传递方式与泄漏面 ​

密钥作为字面量写进 SQL 等于把钥匙贴在门上

SELECT pgp_sym_decrypt(phone_enc, 'my-passphrase') 这条语句的文本会被以下系统原样保存：pg_stat_statements.query、慢查询日志（log_min_duration_statement）、语句日志（log_statement）、错误日志的 STATEMENT: 行（log_min_error_statement）。密钥从此成为所有这些副本里的常驻字符串。

按安全度递增的密钥传递方式：

  1. 绑定参数（应用侧最推荐）：密钥作为 $1 传入，pg_stat_statements 与语句日志里只有占位符骨架。注意 PG13+ 的 log_parameter_max_length / log_parameter_max_length_on_error 决定参数值是否会随日志泄漏，敏感库保持截断或关闭。
  2. 会话级自定义变量：SELECT set_config('app.enc_key', :key, false); 之后在 SQL 里用 current_setting('app.enc_key') 取用。密钥不再出现在后续语句文本里——但第一条 set_config 语句本身若以字面量执行仍会泄漏，因此应用执行它时也应走绑定参数。
  3. 密钥服务取回 + 绑定参数：应用启动/请求时从 KMS / Vault 取密钥，全程不进 SQL 文本，是生产的标准形态。
  4. 字面量：只允许出现在受控的临时排障会话，事后该会话所在实例的相关日志与 pg_stat_statements 快照要清理。

三把密钥的分工：加密、MAC 与盐 ​

一套列级加密方案里通常同时存在多把密钥，混用一把是常见的架构隐患。按用途拆开：

密钥用途泄漏后果轮换代价
数据加密密钥（DEK）pgp_sym_encrypt 等加解密密文可被全部解密高：版本列 + 双读改写全表
MAC 密钥hmac 查找列可对候选明文做比对（相等模式）极高：破坏索引与既有摘要
盐 / 派生参数伪名化、密钥派生影响对应关系中：全量假值 / 摘要变化

分工的原则：影响面小的密钥可以频繁动，影响面大的密钥要制度化地少动。 DEK 轮换有版本列机制兜底；MAC 密钥几乎不可轮换（换掉等于重建全部索引与历史摘要），因此它的权限控制要最严格——查询侧永远不需要 MAC 密钥，只有写入与回填路径才接触它。三把密钥在 KMS 里是三个独立对象，各自的授权主体与审计记录分开。

攻击面自查：加密方案自身会引入什么 ​

列级加密是防御手段，但实施不当会引入新的攻击面。上线前过一遍自查：

  • 密钥进过哪些副本？ SQL 文本、pg_stat_statements、语句日志、错误日志、应用配置中心、CI/CD 变量、工程师的终端 history——逐处确认密钥的形态（应只有占位符或引用，不是字面量）。
  • 备份里密文与密钥是否分离？ 备份目录与密钥来源在同一权限域里，加密形同虚设；恢复演练用的密钥也要有独立的审批。
  • 测试环境从哪拿数据？ 从生产复制的「密文 + 同一套会话变量默认值」会让测试环境变成解密环境；测试集走脱敏（下一页）而不是直接拷贝。
  • 谁能改加密逻辑？ 视图、函数、触发器里的加解密逻辑是高价值篡改目标（改成「顺手外发明文」），相关对象纳入 DDL 审计与权限收敛。
  • 回滚窗口里的明文：明文列删除后，历史备份与归档里仍有旧明文，其保留期与清理要和迁移计划一起定。
  • 密文侧信息泄漏：密文长度、更新频率、HMAC 相等性本身都是信息（相等模式、频率分析）；设计阶段接受这些侧信道，并在评审时写明「哪些等值性是故意保留的」。

索引限制：加密后无法高效等值查询 ​

随机加密的密文列上，B-tree 索引没有意义——同一个值每次密文都不同，等值条件 WHERE phone_enc = <密文> 永远匹配不上。可选路径按代价排列：

  • 并存 HMAC 查找列（默认推荐）：phone_hash bytea 存 hmac(明文, mac密钥, 'sha256')，在它上面建唯一索引；等值查询命中哈希列后再解密展示。MAC 密钥与加密密钥分属两把，泄露影响面更小。
  • 确定性加密列：encrypt()（零 IV）的密文可直接建索引、可 join、可做唯一约束；接受相等模式暴露的代价。适合「必须用密文本身做键」的场景（如跨系统对接以密文为主键）。
  • 前缀/脱敏列：对「模糊搜索」需求（手机号尾号查询），另存后四位明文列——注意这是有意的部分明文暴露，要过数据分级评审。
  • 范围查询：加密列无法做范围比较（BETWEEN、排序）。确有需求时考虑应用侧加密 + 应用侧解密后过滤（代价高），或重新评估该字段是否真需要加密。

密钥轮换：版本列与双读改写 ​

列级密钥轮换的现实约束是：存量密文必须用旧密钥解。标准迁移模式：

sql
-- 1. 表上增加版本列（新表建表时就带上）
ALTER TABLE customers ADD COLUMN IF NOT EXISTS enc_ver int NOT NULL DEFAULT 1;

-- 2. 写入侧切换：新数据用密钥 v2
--    enc_ver = 2, phone_enc = pgp_sym_encrypt(明文, key_v2, ...)

-- 3. 读取侧双读：按版本选密钥
SELECT CASE enc_ver
         WHEN 1 THEN pgp_sym_decrypt(phone_enc, current_setting('app.key_v1'))
         WHEN 2 THEN pgp_sym_decrypt(phone_enc, current_setting('app.key_v2'))
       END AS phone
FROM customers WHERE id = 42;

-- 4. 后台批量改写（低峰分批）
UPDATE customers SET
  phone_enc = pgp_sym_encrypt(pgp_sym_decrypt(phone_enc, current_setting('app.key_v1')),
                              current_setting('app.key_v2'), 'cipher-algo=aes256'),
  enc_ver   = 2
WHERE enc_ver = 1
  AND id BETWEEN 1 AND 100000;   -- 分批循环

全部改写完成后，旧密钥进入吊销流程。hmac 查找列通常不需要轮换（MAC 用途密钥轮换会破坏索引等值，代价高）——它的泄露影响是「相等模式可被持有 MAC 密钥者比对」，评估时注意这一点。

加密列与逻辑复制、下游系统 ​

加密列在数据流向下游时的三个事实要提前规划：

  • 物理复制无感，逻辑复制按原样传密文：流复制按块复制，备库与主库对称（解密能力取决于谁持钥）；逻辑复制把 bytea 密文原样送到订阅端——订阅端要么持钥（复制了解密能力），要么只见密文。期望「下游有数据、无解密能力」时，只同步密文列与查找列，不同步任何持钥配置。
  • ETL / 数仓消费密文：抽取环节对加密列做两类处理——直接落密文（分析不可用但安全），或在抽取管道中做 HMAC 伪名化（保留 join 能力，见下一页）。在抽取层解密成明文落数仓是明文扩散，除非数仓安全等级与生产同级，否则不做。
  • 全文检索与外部索引：Elasticsearch 等外部索引拿不到明文（这是特性不是缺陷），需要按字段检索时索引 HMAC 列或预计算的泛化列（城市、年龄段），而不是明文。

下游规划的核心问题是「解密能力要不要跟着数据走」：多数事故恰恰发生在下游——生产加密得很认真，数仓里躺着全量明文。把「下游各环节的数据形态」画进架构图，是列级加密方案评审的一部分。

与审计、脱敏的联动 ​

列级加密与本阶段其他两块内容的衔接点：

  • 审计侧：把「解密出口」纳入 pgaudit 对象审计——解密视图的 SELECT 权限授给审计角色，谁查过明文就有 OBJECT 级记录；普通会话审计可用 FUNCTION 类别盯住解密函数的每次调用。
  • 参数与日志：pgaudit.log_parameter = on 时，绑定参数里的密钥会进审计日志——敏感库保持 off，或用 pgaudit.log_parameter_max_size 截断（语句日志一页的参数泄漏逻辑在这里同样适用）。
  • 脱敏侧：加密与脱敏解决不同问题且常组合使用——加密保护「落盘的值」，脱敏保护「返回的值」；「加密列 + 按角色解密的视图」本身就是一种动态脱敏实现，下一页展开。
sql
-- 把解密视图纳入对象审计：授给审计角色后，对它的每次读取都会留下 OBJECT 级 AUDIT 记录
GRANT SELECT ON public.v_customers_pii TO auditor;
-- 日志形态（示意）：AUDIT: OBJECT,x,x,READ,SELECT,VIEW,public.v_customers_pii,SELECT * FROM public.v_customers_pii WHERE id = 42,<not logged>

性能影响：加解密发生在每行 ​

列级加密的成本发生在每一行的写入与读取路径上：

  • encrypt / decrypt（AES 硬件加速）单行开销在微秒级，热路径上对吞吐的影响通常个位数百分比；
  • pgp_sym_encrypt 每次调用要做随机数生成、密钥派生与 MDC 计算，单行开销显著高于原生格式，批量写入场景实测后再定挡；
  • 解密发生在 SELECT 的表达式求值里：SELECT * 式的宽查询会把整列解一遍——只解密当前请求真正需要的列，把加密列从 ORM 默认字段清单里摘出来；
  • 加密列上的 ORDER BY / 范围条件不可下推，相关排序过滤要么靠查找列，要么在应用侧完成；
  • 用 EXPLAIN ANALYZE 对比加密列表达式在执行计划中的耗时占比，是评估「要不要把某列加密」的直接依据。

密文列的监控与容量观测 ​

加密列的运维观测点与普通列不同，巡检关注三类对象：

sql
-- 表与索引的体积对比：密文膨胀是否在预期内（与迁移前的容量账对照）
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid))       AS heap_size,
       pg_size_pretty(pg_indexes_size(relid))        AS index_size
FROM pg_stat_user_tables
WHERE relname IN ('customers', 'orders_pii')
ORDER BY pg_total_relation_size(relid) DESC;
  • 容量：pgp 报文与文本形态编码带来的膨胀若让体积账与设计稿偏差，先核对「迁移时是否残留了明文影子列」，再看膨胀本身。
  • 性能：用 pg_stat_statements 对比加解密函数的总耗时占比——pgp_sym_decrypt 频繁出现在 top 查询里时，回头检查「是否解密了当前请求不需要的列」；HMAC 查找列的 idx_scan 应与查询路径预期一致，走不进索引说明等值条件写法有误。
  • 异常：解密报错（密钥版本缺失、报文损坏）应体现在应用侧错误指标上，数据库侧用日志错误关键字检索兜底。

把这组观测纳入常规巡检，与普通表共用面板但单独标注加密列；「先看密钥、再看库」的排查顺序写进 runbook。

应用侧加密 vs 数据库侧加密 ​

同样的「列级加密」，加密动作可以放在数据库（pgcrypto）或应用（PHP 的 openssl / sodium 等）里完成：

维度数据库侧（pgcrypto）应用侧加密
改造成本低（SQL 层完成，存量数据可 SQL 批量迁移）中（应用代码、ORM、历史数据迁移都要动）
密钥暴露面密钥进过数据库会话/内存（虽可绑定参数，仍存在于服务器进程内存）数据库全程只见密文，密钥不出应用
日志与 pg_stat_statements需要 log_parameter_max_length 等配置兜底天然不进 SQL 文本
DBA 可见性有权者可在库内解密DBA 看到的是纯密文
跨应用一致性由库内函数保证需要多个应用共享加密库/协议
适用运维主导的快速加固、存量系统密钥治理要求高的新系统

两者也可混合：应用侧加密为主，pgcrypto 承担「存量数据 SQL 批量迁移」「数据平台内受控解密」等场景。无论哪种，密钥管理（KMS、轮换、权限）都是方案的成败点，实现只是细节。

PHP 侧的接入要点 ​

数据库侧方案确定后，应用（以 PHP 为例）的接入质量决定密钥是否真的不泄漏：

  • 密钥获取：从 KMS 代理 / 配置中心取钥进内存，随请求生命周期管理；不进代码仓库、不进环境变量导出文件、不打进错误日志（异常处理的白名单里明确排除密钥变量）。
  • 统一封装：加解密调用收敛到一个服务类（如 EncryptionService::encrypt($column, $value)），算法、选项、密钥引用集中在配置里；散落在仓储层的 pgp_sym_encrypt(...) 字符串是配置漂移的起点。
  • 绑定参数执行：set_config 与查询一律走 PDO 预处理语句，密钥作为参数注入；代码评审的检查项加上「SQL 文本中不得出现密钥或 pgp_sym_* 字面量调用」。
  • 降级与可观测：解密失败（如密钥版本缺失）要有明确的错误分支与告警，而不是让密文以异常形式漏到接口响应里。
php
// PDO 侧统一示例：密钥与明文都走绑定参数
$stmt = $pdo->prepare(
    'INSERT INTO customers (name, phone_enc, phone_hash)
     VALUES (:name,
             pgp_sym_encrypt(:phone_a, current_setting(''app.cust_key''), ''cipher-algo=aes256''),
             hmac(:phone_b, current_setting(''app.mac_key''), ''sha256''))'
);
$stmt->execute([':name' => $name, ':phone_a' => $phone, ':phone_b' => $phone]);
// 同一明文出现两次就拆成两个占位符——不要赌驱动对命名参数复用的兼容性

实战示例 ​

示例一：手机号 / 身份证字段加密方案 ​

需求：customers 表存储客户 PII；手机号要支持等值查询（防重、按号查人）；身份证号只写不查（仅取数平台解密）；应用用 PHP，密钥来自配置中心的 KMS 代理。

sql
CREATE EXTENSION pgcrypto;

CREATE TABLE customers (
  id           bigserial PRIMARY KEY,
  name         text        NOT NULL,
  phone_enc    bytea       NOT NULL,          -- pgp 随机加密
  phone_hash   bytea       NOT NULL,          -- hmac 查找列
  id_card_enc  bytea,                         -- 公钥加密，取数侧解密
  enc_ver      int         NOT NULL DEFAULT 1,
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- 等值查找走哈希列
CREATE UNIQUE INDEX uq_customers_phone_hash ON customers (phone_hash);

应用会话（以 psql 演示；生产中密钥经绑定参数注入）：

sql
-- 1. 会话内注入密钥（该语句由应用用绑定参数执行）
SELECT set_config('app.cust_key', 'k1-aes-passphrase-from-kms', false);

-- 2. 写入
INSERT INTO customers (name, phone_enc, phone_hash, id_card_enc)
VALUES (
  '张三',
  pgp_sym_encrypt('13800138000', current_setting('app.cust_key'), 'cipher-algo=aes256'),
  hmac('13800138000', current_setting('app.cust_key'), 'sha256'),
  pgp_pub_encrypt('110101199001011234',
                  (SELECT pub_key FROM app_keys WHERE name = 'customer_pii'))
);

-- 3. 等值查询（命中唯一索引，不解密全表）
SELECT id, name,
       pgp_sym_decrypt(phone_enc, current_setting('app.cust_key')) AS phone
FROM customers
WHERE phone_hash = hmac('13800138000', current_setting('app.cust_key'), 'sha256');
text
 id | name |    phone
----+------+--------------
  1 | 张三 | 13800138000
sql
-- 4. 防重校验（注册时判断手机号是否已存在）
SELECT EXISTS (
  SELECT 1 FROM customers
  WHERE phone_hash = hmac('13800138000', current_setting('app.cust_key'), 'sha256'
)) AS phone_taken;

要点复盘：phone_hash 与加密密钥共用会话变量只是演示，生产中 HMAC 建议用独立 MAC 密钥；身份证列用公钥加密，写路径不需要私钥；所有涉及密钥的字面量都不出现在 SQL 文本里。

示例二：存量数据加密迁移 ​

把已有明文列平滑切换为密文列：

sql
-- 1. 加密列 + 版本列（先兼容双写期）
ALTER TABLE customers ADD COLUMN phone_enc bytea;
ALTER TABLE customers ADD COLUMN enc_ver  int   NOT NULL DEFAULT 0;
ALTER TABLE customers ADD COLUMN phone_hash bytea;

-- 2. 分批回填（低峰执行，注意 WAL 与复制延迟）
WITH batch AS (
  SELECT id, phone FROM customers
  WHERE enc_ver = 0 AND phone IS NOT NULL
  LIMIT 50000
)
UPDATE customers c
SET phone_enc  = pgp_sym_encrypt(b.phone, current_setting('app.cust_key'), 'cipher-algo=aes256'),
    phone_hash = hmac(b.phone, current_setting('app.cust_key'), 'sha256'),
    enc_ver    = 1
FROM batch b
WHERE c.id = b.id;

-- 3. 应用切换读写到加密列（灰度）后，删除明文列
-- ALTER TABLE customers DROP COLUMN phone;
-- 清理前把明文列的回滚窗口（WAL 归档/备份）一并纳入计划

注意：步骤 2 的批量 UPDATE 期间明文值会出现在 pg_stat_statements 与可能的日志里（本例用 set_config 传密钥可解密，但明文来自表列、不进语句文本，无额外泄漏）；若原方案是把明文作为字面量放进 UPDATE，必须改为函数内取值或绑定参数。

示例三：验证密钥没有泄漏到日志 ​

sql
-- 1. 检查 pg_stat_statements 中的语句文本是否含密钥痕迹
SELECT query FROM pg_stat_statements
WHERE query ILIKE '%pgp_sym_decrypt%'
LIMIT 5;
text
                      query
--------------------------------------------------
 SELECT pgp_sym_decrypt(phone_enc, current_setting('app.cust_key')) AS phone
 FROM customers WHERE phone_hash = hmac($1, current_setting('app.cust_key'), $2)
bash
# 2. 检查当日日志中是否出现密钥或明文手机号
grep -c 'k1-aes-passphrase' postgresql-2026-10-07.log || true
grep -c '13800138000' postgresql-2026-10-07.log || true
text
0
0

把这两步做成巡检脚本：语句文本里只允许出现 current_setting(...) 与 $n 占位符；日志里密钥字面量与样例敏感值计数必须为 0。

示例四：读写分级——SECURITY DEFINER 解密函数 ​

需求：业务角色只能拿到「脱敏形态」的手机号；取数角色经授权才能拿全号；密钥与解密逻辑收敛在函数里，不暴露给业务会话。

sql
-- 1. 收回业务角色对底表的直接访问
REVOKE ALL ON public.customers FROM app_user;

-- 2. 解密函数：定义者权限（SECURITY DEFINER），属主是可读底表的专用角色
CREATE FUNCTION app.get_phone_masked(p_id bigint) RETURNS text
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
  SELECT substring(pgp_sym_decrypt(phone_enc, current_setting('app.cust_key'))
                   FROM 1 FOR 3) || '****' || substring(
                   pgp_sym_decrypt(phone_enc, current_setting('app.cust_key')) FROM 8)
  FROM public.customers WHERE id = p_id
$$;

CREATE FUNCTION app.get_phone_full(p_id bigint) RETURNS text
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
  SELECT pgp_sym_decrypt(phone_enc, current_setting('app.cust_key'))
  FROM public.customers WHERE id = p_id
$$;

-- 3. 权限分级：业务角色只给脱敏函数；全号函数仅授权取数角色
GRANT EXECUTE ON FUNCTION app.get_phone_masked(bigint) TO app_user;
GRANT EXECUTE ON FUNCTION app.get_phone_full(bigint) TO data_officer;
REVOKE ALL ON FUNCTION app.get_phone_full(bigint) FROM PUBLIC;
sql
-- app_user 会话验证
SELECT app.get_phone_masked(1);
text
 get_phone_masked
------------------
 138****8000
sql
-- 4. 权限核对：业务角色拿不到底表与全号函数
SELECT has_table_privilege('app_user', 'public.customers', 'SELECT')          AS can_read_table,
       has_function_privilege('app_user', 'app.get_phone_full(bigint)', 'EXECUTE')    AS can_full_decrypt,
       has_function_privilege('app_user', 'app.get_phone_masked(bigint)', 'EXECUTE')  AS can_masked;
text
 can_read_table | can_full_decrypt | can_masked
----------------+------------------+------------
 f              | f                | t

要点复盘：函数体里用 current_setting 取钥（由应用经绑定参数在会话内注入），密钥不出现在函数定义里；SECURITY DEFINER 让普通角色「借道」函数访问底表而不获得底表权限，攻击面收敛到两个函数入口；全号函数是天然的审计锚点——pgaudit 的 FUNCTION 类别会记录每次函数调用（会话审计），每一次解密全号都有记录。

注意事项 ​

pgcrypto 不提供密钥对生成函数。 非对称加密的密钥对要用 gpg 等外部工具生成，再经 dearmor() 导入；不要去扩展里找一个不存在的「生成密钥」函数。

密钥字面量会泄漏到 pg_stat_statements 与日志。 这是列级加密方案最常见的翻车点。密钥传递必须走绑定参数或会话变量，并用巡检兜底（见实战示例三）。

随机加密的密文列不能等值索引。 需要按密文字段查询时，要么并存 HMAC 查找列，要么明确接受确定性加密的相等模式暴露。两难的唯一出口是「查找列 + 随机加密」组合。

LIKE / 范围查询在密文列上不可用。 方案设计阶段就要列出该字段的所有查询方式；加密后才发现有模糊查询需求，返工成本极高。

ECB 模式与零 IV 的确定性加密暴露相等模式。 同值同密文意味着攻击者可做频次分析、可对已知明文做比对；用它做「防 DBA」的保密是自欺。

des / bf 是遗留算法。 新代码一律 AES；存量纳入轮换。哈希选 sha256+ 与 hmac，md5/sha1 只用于非安全用途。

解密有 CPU 成本，避免宽查询全列解密。 SELECT * 会把加密列也解一遍；应用层按需选择列。批量回填与轮换任务用分批事务，控制 WAL 量与复制延迟。

pgp 解密函数拒绝无 MDC 报文。 这是保护而非障碍——自研的「加密报文」若绕过 MDC，等于放弃了篡改检测，不建议。

应用侧加密与数据库侧加密的密钥治理不能混用。 同一字段两套加密并存时，轮换、审计、访问控制要明确唯一 owner，否则会出现「没人知道这列的密钥在哪」的死数据。

列级加密不影响权限模型的必要性。 有 SELECT 权限的角色即使解不了密文，也能观察到密文长度与更新时间；列级加密是权限与 RLS 的补充，不是替代。

密文膨胀要进存储规划。 pgp 报文格式比明文长出几十到上百字节（包头、盐、MDC），bytea 还有存储开销；单行膨胀乘以行数就是容量账，宽表的多个加密列要一起算。

函数与视图的属主是新的敏感资产。 SECURITY DEFINER 解密函数把「借道访问」绑定在属主角色上，属主权限的收敛与审计（它自己是谁、能被谁替换）要纳入常规权限巡检，而不是一次配置后遗忘。

密文列不做「部分更新」，高频变更列要先掂量。 UPDATE 触发的是整列密文重写（新明文整体加密），高频更新的字段加密前要评估写放大；优先加密读多写少的 PII 列，高频变更列重新审视必要性。

密文列与 HMAC 查找列必须成对写入。 两列在同一事务里保持一致是方案正确性的前提；任何绕过默认写入路径的批量脚本都要同时写两列，巡检里加一条「两列不一致行数 = 0」的对账查询。

最佳实践 ​

  1. 默认组合：pgp_sym_encrypt 随机加密 + hmac 查找列 + 唯一索引。 兼顾保密强度与等值查询，是 PII 字段的出厂设置。
  2. 密钥只经绑定参数或会话变量进入 SQL。 严禁字面量；PG13+ 实例配合 log_parameter_max_length 控制日志暴露面，并把「密钥泄漏巡检」纳入脚本。
  3. 查找列用独立 MAC 密钥。 加密密钥与 HMAC 密钥分开，任一泄露的影响面被隔离；HMAC 优先于裸 digest，防字典比对。
  4. 算法白名单化。 应用与运维约定只允许 aes（/aes256）与 sha256+；bf、des、3des、ecb 出现在新代码里即打回。
  5. 建表时带上 enc_ver 版本列。 密钥轮换是必然事件，版本列 + 双读 + 分批改写是标准迁移路径；没有版本列的加密表都会在第一次轮换时变成大工程。
  6. 极敏感、低频读的字段用公钥加密。 私钥只存在于取数侧，写路径永不接触解密能力，天然实现「可写不可读」的职责分离。
  7. 口令存储优先应用侧散列（bcrypt/Argon2 类），pgcrypto 的 crypt/gen_salt 用于数据库侧兜底或历史系统；不要用裸哈希存口令。
  8. 把解密限定在必要列与必要会话。 ORM 默认字段清单里摘除加密列；取数与导出走审批后的专用会话（专用 set_config 密钥），不与业务连接混用。
  9. 批量加密/解密任务分批执行并监控复制延迟。 每批控制在数千到数万行，观察 pg_stat_replication 与日志增长，避免把主备链路打挂。
  10. 评估与卷级 TDE 的组合而非二选一。 卷级防「拿走」、列级防「看到」，高敏感系统两层都上，密钥体系分别治理。
  11. 应用侧加密优先用于新建系统；pgcrypto 胜在存量改造与库内批量迁移，按改造成本与密钥治理要求选侧。
  12. 加密方案要写入演练。 定期做「用备份数据 + 密钥恢复一张加密表」的演练——密文数据加丢失密钥等于数据报废，恢复演练必须覆盖密钥资产。

下一节

继续学习：数据脱敏方案

参考链接 ​