KingbaseES 实战：用户密码存储与口令认证深度解析

jiayou64
5 阅读3分钟

KingbaseES 实战：用户密码存储与口令认证深度解析

引言

密码管理是数据库安全的第一道防线。KingbaseES 提供了从经典的 SCRAM-SHA-256 到国密算法 SM2、SM3、SM4 的完整加密支持，能够满足从通用场景到国密合规的各类安全需求。

但在实际运维中，很多用户容易混淆两个核心概念：用户密码怎么存 和 口令怎么验。这种混淆常常导致“密码明明正确却登录失败”的困扰——密码用 MD5 方式存储，却要求用 SCRAM-SHA-256 方式认证；或者为新用户设置了强密码，却因为存储算法与认证方法不匹配而无法连接。

密码存储的本质：用户密码存储使用的是哈希算法——一种不可逆的单向加密技术。与可逆的对称/非对称加密不同，哈希算法将密码转换为固定长度的哈希值后，无法反向还原出原始密码，这正是密码存储安全的基础。

本文将首先解析用户密码存储机制，然后详解口令认证流程，最后通过实战案例演示二者的协同配置与故障排查。

第一章：核心概念——用户密码存储与口令认证的分离

1.1 两个独立但协同的安全环节

维度用户密码存储加密基于口令的认证
核心问题数据文件泄露后，用户密码是否安全？网络传输时，口令是否会被窃取？
控制参数password_encryptionsys_hba.conf 中的 METHOD
默认值scram-sha-256scram-sha-256（推荐）
作用时机创建/修改用户密码时客户端连接数据库时
数据流向密码 → 加密 → 存入 sys_authid客户端输入 → 挑战-响应 → 验证
比喻把用户密码写进保险箱的方式门卫核对身份的方式

1.2 为什么需要分离？

  • 存储安全：防止 DBA 或攻击者直接查看数据文件获取密码
  • 传输安全：防止网络嗅探获取密码明文
  • 灵活组合：可根据安全需求独立升级某一环节

1.3 密码存储在哪里？

KingbaseES 中所有用户的密码信息都存储在 sys_authid 系统表中：

-- 查看用户密码存储信息
SELECT rolname, rolpassword, rolvaliduntil 
FROM sys_authid 
WHERE rolname IN ('system', 'fin_app');

输出示例：

   rolname   |                             rolpassword                             | rolvaliduntil
-------------+---------------------------------------------------------------------+---------------
 system      | SCRAM-SHA-256$4096:SBfzuqMqQjEWP/w+n8TlwQ==$WeOQ2yOLEGvYp9v0DS5F... | 
 fin_app     | SCRAM-SHA-256$4096:kOPAYRYx2elBrNaeCo18BQ==$4xV0YdFPq7Uq2UvGqJ2H... |

rolpassword 字段取值详解：

格式说明安全等级适用场景
NULL用户未设置密码❌ 无特殊用途账户
md5 + 32位哈希MD5哈希存储⭐⭐ 中遗留系统过渡
SCRAM-SHA-256$...SCRAM挑战-响应格式⭐⭐⭐ 高国际标准场景（默认）
SCRAM-SM3$...国密SCRAM格式⭐⭐⭐ 高国密合规场景
sm3$...国密SM3哈希格式⭐⭐⭐ 高国密基础场景

1.4 测试用户准备

为便于后续演示，我们创建四个测试用户：

-- 创建演示数据库
CREATE DATABASE demodb;

-- 创建四个测试用户
CREATE USER fin_app WITH PASSWORD 'SecurePass!2026';          -- 金融应用
CREATE USER teaching_assistant WITH PASSWORD 'Kingbase_123';  -- 教学应用
CREATE USER legacy_mercury WITH PASSWORD 'Mercury@2026';      -- 遗留系统
CREATE USER gov_finance WITH PASSWORD 'Gov@2026';             -- 政务系统

这四个用户将贯穿全文，演示不同场景下的配置与问题排查：

用户目标存储算法目标认证方法测试重点
fin_appSCRAM-SHA-256scram-sha-256强安全标准配置
teaching_assistantSCRAM-SHA-256scram-sha-256通用场景
legacy_mercuryMD5md5遗留系统过渡
gov_financeSM3sm3国密合规场景

