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

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

实验分为两个风险级别：

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

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

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 创建数据库、业务模式和最小角色

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

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

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

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

执行初始化：

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 搜索路径。

成功末尾应出现：

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

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

与 Pigsty 声明式配置对齐

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

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。若在既有集群中应用声明，应先评审差异，然后使用：

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 生成连接快照、对象树、服务拓扑与环境清单

建立证据目录：

mkdir -p evidence/ch01

连接快照

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 直接查询目录验证。

对象树

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 管理节点执行：

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 和连接快照合读。

版本清单

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

{
  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

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

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

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

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

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

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

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

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

然后使用完整确认口令：

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 两侧指认同一对象

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

从 PostgreSQL 侧运行最终快照：

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
• PostgreSQL 18：CREATE DATABASE
• PostgreSQL 18：默认权限
• Pigsty v4.4：用户与角色配置
• Pigsty v4.4：数据库配置

上一节：最小 psql 生存卡 · 返回本章目录 · 下一章：手到擒来：psql 与可复现工作流 · 查看全书目录 · 查看索引中心