kingbase-sql-audit

# kingbase-sql-audit：KingbaseES（金仓）SQL 上线前审查 对即将上线的金仓 SQL（DML/DDL/DCL/函数/存储过程/触发器）做严格的合规与风险审查，产出结构化、可执行的审查报告。本技能是 pg-sql-audit 的金仓移植版，默认假设目标实例运行在 **PG 兼容模式（`database_mode=pg`）**，本技能的全部脚本与 SQL 已在该模式下实测通过（KingbaseES V009R001C010 验证）。 ## 与 PostgreSQL 的兼容性说明（金仓差异，务必先读） KingbaseES 默认采用 PG 兼容模式，因此本技能使用的系统目录/视图与 PostgreSQL 完全一致，可直接复用：`pg_stat_user_tables`、`pg_stat_activity`、`pg_locks`、`pg_constraint`、`pg_indexes`、`pg_depend`、`pg_trigger`、`pg_roles`、`pg_hba_file_rules` 等。以下为金仓特有差异，审查时必须注意： | 项目 | PostgreSQL | KingbaseES（本技能按此执行） | |---|---|---| | 统计语句视图 | `pg_stat_statements` | `sys_stat_statements`（金仓版，位于 `public` schema，实测可查） | | 客户端认证文件 | `pg_hba.conf` | `sys_hba.conf`（用 `SHOW hba_file;` 确认实际路径） | | 主配置文件 | `postgresql.conf` | `kingbase.conf`（用 `SHOW config_file;` 确认） | | EXPLAIN BUFFERS | 需配合 ANALYZE | 同左：`EXPLAIN (..., BUFFERS)` 必须配 ANALYZE 否则报错。审查禁止 ANALYZE，因此执行计划一律用 `EXPLAIN (COSTS, VERBOSE, FORMAT TEXT)`（无 BUFFERS） | | 只读会话兜底 | `SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY` | 语法兼容，但实测**该语句不影响"当前事务"**；必须在显式事务内用 `SET TRANSACTION READ ONLY` 才真正生效。本技能脚本一律采用 `BEGIN; SET TRANSACTION READ ONLY; ...; ROLLBACK;` | | 扩展 | pg_trgm 等 | **V9R1C10 默认不提供 pg_trgm**（`pg_available_extensions` 实测无），`LIKE '%xxx'` 的优化方案需调整（见 `references/review_dimensions.md` 维度1） | | 在线建索引 | `CREATE INDEX CONCURRENTLY` | 实测支持，但同样**不能在事务块内执行**，必须单独会话/单独 psql -c | | 其他 | | `CREATE INDEX CONCURRENTLY`、`ADD CONSTRAINT ... NOT VALID` + `VALIDATE CONSTRAINT`、`DO $$ ... $$` plpgsql、`pg_sleep`、`GET DIAGNOSTICS ROW_COUNT` 均已实测兼容 | ## 核心原则 1. **只读、零风险**：审查过程中对目标库的所有连接与查询必须只读，绝不执行任何会修改数据/结构的操作，绝不使用 `EXPLAIN ANALYZE`。 2. **先问频率，再定级**：如果用户未提供 SQL 的调用频率/执行场景（OLTP 高频 / 一次性报表 / 后台批处理），必须先暂停并询问，缺失该信息无法准确分级风险。 3. **可执行优先**：每一条风险发现都必须给出可直接复制使用的修正 SQL 和回滚方案，而不是泛泛而谈。 4. **中文输出**：报告全程使用中文，SQL 代码块保持规范格式化。 ## 连接约定 按以下优先级解析连接参数（脚本已内置该逻辑，无需手工逐项传递）： 1. 用户明确提供的连接参数（host/port/user/password/dbname）； 2. 环境变量 `PGHOST` `PGPORT` `PGUSER` `PGPASSWORD` `PGDBNAME`（`PGDATABASE` 作为 `PGDBNAME` 的等价别名）。脚本同时兼容金仓手册风格的 `KINGBASEHOST`/`KINGBASE_HOST`/`KINGBASEPORT`/`KINGBASE_PORT`/`KINGBASEUSER`/`KINGBASE_USER`/`KINGBASEPASSWORD`/`KINGBASE_PASSWORD` 等变体作为兜底； 3. 缺省值：`PGHOST=127.0.0.1 PGPORT=5432 PGDBNAME=kingbase PGUSER=kingbase PGPASSWORD=123456`。 ```bash export PGHOST=127.0.0.1 PGPORT=5432 PGDBNAME=kingbase PGUSER=kingbase PGPASSWORD=123456 ``` > 密码在 shell 通道**只能**通过环境变量 `PGPASSWORD` 传递，禁止在命令行、日志、报告中出现明文密码；python 通道可用 `--password` 参数，但同样推荐使用 `PGPASSWORD`。 ## 前置要求 - 可用的 PostgreSQL 兼容客户端，二选一： - `psql`（实测 PG 的 psql 可直接连接金仓 V9）； - Python `psycopg2` 或 `psycopg[binary]`（psycopg3），脚本优先用 psycopg2，找不到时自动降级 psycopg3。 - 需要用户提供：目标库主机、端口、用户名、数据库名、待审查 SQL 文本 - 需要的数据库权限：能够 `SELECT` 系统视图（`pg_stat_user_tables`、`pg_stat_activity`、`pg_locks`、`pg_constraint`、`pg_depend` 等）及对目标表有只读权限；不需要写权限 ## 工作流程 ### Step 0：信息完整性检查 在开始任何审查前，确认以下信息齐全： - [ ] 数据库连接信息（host/port/user/dbname），密码通过 `PGPASSWORD` 环境变量单独获取 - [ ] 待审查 SQL 文本（可以是文件或直接粘贴） - [ ] **调用频率/执行场景**：若缺失，必须停止分析并提问，例如： > "这几条 SQL 的执行场景是什么？是 OLTP 高频调用（比如接口里每次请求都会跑）、一次性报表查询，还是后台批处理任务？调用频率大概是多少（QPS/每天次数）？" 收到回复后再继续，频率将直接影响索引建议的强度和风险等级判定（详见 `references/review_dimensions.md` 维度1）。 ### Step 1：连接目标库，采集环境背景 使用只读采集脚本（**psql 通道或 python SDK 通道二选一，结果等价**）： ```bash # psql 通道 PGPASSWORD=<密码> ./scripts/collect_context.sh \ -h <host> -p <port> -U <user> -d <dbname> \ -t "schema1.table1,schema1.table2" # python SDK 通道（psycopg2/psycopg3，连接参数不传时按连接约定自动解析） ./scripts/collect_context.py \ --host <host> --port <port> --user <user> --dbname <dbname> \ --tables "schema1.table1,schema1.table2" ``` 该脚本会依次采集： - 实例版本、`database_mode`（期望 `pg`）、`hba_file`/`config_file`、`statement_timeout`/`lock_timeout`/`idle_in_transaction_session_timeout` 等关键参数 - 涉及表的 `pg_stat_user_tables` 统计（行数、死元组、最后 ANALYZE/VACUUM 时间）、表大小 - 涉及表的索引清单、外键依赖（正向+反向）、依赖的视图/物化视图、触发器定义 - `sys_stat_statements`（金仓版 pg_stat_statements）可用性与已记录语句量 - 当前长事务/未提交事务、当前锁等待情况 所有查询均包裹在 `BEGIN; SET TRANSACTION READ ONLY; ...; ROLLBACK;` 只读事务中执行（python 通道另加 `SET default_transaction_read_only = on` 会话兜底），不改变任何数据。 ### Step 2：解析 SQL，识别类型 将用户提供的 SQL 按分号拆分为独立语句，逐条识别类型： - `SELECT`/`INSERT`/`UPDATE`/`DELETE`/`MERGE`/`WITH`（可 EXPLAIN） - `ALTER`/`CREATE`/`DROP`/`GRANT`/`REVOKE`/`COMMENT`/`TRUNCATE`（DDL/DCL，转人工审查，不可 EXPLAIN） - 函数/存储过程定义（`CREATE [OR REPLACE] FUNCTION/PROCEDURE`） - 触发器定义（`CREATE TRIGGER`） > 注意：函数/触发器体内部含分号时，按分号切分会把函数体切碎，需在喂给脚本前人工把函数/触发器定义拆成独立文件或单独处理。 ### Step 3：执行计划分析（仅 DML） 对可 EXPLAIN 的语句，使用 explain 脚本（psql 或 python 通道）： ```bash # psql 通道 PGPASSWORD=<密码> ./scripts/explain_check.sh \ -h <host> -p <port> -U <user> -d <dbname> -f <sql_file> # python SDK 通道 ./scripts/explain_check.py \ --host <host> --port <port> --user <user> --dbname <dbname> -f <sql_file> ``` 该脚本对每条 DML 语句在 `BEGIN; SET TRANSACTION READ ONLY; SET LOCAL statement_timeout='30s'; EXPLAIN (COSTS, VERBOSE, FORMAT TEXT) <SQL>; ROLLBACK;` 中执行，**绝不加 `ANALYZE`、绝不加 `BUFFERS`、绝不提交**。 拿到执行计划后，按 `references/review_dimensions.md` 维度 1 的标准判断： - 是否全表扫描、Join 顺序是否合理、是否磁盘排序 - 索引是否因隐式类型转换/函数包裹列而失效 - 结合 Step 0 采集的调用频率，决定索引建议的强度（高频必须给具体 `CREATE INDEX CONCURRENTLY` 建议；低频仅提示） ### Step 4：逐维度审查 对每条 SQL，逐一核对以下七个维度（详细判断标准、模板 SQL 见 `references/review_dimensions.md`）： 1. **执行计划与索引分析** — 全表扫描、索引失效、Join 顺序 2. **DDL 安全与锁风险** — 是否全表重写、是否设置了超时保护、是否用了 `CONCURRENTLY`、依赖对象影响 3. **开发者规范** — 禁止 `SELECT *`、批量操作分批、事务边界是否清晰 4. **回退机制** — 每条 DDL 给出精确回滚语句；DML 建议事务内执行+快照备份 5. **SQL 注入风险** — 是否存在拼接迹象；函数/存储过程内动态 SQL 是否用 `quote_ident`/`quote_literal`/`format`+`USING` 6. **触发器安全性**（若涉及）— 触发时机、级联触发、递归风险、批量场景性能放大 7. **高级环境关联风险** — 统计信息是否过时、长事务冲突、复制延迟、资源消耗 对每一项发现，按风险等级归类：🔴 严重 / 🟠 警告 / 🟡 建议 / 🟢 通过。 ### Step 5：生成结构化报告 使用 `assets/report_template.md` 作为输出模板，填充： - 总体评估（风险评分 1-10、是否建议上线、上线前提条件） - 按风险等级分类的发现清单（每条含：SQL 编号摘要、风险描述、后果、修正 SQL、回滚建议、是否高频敏感） - 逐条 SQL 的七维度详情 - 待确认事项（若审查中仍有信息缺口） ## 输出要求 - 报告语言：中文 - 所有建议 SQL 必须是可直接复制执行的完整语句，不使用占位符省略关键部分 - 每条 DDL 必须配一条回滚语句（或明确说明"无法回滚，需提前备份"及备份语句） - 涉及应用层拼接 SQL 的场景，必须原样提出 `references/review_dimensions.md` 维度5 中的确认话术 ## 执行约束（绝对遵守） - 所有分析仅使用 `EXPLAIN (COSTS, VERBOSE, FORMAT TEXT)`（不加 `ANALYZE`、不加 `BUFFERS`）和系统目录/视图只读查询，绝不执行任何 DDL/DML/DCL - 密码只能通过 `PGPASSWORD` 环境变量（或 python `--password`）传递；不接受、不记录、不回显明文密码 - 采集脚本与 EXPLAIN 脚本均在只读事务（`BEGIN; SET TRANSACTION READ ONLY; ...; ROLLBACK;`）中执行，双重防止意外提交 - 缺乏调用频率信息时必须先暂停提问，不得臆测频率直接给结论 - 不删除、不修改用户提供的原始 SQL 文件 ## Pitfalls & Solutions | 坑点 | 现象 | 解决方案 | |---|---|---| | EXPLAIN 报错缺少参数 | SQL 中包含应用层占位符（如 `$1`、`:param`）导致语法错误 | 提示用户提供具体示例参数值，或用假设值替换后再 EXPLAIN，并在报告中注明"使用示例参数估算" | | `EXPLAIN (..., BUFFERS)` 报错 | 金仓与 PG 一样：`EXPLAIN option BUFFERS requires ANALYZE` | 一律去掉 BUFFERS，用 `EXPLAIN (COSTS, VERBOSE, FORMAT TEXT)`；如确实需要 buffer 统计，提示用户可另行在低峰期手工 `EXPLAIN (ANALYZE, BUFFERS)` | | `SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY` 后写入仍成功 | 该语句只影响"之后开启的事务"，psql 多 `-c` 或同一隐式事务内不生效（金仓实测） | 一律改用显式事务 `BEGIN; SET TRANSACTION READ ONLY; ...; ROLLBACK;`（本技能脚本已内置，实测可拦截 CREATE TABLE） | | DDL 语句无法 EXPLAIN | `ALTER TABLE` 等不支持 EXPLAIN | 转入人工审查环节（维度2），基于表大小和统计信息估算耗时 | | 大表统计信息过时导致计划失真 | `last_analyze` 很久之前 | 在报告中标注"执行计划可能不准，建议先 ANALYZE"，不要直接采信计划里的行数估算 | | 触发器/函数定义中包含动态 SQL 或分号 | 普通 EXPLAIN 无法覆盖内部逻辑；shell 脚本按分号切分会把函数体或字符串字面量（如 `VALUES ('a;b')`）切碎 | 单独解析函数体文本，人工核查 `EXECUTE` 部分的拼接方式（维度5/6）；函数/触发器定义或含分号的字符串字面量需人工拆分后再喂给脚本（python 通道对单引号/双引号/`$$` 有感知，优先使用） | | SQL 文件含 `/* */` 块注释 | 两个 explain 脚本都只剥离整行 `--` 注释，块注释会影响首语句识别 | 先人工清理块注释，或用 `psql -f` 之外的路径由 python 通道处理后再交给人工复核 | | `sys_stat_statements` 查询报错 | 实例未启用该扩展（需在 kingbase.conf 的 `shared_preload_libraries` 加入 `sys_stat_statements` 并重启） | 记为"环境负载画像不可用"，降级为仅系统视图结论，不影响其余审查 | | 用户按金仓手册习惯给 `KINGBASEHOST`/`KINGBASE_PORT` 环境变量 | 非标准变量 | 脚本已兼容 KINGBASE* 变体；报告中统一用 `PGHOST/PGPORT/...` 表述，金仓连接协议与 PG 完全兼容 | | 用户只给了 SQL 没给连接信息 | 无法采集环境背景 | 仍可基于 SQL 文本做静态审查（规范、注入风险、回滚方案），但需明确告知"因缺少连接信息，执行计划/锁风险/依赖分析部分无法完成" |