# 实战：建立 `pg36_shop` 地图与实验基线

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

---

现在把前六节的地图落到一个真实对象上。本实验会在 L1 沙箱创建 `pg36_shop` 数据库、`shop` 模式和三个专用角色，随后从 PostgreSQL 与 Pigsty 两侧收集证据。

实验分为两个风险级别：

- `setup` 与快照采集：`R1·可逆变更`，只创建以 `pg36_` 命名的教学对象；
- `reset:sql`：`R2·破坏性演练`，会删除整个 `pg36_shop` 数据库，只能在确认可销毁的 L1 中执行。

准备两个不含密码的连接 URI。将 `<L1_HOST>` 替换为实际域名或 IP；密码由 `psql` 询问或使用 ch02 将介绍的安全凭据机制：

```bash
export PG36_BOOTSTRAP_URL='postgresql://dbuser_dba@<L1_HOST>:5436/postgres?application_name=pg36-ch01-admin'
export PG36_SHOP_ADMIN_URL='postgresql://dbuser_dba@<L1_HOST>:5436/pg36_shop?application_name=pg36-ch01-admin'
```

这里使用 `5436` default 服务，目的是跟随主库且绕过事务连接池执行管理脚本。若你的环境修改了 Pigsty 默认服务，请根据实际配置替换，不能照抄端口猜路径。

## 1.7.1 创建数据库、业务模式和最小角色 {#item-1-7-1}

本章只建立权限骨架，不创建订单、商品或支付表：

| 角色 | 是否登录 | 责任 |
|---|---:|---|
| `pg36_owner` | 否 | 拥有数据库和模式；迁移时由受控管理会话 `SET ROLE` 使用 |
| `pg36_app` | 是 | 应用运行角色；只获得 `shop` 中未来业务对象的读写权限 |
| `pg36_ro` | 是 | 只读角色；只获得 `shop` 中未来业务对象的读取权限 |

对象所有者使用 `NOLOGIN`，避免应用直接以所有者身份绕过授权边界。两个登录角色在本章故意不设置密码；这既避免在教程中分发固定密码，也使它们在默认密码认证规则下暂时无法远程登录。ch02 会为连接与凭据建立正式工作流。

下载或打开三份伴随实验文件：

- [`setup.sql`](/labs/ch01/setup.sql)：幂等创建角色、数据库、模式和默认权限；
- [`verify.sql`](/labs/ch01/verify.sql)：机器验证状态并输出摘要；
- [`reset.sql`](/labs/ch01/reset.sql)：带确认口令的实验清理。

执行初始化：

```bash
psql -X "$PG36_BOOTSTRAP_URL" \
  -v ON_ERROR_STOP=1 \
  -f setup.sql
```

执行身份为 `dbuser_dba` 或等价的实验管理员；目标必须是 L1 的 `postgres` 数据库。脚本会：

1. 仅在缺失时创建三个 `pg36_` 角色，并收敛高风险属性；
2. 仅在缺失时从 `template0` 创建 UTF-8 数据库；
3. 创建由 `pg36_owner` 拥有的 `shop` 模式；
4. 撤销 `public` 模式的公共建对象权限；
5. 为两个运行角色授予模式使用权和未来对象的默认权限；
6. 为 `pg36_shop` 中的两个运行角色设置 `pg_catalog, shop` 搜索路径。

成功末尾应出现：

```text
[setup] complete: roles intentionally have no password in this chapter
```

这条消息只证明脚本执行完毕，不能替代下一目的状态验证。

### 与 Pigsty 声明式配置对齐

SQL 能证明 PostgreSQL 对象机制，但 Pigsty 管理的长期环境还应把期望状态写入 inventory，避免下次自动化执行时出现配置漂移。最小声明可采用下面的结构；不要把实际明文密码直接写进公开配置：

```yaml
pg_users:
  - { name: pg36_owner, login: false, pgbouncer: false, comment: pg36_shop object owner }
  - { name: pg36_app,   login: true,  pgbouncer: false, comment: pg36_shop runtime role }
  - { name: pg36_ro,    login: true,  pgbouncer: false, comment: pg36_shop read-only role }

pg_databases:
  - name: pg36_shop
    owner: pg36_owner
    encoding: UTF8
    pgbouncer: true
    schemas:
      - { name: shop, owner: pg36_owner }
```

本章先将 `pgbouncer: false` 用于尚无凭据的两个登录角色；数据库本身可以加入连接池。设置正式认证材料后，再把用户加入 PgBouncer。若在既有集群中应用声明，应先评审差异，然后使用：

```bash
cd ~/pigsty
bin/pgsql-user pg-meta pg36_owner
bin/pgsql-user pg-meta pg36_app
bin/pgsql-user pg-meta pg36_ro
bin/pgsql-db pg-meta pg36_shop
```