核心问题：如果存储算法与认证方法不匹配，就会导致 “密码正确却登录失败”。

第二章：用户密码存储加密——password_encryption 详解

2.1 加密算法基础

在深入密码存储之前，有必要了解数据库加密算法的整体分类：

算法类型代表算法基本原理适用场景
对称加密AES、SM4加解密使用相同密钥字段级加密
非对称加密RSA、SM2公钥加密、私钥解密数字签名、SSL证书
哈希算法SM3、SHA-256、MD5单向加密，不可逆存储用户密码

用户密码存储使用的是哈希算法——其不可逆特性确保了即使数据文件泄露，也无法还原出原始密码。

2.2 支持的存储算法

KingbaseES 支持多种口令加密算法，由 password_encryption 参数动态决定。

2.2.1 通用算法
参数值算法类型技术标准存储格式示例安全等级
scram-sha-256SCRAM + SHA-256RFC 7677SCRAM-SHA-256$4096:盐$密钥⭐⭐⭐
md5MD5 哈希RFC 1321md5 + 32位十六进制哈希⭐⭐
2.2.2 国密算法（V9R1+）
参数值算法类型技术标准存储格式示例安全等级
sm3国密 SM3GM/T 0004-2012sm3 + 64位十六进制哈希⭐⭐⭐
scram-sm3SCRAM + SM3GM/T 0091-2020SCRAM-SM3$...⭐⭐⭐

2.3 查看密码存储格式

-- 查看用户密码存储格式
SELECT 
    rolname AS 用户名,
    CASE 
        WHEN rolpassword IS NULL THEN '未设置密码'
        WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'SCRAM-SHA-256'
        WHEN rolpassword LIKE 'SCRAM-SM3$%' THEN 'SCRAM-SM3'
        WHEN rolpassword LIKE 'md5%' THEN 'MD5'
        WHEN rolpassword LIKE 'sm3%' THEN 'SM3'
        ELSE '未知格式'
    END AS 存储算法
FROM sys_authid 
WHERE rolname IN ('fin_app', 'teaching_assistant', 'legacy_mercury', 'gov_finance');

输出：

       用户名       |   存储算法    
--------------------+---------------
 fin_app            | SCRAM-SHA-256
 teaching_assistant | SCRAM-SHA-256
 legacy_mercury     | SCRAM-SHA-256
 gov_finance        | SCRAM-SHA-256

说明：此时所有用户均为默认的 SCRAM-SHA-256 存储格式，后续我们将修改 legacy_mercury 和 gov_finance 的存储算法。

2.4 参数作用域与优先级

password_encryption 是 USER 级别参数，可在多个层级设置：

优先级（从高到低）：

  1. 会话级：SET password_encryption = 'sm3';
  2. 用户级：ALTER ROLE fin_app SET password_encryption = 'sm3';
  3. 数据库级：ALTER DATABASE demodb SET password_encryption = 'scram-sm3';
  4. 实例级：ALTER SYSTEM SET password_encryption = 'scram-sm3';

2.5 参数验证

-- 查看参数级别和是否需要重启
SELECT name, context, pending_restart 
FROM sys_settings 
WHERE name = 'password_encryption';

输出：

        name         | context | pending_restart
---------------------+---------+-----------------
 password_encryption | user    | f

说明：context = user 表示可在各层级动态设置；pending_restart = f 表示修改后无需重启。

2.6 修改存储算法

理解了存储算法的基本概念，接下来为测试用户配置不同的存储格式。

2.6.1 为用户设置默认存储算法
-- 为各用户设置默认存储算法
ALTER ROLE fin_app SET password_encryption = 'scram-sha-256';
ALTER ROLE teaching_assistant SET password_encryption = 'scram-sha-256';
ALTER ROLE legacy_mercury SET password_encryption = 'md5';
ALTER ROLE gov_finance SET password_encryption = 'sm3';
2.6.2 验证用户级设置
-- 以 legacy_mercury 身份连接，查看生效的算法
\c - legacy_mercury
SHOW password_encryption;

输出：

 password_encryption
---------------------
 md5
2.6.3 ⚠️ 关键提醒：重置密码使算法生效
-- 返回 system 用户，重置所有测试用户的密码
\c - system

