2.1 可靠连接与上下文保护

第 1 章已经说明：连接串表达客户端意图，SQL 快照才是服务端证据。本节把这条原则固化成一个可重复入口。连接参数负责“去哪里”，凭据负责“我是谁”，上下文保护负责“这里是否允许执行这项任务”；三者不能因为都出现在一次连接里就混成一件事。

2.1.1 连接 URI、服务文件与环境变量

libpq 客户端——包括 psql、pg_dump、pg_restore 和 pgbench——共享一套连接参数。参数可以来自命令行、连接 URI、service file、环境变量与内置默认值。工程上的关键不是选出唯一写法，而是让覆盖关系和秘密边界可见。

给端点命名

下载连接服务文件示例，复制到当前用户的私有路径并替换 <L1_HOST>：

[pg36-admin]
host=<L1_HOST>
port=5436
dbname=pg36_shop
user=dbuser_dba
application_name=pg36-ch02
connect_timeout=5
options=-c statement_timeout=30s -c lock_timeout=5s

然后设置：

chmod 600 "$PWD/pg_service.conf"
export PGSERVICEFILE="$PWD/pg_service.conf"
psql -X "service=pg36-admin"

pg36-admin 是 libpq service 名称，不是 Pigsty 服务名。这里把它映射到 Pigsty default 服务的默认端口 5436：HAProxy 跟随当前主库，并把连接直接交给 PostgreSQL。若平台修改过服务定义，以实际配置和 ch01 的端点快照为准。

service file 使用 INI 语法。用户级默认路径是 ~/.pg_service.conf；PGSERVICEFILE 可以指定另一文件。显式连接参数会覆盖 service file 中的同名参数，service file 的值又会覆盖相应环境变量。例如：

PGPORT=9999 psql -X \
  "service=pg36-admin port=5436 application_name=pg36-override"

最终端口是 URI 中显式给出的 5436，而不是环境变量的 9999。不要靠记忆猜覆盖结果；连接后用 \conninfo 和 SQL 快照验证。

把秘密留在秘密载体

不要把密码写入本书配置、Git、命令行 URI 或 PGPASSWORD。Unix 上的 passfile 默认是 ~/.pgpass，也可由 PGPASSFILE 指定；每行格式是：

hostname:port:database:username:password

文件权限必须限制为 0600 或更严格，否则 libpq 会忽略它。匹配按从上到下的第一条记录决定，过早出现的 * 通配行可能把错误凭据应用到意外目标。密码中的 : 与 \ 还要按 passfile 规则转义。

自动化任务使用 -w（--no-password）：

psql -X -w "service=pg36-admin" -c 'SELECT current_database();'

它不会弹出交互式密码提示；若非交互凭据缺失，任务会立即失败。这比 CI 卡在不可见的密码提示上更可靠。交互探索时可以去掉 -w，让客户端主动询问。

service file 与 passfile 解决的是客户端配置管理，不是权限设计。角色授权、SCRAM、 证书和 pg_hba.conf 会在 ch23《固若金汤：认证、授权与数据安全》 系统展开。

2.1.2 application_name、提示符与上下文快照

可靠连接需要同时照顾人、服务器和证据系统：

• application_name 让服务端活动视图与日志知道“这条连接自称在做什么”；
• 提示符让操作者持续看见用户、主机、端口、数据库和事务状态；
• 上下文快照用服务端 SQL 证明实际数据库、角色、后端和读写状态。

三者互补，但都不是安全身份。客户端可以伪造 application_name，提示符可以被本地配置改坏，快照也只证明采集时刻的会话状态。

可观察的连接标签

service file 已经设置 application_name=pg36-ch02。也可按任务覆盖：

psql -X \
  "service=pg36-admin application_name=pg36-ch02-inspect"

在另一条有权查看活动会话的连接中验证：

SELECT
    pid,
    usename,
    datname,
    application_name,
    client_addr,
    backend_start,
    state
FROM pg_catalog.pg_stat_activity
WHERE application_name LIKE 'pg36-ch02%'
ORDER BY backend_start, pid;

标签应包含系统或任务名，而不是工单中的秘密、客户数据或完整 SQL。后续监控会使用它聚合会话，但不会把它当作授权条件。

让提示符暴露危险上下文

下载psqlrc 示例，其中核心设置是：

\set PROMPT1 '%n@%m:%>/%/%R%x%# '
\set PROMPT2 '%n@%m:%>/%/%R%x%# '

常用转义含义如下：

本书的可复现实验仍统一使用 psql -X，因为 -X 会跳过用户与系统 psqlrc，避免个人格式、变量或自动 SQL 改变脚本行为。交互会话可以使用提示符增强，人读体验与机器复现不应争用同一隐含配置。

进入会话后的标准快照

连接后先执行：

\conninfo