这些是 `R1` 操作，目标集群名不一定是 `pg-meta`。运行前用 `ansible-inventory --graph` 确认限制范围；若清单里已经存在同名但含义不同的对象，立即停止，不要让自动化强行“收敛”。

## 1.7.2 生成连接快照、对象树、服务拓扑与环境清单 {#item-1-7-2}

建立证据目录：

```bash
mkdir -p evidence/ch01
```

### 连接快照

```bash
psql -X "$PG36_SHOP_ADMIN_URL" -A -t -v ON_ERROR_STOP=1 -c "
SELECT jsonb_build_object(
  'captured_at', clock_timestamp(),
  'database', current_database(),
  'session_user', session_user,
  'current_user', current_user,
  'server_addr', inet_server_addr(),
  'server_port', inet_server_port(),
  'backend_pid', pg_backend_pid(),
  'server_version', current_setting('server_version'),
  'search_path', current_setting('search_path'),
  'in_recovery', pg_is_in_recovery()
);" > evidence/ch01/connection.json
```

这里以管理员会话采集，因此 `search_path` 不会冒充 `pg36_app` 的角色级设置。角色设置由 `verify.sql` 直接查询目录验证。

### 对象树

```bash
psql -X "$PG36_SHOP_ADMIN_URL" > evidence/ch01/objects.txt <<'PSQL'
\pset pager off
\conninfo
\dn+
\du+ pg36_*
\d shop.*
PSQL
```

此时 `shop` 模式尚无业务关系，`\d shop.*` 返回“没有找到任何关系”是正确结果。对象树的目标是证明数据库、模式和角色边界，不是提前制造表。

### 服务拓扑与环境清单

在 Pigsty 管理节点执行：

```bash
cd ~/pigsty

{
  printf 'captured_at=%s\n' "$(date -Is)"
  printf 'node=%s\n' "$(hostname -f 2>/dev/null || hostname)"
  printf 'pigsty=%s\n' "$(git describe --tags --always 2>/dev/null || printf unknown)"
  printf '%s\n' '--- inventory ---'
  ansible-inventory --graph
  printf '%s\n' '--- runtime ---'
  pg list pg-meta
  printf '%s\n' '--- listening ports ---'
  ss -lnt | awk 'NR == 1 || $4 ~ /:(5432|5433|5434|5436|5438|6432)$/'
} > evidence/ch01/platform.txt
```

将 `pg-meta` 替换为实际集群名。`ss` 只证明端口正在监听，不证明后端角色、路由正确或 SQL 可用；它必须与 `pg list` 和连接快照合读。

### 版本清单

在客户端记录客户端与服务器版本：

```bash
{
  psql --version
  psql -X "$PG36_SHOP_ADMIN_URL" -A -t -c \
    "SELECT 'server=' || current_setting('server_version');"
} > evidence/ch01/versions.txt
```

客户端与服务端小版本不同不一定是错误，但必须留痕。涉及协议、元命令或版本特性的实验以实际版本为准。

## 1.7.3 建立 `verify:state` 与三档 `reset` {#item-1-7-3}

`verify:state` 不是“脚本没报错”的同义词。它从系统目录重新读取最终状态，检查对象所有者、角色、权限和数据库级搜索路径：

```bash
psql -X "$PG36_BOOTSTRAP_URL" \
  -v ON_ERROR_STOP=1 \
  -f verify.sql \
  | tee evidence/ch01/verify.txt
```

成功输出的值会因环境不同而变化，但必须包含：

```text
status=ok
database=pg36_shop
database_owner=pg36_owner
schema=shop
schema_owner=pg36_owner
in_recovery=false
```

在备库或错误路由上执行时，脚本不应被“修到能过”；先回到 1.1 确认为什么管理连接没有进入可写主库。

本书使用三档复位，它们按影响范围命名，不代表都要在每章执行：

| 复位 | 影响范围 | ch01 的实现 | 风险与使用条件 |
|---|---|---|---|
| `reset:sql` | 教学数据库、模式、角色和数据 | 删除 `pg36_shop` 与三个 `pg36_` 角色 | `R2`；仅限无保留价值的 L1 |
| `reset:cluster` | PostgreSQL 集群配置、成员和服务 | 本章不修改集群级状态，因此应为 no-op | 后续章节按变更提供；不能用“重装集群”替代诊断 |
| `reset:host` | 整台实验主机 | 回到第 0 章重建 L1 | `R2`；仅当主机基线已不可相信 |

执行 `reset:sql` 前必须同时满足：

- 当前是明确标识的可销毁 L1；
- `pg36_shop` 中没有需要保留的数据；
- 三个 `pg36_` 角色没有被其他数据库使用；
- `PG36_BOOTSTRAP_URL` 指向预期集群的主库管理服务；
- 已阅读 `reset.sql`，确认它没有被本地修改。

然后使用完整确认口令：