ALTER USER fin_app PASSWORD 'SecurePass!2026';
ALTER USER teaching_assistant PASSWORD 'Kingbase_123';
ALTER USER legacy_mercury PASSWORD 'Mercury@2026';
ALTER USER gov_finance PASSWORD 'Gov@2026';
2.6.4 验证密码格式已更新
-- 查看更新后的密码存储格式
SELECT 
    rolname AS 用户名,
    CASE 
        WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'SCRAM-SHA-256'
        WHEN rolpassword LIKE 'md5%' THEN 'MD5'
        WHEN rolpassword LIKE 'sm3%' THEN 'SM3'
    END AS 存储算法,
    LEFT(rolpassword, 40) AS 预览
FROM sys_authid 
WHERE rolname IN ('fin_app', 'teaching_assistant', 'legacy_mercury', 'gov_finance');

输出：

     用户名      |   存储算法    |                  预览                  
-----------------+---------------+------------------------------------------
 teaching_assistant | SCRAM-SHA-256 | SCRAM-SHA-256$4096:hJtCJycgo8UPa1T2Atf2jw==
 fin_app         | SCRAM-SHA-256 | SCRAM-SHA-256$4096:kOPAYRYx2elBrNaeCo18BQ==
 legacy_mercury  | MD5           | md5bb38a33d6911780aa3d4d13a2210e670
 gov_finance     | SM3           | sm33f64ebe87017d4c9364f1547b67e896a552a

✅ 验证通过：legacy_mercury 已转为 MD5 格式，gov_finance 已转为 SM3 格式。

2.7 本章核心要点

要点说明
存储算法只影响未来修改参数只影响后续执行的 CREATE/ALTER USER
存量密码不变已存在用户的密码格式不会自动改变
修改后必须重置ALTER ROLE ... SET + ALTER USER ... PASSWORD 两步完成
存储 ≠ 认证存储算法只决定“怎么存”，不决定“怎么验”

第三章：口令认证——基于密码的认证方法

配置好密码存储后，还需要在 sys_hba.conf 中指定认证方法，客户端才能成功连接。

3.1 基于密码的认证方法

3.1.1 认证方法一览
METHOD安全等级说明适用版本
scram-sha-256⭐⭐⭐SCRAM挑战-响应，抗嗅探、抗重放所有版本
scram-sm3⭐⭐⭐SCRAM + SM3V9R1+
sm3⭐⭐⭐国密 SM3 哈希认证V9R1+
md5⭐⭐兼容老客户端所有版本
password⚠️ 极低明文密码传输仅限测试

核心原则：认证方法必须与用户密码的存储算法兼容。

3.1.2 认证方法对比
认证方法密码传输方式抗嗅探抗重放
scram-sha-256加密挑战-响应✅✅
scram-sm3加密挑战-响应✅✅
sm3SM3哈希✅❌
md5MD5哈希⚠️ 弱❌

3.2 sys_hba.conf 配置

3.2.1 配置结构

根据第二章设置的存储算法，配置对应的认证方法：

# TYPE    DATABASE    USER                ADDRESS             METHOD
# -----------------------------------------------------------------------
local     all         system                                  peer
host      all         fin_app             192.168.126.0/24    scram-sha-256
host      all         teaching_assistant  192.168.126.0/24    scram-sha-256
host      all         legacy_mercury      192.168.126.0/24    md5
host      all         gov_finance         192.168.126.0/24    sm3
3.2.2 查看生效配置
-- 查看当前生效的所有认证规则
SELECT type, user_name, address, auth_method
FROM sys_hba_file_rules
WHERE user_name != '{system}'::text[];

输出：

 type |    user_name          |    address    |  auth_method  
------+-----------------------+---------------+---------------
 host | {fin_app}             | 192.168.126.0 | scram-sha-256
 host | {teaching_assistant}  | 192.168.126.0 | scram-sha-256
 host | {legacy_mercury}      | 192.168.126.0 | md5
 host | {gov_finance}         | 192.168.126.0 | sm3
3.2.3 重载配置
# 修改配置文件后需重载使其生效
sys_ctl reload -D $KINGBASE_DATA

3.3 认证流程详解

3.3.1 SCRAM-SHA-256 认证流程
# 网络连接测试
ksql -U fin_app -d demodb -h 192.168.126.16
用户 fin_app 的口令：
demodb=>

