13.5 安全、测试与观测

数据库例程一旦拥有底表或管理能力，就同时是：

• 可执行代码；
• SQL API；
• 权限边界；
• 查询计划输入；
• 写事务的一部分；
• 生产观测对象。

因此评审标准不能停在“函数能调用、trigger 会触发”。本节把安全、测试和 观测合并，因为三者都在回答同一个问题：运行态是否真的是我们声明的对象。

13.5.1 SECURITY DEFINER、固定 search_path 与最小权限

invoker 与 definer

默认 SECURITY INVOKER：

function uses caller privileges

SECURITY DEFINER：

function uses owner privileges

后者可以给应用一个窄能力，而不授予底表权限：

pg36_app:
  no SELECT/UPDATE on shop_ch13.sales_order
  no INSERT on shop_ch13.payment
  EXECUTE capture_payment(...)

capture_payment owner:
  pg36_owner NOLOGIN
  owns only intended database objects

这比把 pg36_owner grant 给应用安全得多，但前提是 function 本身无法被 劫持或滥用。

threat model：名字解析

危险函数：

CREATE FUNCTION admin.check_secret(...)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    SELECT ... FROM password_table ...;
END
$$;

如果运行时 search_path 先命中调用者可写 schema 或临时关系，攻击者可以 创建同名对象，让 definer 权限访问错误目标。函数、operator、type 和隐式 cast 的解析也可能成为入口。

官方 Writing SECURITY DEFINER Functions Safely 要求排除不可信可写 schema，并把 pg_temp 放在可信路径最后。

本章使用：

SECURITY DEFINER
SET search_path = pg_catalog, pg_temp

并在 body 中全限定业务对象：

UPDATE shop_ch13.sales_order ...
INSERT INTO shop_ch13.payment ...

pg_catalog 明确位于前面，pg_temp 明确位于最后；没有 public 或应用可写 schema。

固定 path 还不够

逐项检查：

1. 所有 table/view/sequence/function/operator/type 是否解析到可信 owner；
2. 动态 SQL 的 identifier 是否来自 allowlist，并用 %I；
3. value 是否通过 USING 绑定，不拼接；
4. 是否调用可被不可信角色替换的同名重载；
5. 临时对象能否遮蔽未限定 relation；
6. 默认参数表达式是否依赖不可信对象；
7. function owner 能否被低权限用户 SET ROLE；
8. owner 是否拥有超出需求的 cluster 能力。

本章 owner 是：

pg36_owner:
  NOLOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS

NOLOGIN 阻止它成为应用连接身份；但能 SET ROLE pg36_owner 的成员仍等于 拥有其能力，membership 必须受控。

创建时立即撤销 PUBLIC

新 function 默认可能给 PUBLIC EXECUTE。若先创建、稍后再 revoke，中间 存在可调用窗口。把 DDL 与 ACL 放在一个事务：

BEGIN;

CREATE FUNCTION shop_ch13.transition_order(...)
...
SECURITY DEFINER;

REVOKE ALL ON FUNCTION
    shop_ch13.transition_order(bigint, bigint, text, text)
FROM PUBLIC;

GRANT EXECUTE ON FUNCTION
    shop_ch13.transition_order(bigint, bigint, text, text)
TO pg36_app;

COMMIT;

本章 setup 最终执行：

REVOKE ALL ON ALL FUNCTIONS IN SCHEMA shop_ch13 FROM PUBLIC;

GRANT EXECUTE ON FUNCTION order_snapshot(bigint) TO pg36_app;
GRANT EXECUTE ON FUNCTION transition_order(...) TO pg36_app;
GRANT EXECUTE ON FUNCTION capture_payment(...) TO pg36_app;

内部 trigger functions 和 maintenance procedure 不授给应用。

参数不是授权

危险接口：

admin.run_sql(command text)
admin.read_table(schema_name text, table_name text)
admin.set_role(role_name text)

即使用 %I 防注入，调用者仍可能选择不应访问的合法对象。安全接口必须 收窄业务能力：

transition_order(order_id, expected_version, target_status, actor)

body 自己决定：

• 只写哪张表；
• 允许哪些边；
• 取得什么锁；
• version 如何推进；
• 返回哪些列；
• 哪些 SQLSTATE 暴露。

