# 安全、测试与观测

LLMS 索引： [llms.txt](/llms.txt)

---

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

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

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

## 13.5.1 `SECURITY DEFINER`、固定 `search_path` 与最小权限 {#item-13-5-1}

### invoker 与 definer

默认 `SECURITY INVOKER`：

```text
function uses caller privileges
```

`SECURITY DEFINER`：

```text
function uses owner privileges
```

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

```text
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：名字解析

危险函数：

```plpgsql
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](https://www.postgresql.org/docs/18/sql-createfunction.html)
要求排除不可信可写 schema，并把 `pg_temp` 放在可信路径最后。

本章使用：

```sql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
```

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

```sql
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 是：

```text
pg36_owner:
  NOLOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS
```

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

### 创建时立即撤销 `PUBLIC`

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

```sql
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 最终执行：

```sql
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 不授给应用。

### 参数不是授权

危险接口：

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

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

```sql
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：

```plpgsql
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，并冻结：

```text
trigger name
parent table
function regprocedure
SECURITY DEFINER
search_path
marker
```

### 安全目录测试

[security-catalog.sql](/labs/ch13/security-catalog.sql) 验证：

```text
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 单元测试、属性测试与并发测试 {#item-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](/labs/ch13/transition-matrix.sql) 固化：

```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 正负路径

成功：

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

失败：

```text
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：

```text
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 测试

在同一事务中分别执行：

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

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

#### 6. exception 子事务

[exception-probe.sql](/labs/ch13/exception-probe.sql) 精确捕获 `P3613`，
使用 `GET STACKED DIAGNOSTICS`，并证明 inner persistent change 回滚。

#### 7. procedure 事务边界

同一 fixture 先运行：

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

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

### 以真实角色测试

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

```text
admin connection:
  session_user=postgres
  SET ROLE pg36_owner

application connection:
  session_user=pg36_app
  no SET ROLE
```

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

### 绕过应用是必测路径

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

```sql
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 不只测输入

可冻结的关系：

```text
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 函数级统计、日志与慢调用定位 {#item-13-5-3}

### `track_functions`

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

| 值 | 含义 |
|---|---|
| `none` | 不跟踪，默认 |
| `pl` | 跟踪过程语言函数 |
| `all` | 也跟踪 SQL/C 函数 |

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

累计视图：

```sql
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` 列，观察系统要另行
  记录采集窗口。

当前事务可看：

```sql
SELECT *
FROM pg_stat_xact_user_functions;
```

本章在一笔 rollback-only probe 中打开 `all`，调用 snapshot 和 transition，
取得五个 routine 的 `calls >= 1`，然后回滚业务变化。官方定义见
[Cumulative Statistics System](https://www.postgresql.org/docs/18/monitoring-stats.html)。

### function counters 不能回答什么

它们不能直接给出：

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

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

### 把外层与内部 SQL 关联

组合：

```text
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：

```sql
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](https://pigsty.io/docs/concept/monitor/)。

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

- 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。

### 观察窗口

发布后按阶段：

```text
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 事实没有混写。

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

---

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