认证流程：

客户端(fin_app)                       服务器
    |                                       |
    |---- TCP连接 ------------------------>|
    |                                       | 1. 检查 sys_hba.conf
    |<--- 挑战: salt + iterations + nonce --|
    |                                       |
    | 2. 计算 ClientProof                    |
    |---- ClientProof + nonce ------------->|
    |                                       | 3. 验证 ClientProof
    |<--- 认证成功/失败 ---------------------|

安全特性：

  • 密码永不明文传输：网络上只传输哈希值和证明
  • 防止重放攻击：每次认证使用不同的随机数
3.3.2 SM3 认证流程
# 国密用户连接
ksql -U gov_finance -d demodb -h 192.168.126.16
用户 gov_finance 的口令：
demodb=>

认证流程：

客户端(gov_finance)                   服务器
    |                                       |
    |---- TCP连接 ------------------------>|
    |                                       | 1. 检查 sys_hba.conf
    |<--- 认证请求 -------------------------|
    |                                       |
    | 2. 计算 SM3 哈希值                     |
    |---- SM3哈希值 ----------------------->|
    |                                       | 3. 验证哈希值
    |<--- 认证成功/失败 ---------------------|

说明：sm3 认证流程相对简单，缺乏 SCRAM 的挑战-响应机制，不具备抗重放能力。

3.4 连接测试验证

# 网络连接测试
ksql -U fin_app -d demodb -h 192.168.126.16        -- ✓ 成功
ksql -U teaching_assistant -d demodb -h 192.168.126.16  -- ✓ 成功
ksql -U legacy_mercury -d demodb -h 192.168.126.16 -- ✓ 成功
ksql -U gov_finance -d demodb -h 192.168.126.16    -- ✓ 成功

# 本地连接测试（失败：无对应规则）
ksql -U fin_app -d demodb
ksql: 错误: FATAL: 没有用于主机 "[local]" 的 sys_hba.conf 记录

3.5 本章核心要点

要点说明
认证方法必须匹配存储算法scram-sha-256 存储对应 scram-sha-256 认证
网络连接需指定IPhost 类型规则只对远程连接生效
配置后需重载sys_ctl reload 使配置生效

第四章：存储与认证的协同测试

理解了存储和认证各自的原理，接下来看看它们如何协同工作——以及不匹配时会发生什么。

4.1 当前配置状态

经过前两章的配置，四个测试用户的状态如下：

用户存储算法认证方法预期结果
fin_appSCRAM-SHA-256scram-sha-256✅ 成功
teaching_assistantSCRAM-SHA-256scram-sha-256✅ 成功
legacy_mercurymd5md5✅ 成功
gov_financesm3sm3✅ 成功

4.2 兼容性测试

测试1：MD5 认证方式
# 临时修改 sys_hba.conf，全部改用 md5
host    all    fin_app            192.168.126.0/24  md5
host    all    teaching_assistant  192.168.126.0/24  md5
host    all    legacy_mercury     192.168.126.0/24  md5
host    all    gov_finance        192.168.126.0/24  md5

# 重载配置
sys_ctl reload

测试结果：

ksql -U legacy_mercury -d demodb -h 192.168.126.16   # ✓ 成功
ksql -U fin_app -d demodb -h 192.168.126.16          # ✓ 成功
ksql -U gov_finance -d demodb -h 192.168.126.16      # ✓ 成功

结论：MD5 认证方式兼容所有存储算法。

测试2：SCRAM-SHA-256 认证方式
# 临时修改 sys_hba.conf
host    all    fin_app            192.168.126.0/24  scram-sha-256
host    all    teaching_assistant  192.168.126.0/24  scram-sha-256
host    all    legacy_mercury     192.168.126.0/24  scram-sha-256
host    all    gov_finance        192.168.126.0/24  scram-sha-256

# 重载配置
sys_ctl reload

测试结果：

ksql -U legacy_mercury -d demodb -h 192.168.126.16   # ❌ 失败
ksql -U fin_app -d demodb -h 192.168.126.16          # ✓ 成功
ksql -U gov_finance -d demodb -h 192.168.126.16      # ❌ 失败