```bash
psql -X "$PG36_BOOTSTRAP_URL" \
  -v ON_ERROR_STOP=1 \
  -v confirm_reset=RESET_PG36_SHOP \
  -f reset.sql
```

脚本会先终止连接到 `pg36_shop` 的会话，再删除数据库与角色。这不是可回滚事务。未提供精确口令时脚本会拒绝执行。复位后重新运行 `setup.sql` 与 `verify.sql`，应得到新的数据库 OID；OID 改变正好说明它不能作为业务稳定标识。

## 1.7.4 验收：从 SQL 与 Pigsty 两侧指认同一对象 {#item-1-7-4}

最后把所有名字放回一张表。下面是结构，不是要求实际值与示例相同：

| 层次 | 示例 | 证据来源 |
|---|---|---|
| 客户端入口 | `<L1_HOST>:5436` | `PG36_SHOP_ADMIN_URL`、`\conninfo` |
| 平台服务 | `pg-meta-default` | Pigsty 服务定义、HAProxy 后端 |
| Pigsty 集群 | `pg-meta` | inventory、`pg list` |
| Pigsty 实例 | `pg-meta-1` | inventory、`pg list` |
| 节点 | `<IP 或主机名>` | inventory、主机事实 |
| PostgreSQL 后端 | 某个 `pid`、地址、`5432` | `pg_backend_pid()`、`inet_server_*()` |
| PostgreSQL 数据库 | `pg36_shop` | `current_database()`、`pg_database` |
| 模式 | `shop` | `pg_namespace`、`\dn+` |
| 角色 | `pg36_owner`、`pg36_app`、`pg36_ro` | `pg_roles`、`\du+` |

从 PostgreSQL 侧运行最终快照：

```sql
SELECT
    current_database() AS database_name,
    (
      SELECT oid
      FROM pg_catalog.pg_database
      WHERE datname = current_database()
    ) AS database_oid,
    inet_server_addr() AS server_addr,
    inet_server_port() AS server_port,
    pg_backend_pid() AS backend_pid,
    pg_is_in_recovery() AS in_recovery;
```

从 Pigsty 侧用 `pg list <cluster>` 找到同一 `server_addr` 对应的实例与角色，再检查 HAProxy 的 default 服务是否把 `5436` 路由到该实例的 PostgreSQL `5432`。这就是“双侧指认”：平台名字最终落到 PostgreSQL 原生事实，SQL 地址也能反查回平台实体。

### 章级验收清单

只有以下各项全部成立，第 1 章才算完成：

- [ ] `verify.sql` 输出 `status=ok`，并以非零退出码报告任何不满足项；
- [ ] 能解释客户端访问 `5436` 而服务器接受端口显示 `5432` 的原因；
- [ ] 能区分 `pg-meta`、`pg-meta-1`、`pg36_shop`、`shop` 与 `pg36_app`；
- [ ] `pg36_owner` 不可登录，数据库与模式均由它拥有；
- [ ] `pg36_app` 与 `pg36_ro` 没有超级用户、建库、建角色、复制或绕过 RLS 权限；
- [ ] 能从 SQL 判断当前实例是否处于恢复状态，并从 `pg list` 找到对应平台角色；
- [ ] `connection.json`、`objects.txt`、`platform.txt`、`versions.txt` 和 `verify.txt` 已生成；
- [ ] 证据文件不包含密码、SCRAM verifier、令牌或不必要的完整 inventory；
- [ ] 知道三档 reset 的影响范围，但没有为了“练习”执行无关的集群或主机重建；
- [ ] 复位演练后可以重新运行 setup → verify，且知道数据库 OID 会改变。

### 交付给 ch02

下一章将接收本章的五样东西：两个管理员连接 URI、三类角色、`pg36_shop.shop` 命名约定、证据目录和可运行的 setup/verify/reset 脚本。ch02 会给应用角色配置安全凭据与服务入口，把这些手工命令改造成可审查、可重跑的任务。

本章刻意没有创建业务表。下一步不是凭直觉开始堆 DDL，而是先把工具链和失败语义固定下来。

## 参考资料

- [PostgreSQL 18：CREATE ROLE](https://www.postgresql.org/docs/18/sql-createrole.html)
- [PostgreSQL 18：CREATE DATABASE](https://www.postgresql.org/docs/18/sql-createdatabase.html)
- [PostgreSQL 18：默认权限](https://www.postgresql.org/docs/18/sql-alterdefaultprivileges.html)
- [Pigsty v4.5：用户与角色配置](https://pigsty.io/docs/pgsql/config/user/)
- [Pigsty v4.5：数据库配置](https://pigsty.io/docs/pgsql/config/db/)

---

[上一节：最小 psql 生存卡](../06/) · [返回本章目录](../) · [下一章：手到擒来：psql 与可复现工作流](/psql-workflow/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)