“防 SQL injection”只是必要条件，不等于授权正确。

输入与资源预算

definer function 应限制：

• identifier 长度与字符集；
• array/JSON 最大大小；
• batch size；
• 正则或全文检索复杂度；
• 可查询时间范围；
• 动态 identifier 集合；
• statement/lock timeout；
• 单次返回行数。

本章 actor：

IF p_actor IS NULL
   OR p_actor !~ '^[A-Za-z0-9][A-Za-z0-9._:@/-]{0,63}$' THEN
    RAISE ... ERRCODE = 'P3617';
END IF;

actor 仍不是认证机制；它只保证安全形状。可信服务必须从已认证上下文生成， 而不是把任意用户输入原样传入。

RLS 不是自动叠加

table owner 通常绕过 row-level security，除非 FORCE ROW LEVEL SECURITY； superuser 和 BYPASSRLS 也有特殊能力。definer function 以 owner 运行时， 不能假设 caller 的 RLS policy 继续隔离行。

若 command API 需要 tenant isolation：

• 显式把 tenant identity 绑定到可信 session/参数；
• 在 body 的每条 SQL 中加入 tenant predicate；
• 评审 owner 与 FORCE ROW LEVEL SECURITY；
• 测试跨 tenant 读取、更新和错误差异；
• 防止通过存在性、timing 或错误字段泄露其他 tenant。

“底表有 RLS”不是 definer function 的完整安全证明。

trigger function 也是代码入口

应用通常不会直接调用 trigger function，但：

• trigger 创建者需要相应权限；
• function source 仍可能被替换；
• function owner 和 path 决定运行能力；
• 其他表可能误挂同一 trigger function；
• 默认 PUBLIC EXECUTE 仍扩大无意义攻击面。

所以本章也 revoke 内部函数 direct execute，并冻结：

trigger name
parent table
function regprocedure
SECURITY DEFINER
search_path
marker

安全目录测试

security-catalog.sql 验证：

app_schema_usage=true
app_order_select=false
app_order_update=false
app_payment_insert=false
app_snapshot_execute=true
app_transition_execute=true
app_capture_execute=true
app_guard_execute=false
app_procedure_execute=false
public_transition_execute=false

ACL 是发布 artifact，不是手工配置备注。

13.5.2 单元测试、属性测试与并发测试

测试从目录到事务逐层增加

1. DDL/目录合同

验证：

• exact signature 与 prokind；
• language、volatility、strict、parallel；
• prosecdef 与 proconfig；
• trigger event/timing/level；
• deferred、transition table；
• owner、ACL、marker；
• pg_get_functiondef() / pg_get_triggerdef() 与 release source。

这能发现“装错对象”，不能证明业务行为。

2. 纯函数单元测试

对 transition matrix 枚举所有状态对；实验由 transition-matrix.sql 固化：

WITH state(value) AS (
    VALUES
      ('created'), ('paid'), ('packing'), ('shipped'),
      ('completed'), ('canceled'), ('expired')
)
SELECT
    old.value,
    new.value,
    shop_ch13.allowed_transition(old.value, new.value)
FROM state AS old
CROSS JOIN state AS new
ORDER BY 1, 2;

断言允许边恰好是六条，反向边和 terminal outward 全部 false。对纯函数， 这种穷举 property test 比几个 happy example 更强。

3. command 正负路径

成功：

created v0 -> canceled v1
created v0 + exact payment -> paid v1

失败：

created -> shipped        -> P3613
paid without payment      -> P3614 at commit
delete captured payment   -> P3614 at commit
expected v0 after v1      -> P3616
wrong payment amount      -> P3618
direct app UPDATE         -> 42501

每个失败都同时断言：

• order status/version unchanged；
• payment count unchanged；
• history/audit unchanged；
• transaction can only continue when error is intentionally caught in a subtransaction。

只检查“报错了”不够；错误前的隐藏写也必须回滚。

4. trigger 粒度测试

单条三行 UPDATE：

BEFORE ROW calls = 3
history rows     = 3
AFTER STATEMENT  = 1
affected_count   = 3
order_ids        = {105,106,107}

再测试零行 UPDATE，确认 statement trigger 是否执行以及 body 是否避免写空 audit。

5. deferral 测试