结论：SCRAM-SHA-256 只兼容同族算法。

测试3：SM3 认证方式
# 临时修改 sys_hba.conf
host    all    fin_app            192.168.126.0/24  sm3
host    all    teaching_assistant  192.168.126.0/24  sm3
host    all    legacy_mercury     192.168.126.0/24  sm3
host    all    gov_finance        192.168.126.0/24  sm3

# 重载配置
sys_ctl reload

测试结果：

ksql -U legacy_mercury -d demodb -h 192.168.126.16   # ❌ 失败
ksql -U fin_app -d demodb -h 192.168.126.16          # ❌ 失败
ksql -U gov_finance -d demodb -h 192.168.126.16      # ✓ 成功

结论：SM3 认证方式只兼容 SM3 存储算法。

测试4：恢复原始配置
# 恢复原始配置
host    all    fin_app            192.168.126.0/24  scram-sha-256
host    all    teaching_assistant  192.168.126.0/24  scram-sha-256
host    all    legacy_mercury     192.168.126.0/24  md5
host    all    gov_finance        192.168.126.0/24  sm3

# 重载配置
sys_ctl reload

4.3 兼容性矩阵

认证方法MD5存储SCRAM-SHA-256存储SM3存储
md5✅ 成功✅ 成功✅ 成功
scram-sha-256❌ 失败✅ 成功❌ 失败
sm3❌ 失败❌ 失败✅ 成功

4.4 本章核心结论

规律说明
弱可验强MD5 认证可验证所有存储算法
同族兼容同一算法族的存储和认证完全兼容
跨族不兼容通用算法与国密算法互不兼容

第五章：最佳实践与故障排查

理解了存储与认证的机制，接下来看看如何在实际运维中应用这些知识。

5.1 先记住这三条

在开始具体操作之前，有三条原则需要先理清楚：

第一，存储和认证是两码事
password_encryption 决定密码怎么存在数据文件里，sys_hba.conf 决定客户端连接时怎么验证。前者管“存”，后者管“验”，各司其职。

第二，改存储算法必须重置密码
修改 password_encryption 参数后，已存在的用户密码不会自动转换格式。必须执行 ALTER USER ... PASSWORD 重新设置，新算法才会真正生效。

第三，认证方法必须兼容存储算法
MD5 认证可以验证所有存储格式（但安全性低），SCRAM 只能验证同族算法，国密和通用算法互不兼容。

5.2 标准化作业流程

5.2.1 创建新用户（三步法）
-- 第一步：确认当前默认算法
SHOW password_encryption;

-- 第二步：创建用户
CREATE USER new_fin_app WITH PASSWORD 'StrongPwd!2026';

-- 第三步：验证存储格式
SELECT rolname, 
       CASE WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'SCRAM-SHA-256'
            WHEN rolpassword LIKE 'md5%' THEN 'MD5'
       END AS algorithm
FROM sys_authid WHERE rolname = 'new_fin_app';

配套 HBA 配置：

# 编辑 sys_hba.conf
host    demodb    new_fin_app    192.168.126.0/24    scram-sha-256

# 重载配置
SELECT sys_reload_conf();
5.2.2 算法迁移（两步法）

这种情况在数据库迁移后尤为常见——HBA配置被统一刷新，但用户密码还是旧的。

-- 第一步：设置用户默认算法
ALTER ROLE legacy_mercury SET password_encryption = 'scram-sha-256';

-- 第二步：【关键】重置密码
ALTER USER legacy_mercury PASSWORD 'NewStrongPwd!2026';

-- 验证
SELECT LEFT(rolpassword, 20) FROM sys_authid WHERE rolname = 'legacy_mercury';
-- 预期输出：SCRAM-SHA-256$...

⚠️ 重要提醒：不执行第二步，仅修改用户级参数无效！

5.3 典型故障排查案例

案例一：国密用户配置错误

现象：政务系统用户 gov_finance 报错认证失败，密码确认无误。

排查过程：

  1. 查存储格式：

    SELECT rolname,
       CASE WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'SCRAM-SHA-256'
            WHEN rolpassword LIKE 'sm3%' THEN 'SM3'
       END AS storage_algo