SELECT
    current_database()                    AS database_name,
    session_user                          AS authenticated_as,
    current_user                          AS effective_as,
    current_setting('search_path')        AS configured_path,
    current_schemas(false)                AS effective_path,
    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,
    current_setting('transaction_read_only')::boolean
                                             AS transaction_read_only,
    current_setting('application_name')   AS application_name;

\conninfo 展示客户端已知的连接信息；SQL 列来自当前 PostgreSQL 后端。经过 Pigsty 5436 进入后，\conninfo 会保留客户端访问的服务入口，而 inet_server_port() 通常显示最后一跳 PostgreSQL 的 5432。把两侧一起保存，才能重建连接路径。

同一快照不要只拍一次。任务开始前用于阻断错误目标，任务结束后用于证明结果属于哪个会话；长任务还应在证据中记录开始和结束时间。

2.1.3 防止连错库、用错角色和改错模式

颜色鲜艳的提示符只能提醒人，不能保护无人值守任务。真正的保护必须在第一条有副作用的 SQL 之前验证数据库、恢复状态、有效角色与搜索路径，并在不符合预期时产生可信的非零退出码。

本章的上下文保护脚本按以下顺序执行：

1. 默认期望数据库为 pg36_shop、对象所有者为 pg36_owner；
2. 验证当前数据库正确且实例不在恢复；
3. 才执行 SET ROLE pg36_owner；
4. 设置并验证 search_path = pg_catalog, shop；
5. 输出一行可保存的上下文摘要。

关键片段是：

\set ON_ERROR_STOP on

SELECT
    current_database() = :'expected_db' AS database_ok,
    NOT pg_is_in_recovery()             AS writable_instance
\gset

\if :database_ok
\else
  \warn '[context] refused: unexpected database'
  DO $guard$
  BEGIN
      RAISE EXCEPTION 'context guard rejected the current database';
  END
  $guard$;
\endif

\if 是 psql 的客户端条件，不是 PL/pgSQL。查询通过 \gset 把一行结果写入 psql 变量；不满足条件时，固定的 DO 块抛出服务端异常，ON_ERROR_STOP 再让脚本以退出码 3 停止。

这里特意不写 \quit 3：PostgreSQL 18 的 psql 中 \quit 不接受自定义状态码，多余参数会使该元命令被忽略。保护脚本若只打印警告而没有可靠失败机制，最危险的结果就是“看起来拒绝，实际上继续”。

为什么先验数据库，再切换角色

如果先以高权限角色执行 SET ROLE，再发现连接到了错误数据库，权限提升动作已经发生。当前示例先执行两个只读断言，确认目标可写且数据库名称正确，之后才切换到无登录对象所有者。任何一步失败都由 ON_ERROR_STOP 截断。

角色验证不能只看 session_user：

SELECT session_user, current_user;

session_user 证明谁完成认证，current_user 证明此刻权限检查采用谁。对象迁移通常要求前者是受控管理员、后者是专用 owner；运行时查询则不应随意成为 owner。

搜索路径要验证有效结果

脚本显式设置：

SET search_path = pg_catalog, shop;

然后比较：

SELECT current_schemas(false)
       = ARRAY['pg_catalog', 'shop']::name[] AS path_ok;

因为 pg_catalog 被显式写入路径，即使 current_schemas(false) 的参数表示不额外包含隐式模式，结果仍会保留它。不要根据函数参数名称想当然地断言结果；在目标版本上观察实际数组。

安全敏感或机器生成的 SQL 仍应显式限定对象名。受控 search_path 降低误解析风险，但不把 shop.orders 写成 orders 的所有上下文都变得安全。

负向验证

保护脚本必须证明“错误目标会失败”。先对正确连接运行：

psql -X -w "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  -f context.sql

预期看到类似：

[context] database=pg36_shop session_user=dbuser_dba \
current_user=pg36_owner search_path=pg_catalog, shop

再显式覆盖到 postgres 数据库：

set +e
psql -X -w \
  "service=pg36-admin dbname=postgres" \
  -v ON_ERROR_STOP=1 \
  -f context.sql
status=$?
set -e
test "$status" -eq 3

第二次运行应在任何写操作之前返回 3。若返回 0，不要继续后续章节；先修复保护脚本或调用方式。

本节验收

• service file 不含密码，passfile 权限符合要求；
• psql -X -w "service=pg36-admin" -c '\conninfo' 可以非交互完成；
• 能同时保存客户端入口与服务端后端快照；
• 错误数据库测试返回 3；
• 能解释为什么 application_name、提示符和 SQL 断言都不能互相替代。

参考资料

• PostgreSQL 18：数据库连接控制
• PostgreSQL 18：环境变量
• PostgreSQL 18：密码文件
• PostgreSQL 18：连接服务文件
• PostgreSQL 18：psql 提示符
• Pigsty v4.4：服务与接入

返回本章目录 · 下一节：用 psql 探索与取证 · 查看全书目录 · 查看索引中心