在同一事务中分别执行：

INSERT captured payment;
UPDATE order TO paid;
SET CONSTRAINTS ALL IMMEDIATE;

应通过。只做其中一步应在 SET CONSTRAINTS 或 commit 时报 P3614。这能 区分“语句成功”与“事务可提交”。

6. exception 子事务

exception-probe.sql 精确捕获 P3613， 使用 GET STACKED DIAGNOSTICS，并证明 inner persistent change 回滚。

7. procedure 事务边界

同一 fixture 先运行：

BEGIN;
CALL expire_stale_orders(...);
COMMIT;

必须是 2D000 且候选仍为 created。再以 top-level CALL 运行，取得 2/2/1 与 total 5；第二次 CALL 必须取得 total 0 且 audit 不增长。

以真实角色测试

owner 测试不能证明应用 ACL。实验分别建立连接：

admin connection:
  session_user=postgres
  SET ROLE pg36_owner

application connection:
  session_user=pg36_app
  no SET ROLE

正向 API 和直接写拒绝必须在 application connection 运行。测试 DSN 不应 因为本机 trust 就被误认为生产认证已验证。

绕过应用是必测路径

如果 trigger 声称覆盖所有普通写入口，测试必须直接：

SET ROLE pg36_owner;
UPDATE shop_ch13.sales_order
SET status = 'shipped', version = version + 1
WHERE order_id = 103;

它绕过 command function，仍应收到 P3613。只从应用 API 测 trigger， 无法区分是应用校验还是数据库护栏生效。

并发属性

至少覆盖：

1. 两个 command 使用同一 expected version；
2. 两个支付引用争同一订单；
3. 相反顺序锁多张表是否 deadlock；
4. deferred aggregate 在并发明细下是否遗漏；
5. procedure 与在线命令争同一行时 SKIP LOCKED 是否可恢复；
6. function 在 READ COMMITTED / REPEATABLE READ / SERIALIZABLE 的错误集合；
7. cancel/timeout 后锁、连接和事务是否释放。

本章 deterministic suite 证明单订单 FOR UPDATE 与 optimistic version 合同，但没有声称覆盖生产并发规模。对真实模型应沿用第 10 章 gate worker 方法，保存 PID、backend_start、application_name、wait graph 和 SQLSTATE。

property 不只测输入

可冻结的关系：

sum(order versions) = history rows
sum(statement_audit.affected_count) = history rows
paid orders = orders with exact captured total
terminal statuses have no outgoing history edge
failed cases add zero durable rows
rerun procedure processes zero already-expired rows
business checksum stable across exact rebuild

这种关系比 identity sequence 恰好连续或耗时固定更耐环境变化。

migration 与 rollback 测试

例程发布还要验证：

• CREATE OR REPLACE 是否保持 OID/ACL/依赖和返回类型限制；
• 新旧签名是否同时存在并产生重载歧义；
• trigger 新旧版本是否会重复执行；
• 回退应用调用旧签名是否仍成功；
• drop 前是否还有依赖和活跃调用；
• reset 是否只作用于 marker 对象。

本章 reset 对错误 token、错误 target、活跃 worker 和对象 inventory 漂移 全部 fail closed。

13.5.3 函数级统计、日志与慢调用定位

track_functions

track_functions 控制用户函数累计统计：

开启有开销，应按观察目标和窗口决定。需要相应权限修改；生产上通过受控 配置流程，而不是应用连接临时打开。

累计视图：

SELECT
    schemaname,
    funcname,
    calls,
    total_time,
    self_time
FROM pg_stat_user_functions
WHERE schemaname = 'shop_ch13'
ORDER BY total_time DESC;

• total_time 包含被调函数时间；
• self_time 排除被调函数时间；
• 数值是累计量，不是分位数；
• stats 有 flush 延迟，并受 transaction 内 snapshot/cache 影响；
• restart、crash 或显式 stats reset 会影响统计连续性；PostgreSQL 18 的 pg_stat_user_functions 本身不提供每行 stats_reset 列，观察系统要另行 记录采集窗口。

当前事务可看：

SELECT *
FROM pg_stat_xact_user_functions;

本章在一笔 rollback-only probe 中打开 all，调用 snapshot 和 transition， 取得五个 routine 的 calls >= 1，然后回滚业务变化。官方定义见 Cumulative Statistics System。