FROM sys_authid WHERE rolname = 'gov_finance';

    输出：gov_finance | SCRAM-SHA-256

  2. 查认证规则：

    SELECT auth_method FROM sys_hba_file_rules 
WHERE user_name = '{gov_finance}'::text[];

    输出：sm3

  3. 根因分析：存储为通用算法，认证为国密算法，跨族不兼容。

解决方案：

ALTER ROLE gov_finance SET password_encryption = 'sm3';
ALTER USER gov_finance PASSWORD 'Gov@2026';
案例二：遗留系统升级后无法登录

现象：旧系统迁移后，用户 legacy_mercury 无法连接。

排查过程：

  1. 查存储格式：rolpassword 以 md5 开头
  2. 查认证规则：auth_method 为 scram-sha-256

解决方案（二选一）：

  • 方案 A（推荐，提升安全）：ALTER USER legacy_mercury PASSWORD 'Mercury@2026';
  • 方案 B（临时，保持兼容）：修改 HBA 为 md5 并重载

5.4 场景化配置建议

场景存储算法认证方法说明
通用生产环境scram-sha-256scram-sha-256首选
国密合规环境scram-sm3scram-sm3国密高安全
基础国密场景sm3sm3国密基础
遗留系统过渡scram-sha-256md5临时方案

禁忌组合（登录必败）：

  • 存储 md5 + 认证 scram-sha-256
  • 存储 scram-sha-256 + 认证 sm3
  • 存储 sm3 + 认证 scram-sha-256

5.5 快速诊断工具

-- 查看所有用户的状态
SELECT 
    rolname,
    CASE 
        WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'SCRAM'
        WHEN rolpassword LIKE 'md5%' THEN 'MD5'
        WHEN rolpassword LIKE 'sm3%' THEN 'SM3'
    END AS storage,
    (SELECT auth_method FROM sys_hba_file_rules 
     WHERE user_name = ('{' || rolname || '}')::text[]) AS auth
FROM sys_authid 
WHERE rolcanlogin = true AND rolname NOT IN ('system', 'kingbase');

5.6 排查要点速查

故障现象可能原因关键检查解决方案
密码正确但认证失败存储与认证不匹配查 rolpassword 和 HBA重置密码或修改HBA
新建用户仍是MD5会话级参数覆盖SHOW password_encryption;RESET password_encryption;
提示密码过期超过有效期查 rolvaliduntilALTER USER ... VALID UNTIL 'infinity';

第六章：总结

6.1 核心逻辑回顾

存储算法（password_encryption） ← 决定数据文件里的密码格式
        ↓
   必须匹配（否则登录失败）
        ↓
认证方法（sys_hba.conf METHOD） ← 决定网络传输的验证方式

6.2 三条基本原则

序号原则一句话说明
①存验分离存储和认证是两个参数，各管一摊
②重置生效改存储后必须重置密码，不重置不生效
③匹配通行认证必须兼容存储，跨族必败

6.3 兼容性速查表

认证方法 \ 存储算法MD5SCRAM-SHA-256SM3
md5✅✅✅
scram-sha-256❌✅❌
sm3❌❌✅

6.4 故障排查三步曲

第一步：查存储
SELECT rolpassword FROM sys_authid WHERE rolname='用户名';
看前缀：SCRAM / md5 / sm3

第二步：查认证
SELECT auth_method FROM sys_hba_file_rules WHERE user_name='{用户名}';
看方法：scram-sha-256 / md5 / sm3

第三步：对规则

  • 同族 → 正常
  • 存储MD5 + 认证SCRAM → 重置密码升级
  • 跨族（SCRAM vs SM3） → 重置密码统一算法

6.5 一句话总结

密码存储决定数据文件的安全底线，口令认证决定网络传输的安全上限，二者必须匹配——改存储必须重置密码，查故障先看两处配置。

【附录】自动化诊断脚本

A.1 脚本功能

一键扫描所有可登录用户，识别以下问题：

  • 存储算法与认证方法不匹配
  • 密码过期
  • HBA规则缺失

说明：该函数需要超级用户权限执行。

A.2 部署脚本

CREATE OR REPLACE FUNCTION fn_check_auth_compatibility(p_target_user name DEFAULT NULL)
RETURNS TABLE (
    user_name       name,
    storage_algo    text,
    auth_method     text,
    issue_level     text,
    issue_detail    text,
    fix_suggestion  text
) AS $$
DECLARE
    v_rec record;
    v_storage_prefix text;
    v_hba_method text;
    v_level text;
    v_detail text;
    v_fix text;
BEGIN
    FOR v_rec IN 
        SELECT 
            a.rolname,
            a.rolpassword,
            a.rolvaliduntil,
            CASE 
                WHEN a.rolpassword IS NULL THEN 'NULL'
                WHEN a.rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'scram-sha-256'
                WHEN a.rolpassword LIKE 'SCRAM-SM3$%' THEN 'scram-sm3'
                WHEN a.rolpassword LIKE 'md5%' THEN 'md5'
                WHEN a.rolpassword LIKE 'sm3%' THEN 'sm3'
                ELSE 'unknown'
            END as storage_algo
        FROM sys_authid a
        WHERE a.rolcanlogin = true 
          AND a.rolname NOT IN ('system', 'kingbase')
          AND (p_target_user IS NULL OR a.rolname = p_target_user)
    LOOP
        v_storage_prefix := v_rec.storage_algo;
        v_hba_method := NULL;
        v_level := 'OK';
        v_detail := '配置正常';
        v_fix := '无需操作';

        -- 查找认证规则
        SELECT h.auth_method INTO v_hba_method
        FROM sys_hba_file_rules h
        WHERE (h.user_name = ('{' || v_rec.rolname || '}')::text[] OR h.user_name = '{all}'::text[])
          AND h.type = 'host'
          AND h.auth_method NOT IN ('peer', 'ident', 'trust')
        LIMIT 1;

        -- 兼容性判断
        IF v_rec.rolpassword IS NULL THEN
            v_level := 'CRITICAL';
            v_detail := '用户未设置密码';
            v_fix := 'ALTER USER ' || v_rec.rolname || ' PASSWORD ''<密码>'';';
            
        ELSIF v_hba_method IS NULL THEN
            v_level := 'WARNING';
            v_detail := '未找到密码认证规则';
            v_fix := '请检查 sys_hba.conf 配置';
            
        ELSIF v_hba_method = 'md5' THEN
            CONTINUE;  -- MD5兼容所有
            
        ELSIF v_hba_method = 'scram-sha-256' THEN
            IF v_storage_prefix != 'scram-sha-256' THEN
                v_level := 'CRITICAL';
                v_detail := '存储(' || v_storage_prefix || ') 不兼容 scram-sha-256 认证';
                v_fix := '重置密码：ALTER USER ' || v_rec.rolname || ' PASSWORD ''<新密码>'';';
            END IF;
            
        ELSIF v_hba_method IN ('sm3', 'scram-sm3') THEN
            IF v_storage_prefix NOT IN ('sm3', 'scram-sm3') THEN
                v_level := 'CRITICAL';
                v_detail := '非国密存储(' || v_storage_prefix || ') 不兼容国密认证';
                v_fix := '重置密码为国密格式';
            END IF;
        END IF;

        -- 检查密码过期
        IF v_rec.rolvaliduntil IS NOT NULL AND v_rec.rolvaliduntil < NOW() THEN
            v_level := 'CRITICAL';
            v_detail := v_detail || '；密码已过期';
            v_fix := v_fix || '；ALTER USER ' || v_rec.rolname || ' VALID UNTIL ''infinity'';';
        END IF;

        user_name := v_rec.rolname;
        storage_algo := v_rec.storage_algo;
        auth_method := COALESCE(v_hba_method, 'N/A');
        issue_level := v_level;
        issue_detail := v_detail;
        fix_suggestion := v_fix;
        
        RETURN NEXT;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

A.3 使用示例

-- 全库健康巡检（只看问题用户）
SELECT * FROM fn_check_auth_compatibility() 
WHERE issue_level IN ('CRITICAL', 'WARNING');

-- 定向排查特定用户
SELECT * FROM fn_check_auth_compatibility('gov_finance');

A.4 输出解读

字段说明
issue_levelCRITICAL（必须修复）/ WARNING（建议关注）/ INFO（仅供参考）
storage_algo用户的密码存储算法
auth_methodHBA中配置的认证方法
fix_suggestion可直接执行的修复命令
avatar
文章
阅读
粉丝