function counters 不能回答什么

它们不能直接给出：

• p95/p99；
• 哪个 request 调用；
• 参数值；
• 哪条内部 SQL 最慢；
• 哪个 call 失败；
• lock/wait 分解；
• SQL function 内联后的完整逻辑边界。

所以它是定位入口，不是 trace。

把外层与内部 SQL 关联

组合：

application_name + trace/request id
  -> outer SELECT function(...)
  -> pg_stat_activity / wait_event
  -> pg_stat_statements
  -> nested statement stats/log
  -> function counters
  -> SQLSTATE + trigger context
  -> business audit/outbox

pg_stat_statements.track = all 可纳入嵌套语句，但会改变数据量；需按目标 配置验证。auto_explain.log_nested_statements 可在有界诊断窗口记录嵌套 计划，同样要控制 duration、sample rate、buffers 与日志敏感性。

不要长期把所有参数和完整 PL/pgSQL context 无筛选写日志。订单引用、用户 标识、token、payload 可能是敏感信息。

慢 routine 的诊断顺序

1. 确认目标 cluster/database/schema/signature；
2. 区分 outer call 慢还是在 pool/lock 等待；
3. 看 pg_stat_activity.state/wait_event；
4. 看 block graph 与长事务；
5. 对内部 SQL 取得规范化 query identity；
6. 用实际参数分布 EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)；
7. 检查 row-trigger 放大与 transition table 大小；
8. 检查 deferred queue 是否在 commit 集中爆发；
9. 比较 function total_time/self_time；
10. 最后才改 SQL、索引、batch 或逻辑位置。

不要看见高 total_time 就重写 PL/pgSQL。总时间可能只是调用次数高，或内部 SQL 在锁上等待。

trigger 的可见性

原始 query：

UPDATE sales_order SET ...

不会把所有 trigger body 展开在 pg_stat_activity.query。需要：

• pg_trigger inventory；
• function stats；
• nested statement statistics/logging；
• SQLSTATE context；
• derived audit relationship；
• 应用端命令与数据库 transaction ID 关联。

本章 statement audit 保存 pg_current_xact_id()，history 保存同一 xid8。 这是数据库内关联，不是全链路 trace。

Pigsty 观察面

Pigsty monitoring 以 metrics、logs、alerting 为三根支柱，并覆盖 PostgreSQL 实例、SQL、连接、复制、WAL 和基础设施。见 Monitoring System。

例程上线时至少增加或确认：

• command function rate/error by low-cardinality identity；
• SQLSTATE rate；
• function cumulative calls/time delta；
• outer SQL latency；
• lock/wait；
• job backlog/age/last success；
• audit/outbox growth；
• database/replica/WAL/connection resource；
• deployment/release annotation。

不要把 actor、order_id 或 function 参数做成 metrics label；高基数和敏感性 都不合适。它们应进入受控日志或数据库 evidence。

观察窗口

发布后按阶段：

catalog/ACL verified
  -> canary command
  -> negative path
  -> representative bulk
  -> lock/WAL/replica observation
  -> enable production callers
  -> watch one workload cycle
  -> only then remove old path

本地 suite 无法伪造生产 observation window。自动 review 应输出 “not observed”，而不是因为 unit test 通过就填绿。

本节安全门禁

进入发布前必须同时满足：

• owner NOLOGIN、非 superuser、能力最小；
• definer path 可信且 pg_temp 最后；
• source 中对象名和 dynamic SQL 已审计；
• PUBLIC 权限在同事务撤销；
• application ACL matrix 精确；
• 正向、负向、绕过应用、deferral、bulk、procedure 边界通过；
• 并发协议有实际 evidence 或明确未验证；
• function/trigger inventory 已冻结；
• metrics/log/alert 查询可执行；
• rollback 会先停调用者和 job，再处理对象；
• evidence 不包含 secret；
• 本地事实与 Pigsty/PgBouncer 事实没有混写。

安全、测试和观测缺一项，数据库端逻辑都还只是“能运行”，不是“可运营”。

上一节：过程、任务与事务控制 · 返回本章目录 · 下一节：实战：为订单状态建立数据库端护栏 · 查看全书目录 · 查看索引中心