保护参展商的读取与写入

介绍

Fern Studio 和 Cedar Works 正在筹备小型手工市集。每家参展商应在同一张共享 PostgreSQL 表中维护自己的装卸登记。市集团队仍必须查看所有人，并分配摊位。客户端命令中的筛选便于浏览，但参展商可以省略它；数据库需要强制执行边界。

你将保存并应用行策略，再用独立的普通密码登录完成真实登记。PostgreSQL 和限定范围账号已准备好。你需要掌握基本终端导航，以及 PostgreSQL SELECT/WHERE、INSERT、UPDATE 和表概念。角色、行级安全、策略表达式、事务和所需 shell 语法会在使用前讲解。无需 Python 编程。全程使用 Terminal。95 分钟是作者估计，包含阅读和普通错误处理，不是测得的初学者完成时间。

在 /home/labex/project/exhibitor-desk 工作。编写 read-access.sql 和 write-access.sql，修改 fair.registrations 上的行策略，并只创建或修正后文指定的两条新登记。保留全部原始记录、表标识和定义、预备授权及账号、凭据、工具和服务。策略文件必须为自包含 SQL，不含 shell 命令、连接切换或外部包含文件。接受等效策略名及正确策略安排。你是课程操作者，可以主动选择每个预备测试账号；本练习保护不同数据库身份之间的边界，不向 VM 的 sudo 操作者保密凭据。它不实现网站认证，也不承诺防御所有信息侧信道。

限制可见范围并保留市集团队访问

本步骤中，你将让每家参展商完整查看自己的登记，并保留组织者跨参展商工作的能力。下一步添加写策略前，参展商写入仍不可用。

从 Terminal 开始。cd 切换目录；无论当前目录在哪里，这个绝对路径都有效：

cd /home/labex/project/exhibitor-desk

./ 运行当前目录的程序。预备 db 启动工具认证真实 PostgreSQL 连接，并运行 PostgreSQL 客户端 psql。它不筛选行，也不编造结果。列出账号别名：

./db accounts

别名选择以下独立准备的登录身份：

别名是你作为操作者使用的便利入口，不是授予 Fern 的数据库权限。选择 cedar 会主动使用 Cedar 的独立凭据。在 Fern 连接内部，切换为 Cedar 必须仍被禁止。

-c 提供一条命令字符串。双引号让空格保留在同一个 shell 参数中。session_user 标识认证登录，current_user 标识语句当前使用权限的角色。在这些普通连接中，两者应相同：

./db fern -c "SELECT session_user, current_user;"

预期两列都是 fern_studio。先读取 Fern 已知的正常记录。货箱数量是整数；零表示已知零值，不表示登记缺失：

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations WHERE registration_id = 'R101';"

R101 属于 Fern，联系人为 Fern desk，货箱数 2，摊位 A1，费用 1200 分。fair 是对表进行分组的模式。这些原始业务事实必须不变。

现在省略可选客户端筛选：

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

初始 Fern 能看到 R101/R102，以及 Cedar 的 R201/R202。WHERE owner_role = 'fern_studio' 可以在这一次查询中隐藏它们，却无法阻止不带筛选的查询。Cedar 也有同样问题。

PostgreSQL 有两层相关权限。GRANT 允许对表或指定列执行操作；预备授权已让这些用户能进行所需操作。行级安全，简称 RLS，进一步决定他们可读取或修改哪些行。启用 RLS 却没有适用策略时，默认拒绝行访问；它不会自动生成归属规则。

editor 拥有本课程表，可以修改策略。所有者通常绕过 RLS，因此 editor 查询成功不能证明参展商受限。用 editor 检查定义，用普通账号测试结果。\d 是 psql 描述命令，不是 SQL：

./db editor -c "\d fair.registrations"

六列为 registration_id、owner_role、contact、crates、booth_label 和 fee_cents。新登记默认摊位为 Pending，费用为 0。预备列授权允许参展商插入 ID、归属、联系人和货箱数，更新归属、联系人和货箱数。他们不能更新 ID、摊位或费用，不能删除行或截断表。即使归属列已获 UPDATE 授权，策略仍必须拒绝改变归属。

通过 PostgreSQL 目录视图 pg_policies 检查已有策略：

./db editor -c "SELECT policyname, roles, cmd, qual, with_check FROM pg_policies WHERE schemaname = 'fair' AND tablename = 'registrations' ORDER BY policyname;"

legacy_open_access 允许所有人。策略通常为宽松型（permissive）：适用策略以 OR 组合。在这个宽策略旁添加窄策略，仍会放行宽策略结果。将撤除旧策略作为变更的一部分。

策略的 USING 表达式决定已有行是否可见。owner_role = current_user 比较行归属与真实数据库身份。FOR SELECT 将策略限制为读取。组织者需要单独策略，限定 TO fair_team，允许其对所有行执行合法操作；true 表示对该指定角色，每行都通过。

保存第一份迁移。cat > read-access.sql 把以下文本写入文件，替换旧副本。<<'SQL' 开始 here-document，带引号标记让其中 SQL 保持字面内容，直到 SQL 独占一行。BEGIN 和 COMMIT 将策略变更组成一个事务，让语句共同提交，或在出错时回滚。DROP POLICY IF EXISTS 也便于修正后重跑迁移：

cat > read-access.sql <<'SQL'
BEGIN;
ALTER TABLE fair.registrations ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS legacy_open_access ON fair.registrations;
DROP POLICY IF EXISTS own_read ON fair.registrations;
DROP POLICY IF EXISTS organizer_access ON fair.registrations;
CREATE POLICY own_read ON fair.registrations
  FOR SELECT TO fern_studio, cedar_works
  USING (owner_role = current_user);
CREATE POLICY organizer_access ON fair.registrations
  TO fair_team USING (true) WITH CHECK (true);
COMMIT;
SQL

如果因遗漏结束标记而 shell 仍等待，单独输入一行 SQL。执行前先读取保存文件：

cat read-access.sql

-f 告诉 psql 从文件读取命令。以限定范围表所有者身份应用：

./db editor -f read-access.sql

预期出现 BEGIN、ALTER TABLE、DROP POLICY、CREATE POLICY 和 COMMIT 消息。提示某个新策略名尚不存在是无害的。启动工具启用 ON_ERROR_STOP：文件语法错误会停止执行，psql 状态为 3。连接关闭会回滚未完成事务。修正文件并重跑，无需重置 VM。文件名或路径错误时，用绝对路径 cd、运行 ls、读取真实文件名后重试。不需删除或重建任何原始行。

用各普通身份检查完整允许集合：

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

Fern 看到 R101/R102，数量 2/0；Cedar 看到 R201/R202，数量 3/1。组织者仍可看到全部四条原始记录：

./db organizer -c "SELECT registration_id, owner_role, booth_label FROM fair.registrations ORDER BY registration_id;"

这是有意的中间状态：参展商已有 SELECT 策略，但尚无 INSERT/UPDATE 策略。保持预备授权不变。运行结果检查；sudo 让预备检查器能检查受保护状态。它读取实际业务并在可丢弃数据上测试策略 SQL，不补齐缺失工作：

sudo python3 /opt/labex-exhibitors/verify.py visibility

预期确认两个账号都看到自己记录，组织者仍可工作。接下来为拟写入值添加独立规则。

同时检查已有行和拟写入值

本步骤中，你将允许参展商添加和更新自己的登记，同时不能为另一归属创建或转移记录。从 Terminal 开始，工作目录为 /home/labex/project/exhibitor-desk。

仅 SELECT 策略不授权 INSERT。写策略需考虑两个时点。对 UPDATE，USING 选择角色可改变的已有行，WITH CHECK 检查拟生成的新行。INSERT 没有旧行，因此策略只使用 WITH CHECK。两种情况下，拟写入归属都必须等于 current_user。

使用相同带引号 here-document 格式保存写迁移。它向已由 read-access.sql 保护的表添加策略：

cat > write-access.sql <<'SQL'
BEGIN;
DROP POLICY IF EXISTS own_insert ON fair.registrations;
DROP POLICY IF EXISTS own_update ON fair.registrations;
CREATE POLICY own_insert ON fair.registrations
  FOR INSERT TO fern_studio, cedar_works
  WITH CHECK (owner_role = current_user);
CREATE POLICY own_update ON fair.registrations
  FOR UPDATE TO fern_studio, cedar_works
  USING (owner_role = current_user)
  WITH CHECK (owner_role = current_user);
COMMIT;
SQL

读取文件，再以 editor 身份应用：

cat write-access.sql

./db editor -f write-access.sql

预期两条 CREATE POLICY 提交。修正后重跑只替换这些指定写策略，不重置登记。若从保存策略文件重新应用，先应用 read-access.sql，再应用 write-access.sql。

等效策略安排也可工作。ALL 或 UPDATE 策略只有 USING 时，会隐式将该表达式用于缺失的 WITH CHECK；省略此子句并不自动意味着有缺陷。这里分开命令策略，让每项职责明确。重要的是每种允许和禁止操作的实际行为，不是策略名或精确拼写。

先练习合法写入，不留下测试数据。BEGIN 开始事务，RETURNING 显示 INSERT/UPDATE 实际存储行，ROLLBACK 丢弃事务变更，保留四条原始记录：

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FERN', 'fern_studio', 'Loading test', 0); UPDATE fair.registrations SET crates = 2 WHERE registration_id = 'TRY-FERN' RETURNING registration_id, owner_role, crates; ROLLBACK;"

预期 TRY-FERN、归属 fern_studio、货箱数 2，随后 ROLLBACK。正常插入接受真实零值，更新可用；拒绝所有写入的规则无法满足要求。

现在测试隐藏的已有行。这条语句必须作为命令成功，但找不到允许更新的行：

./db fern -c "UPDATE fair.registrations SET contact = 'Wrong contact' WHERE registration_id = 'R201' RETURNING registration_id;"

预期没有返回行，显示 UPDATE 0。R201 属于 Cedar。零行结果是行筛选，不是连接失败或数据库错误。

接下来两条命令是预期 SQL 失败。两者把探测放在事务中：错误策略若意外接受，最终 ROLLBACK 仍会撤销。如果出错导致命令在 ROLLBACK 前停止，连接关闭也会丢弃未完成事务。

以 Fern 认证身份尝试创建 Cedar 所有的记录。刻意省略 RETURNING：对返回行的 SELECT 策略检查，不能替代拟写入行规则：

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FOREIGN', 'cedar_works', 'Wrong owner', 1); ROLLBACK;"

预期 new row violates row-level security policy。$? 是紧邻前一 shell 命令状态；运行任何其他命令前先打印：

echo $?

此失败的 -c SQL 命令应返回状态 1。状态 2 并伴随连接或认证错误，是不同失败，不能证明行策略起效。启动工具每次调用选择预备凭据；使用 ./db accounts，按指定别名重试，不要编辑凭据文件。

接着尝试将 Fern 可见行的归属改为 Cedar。常量 UPDATE 没有读取表列的 WHERE 或 RETURNING，直接触及拟写入行检查；外层事务使意外接受的探测也可撤销：

./db fern -c "BEGIN; UPDATE fair.registrations SET owner_role = 'cedar_works'; ROLLBACK;"

echo $?

仍预期行安全错误和状态 1。不要认为只有带 RETURNING 的更新才需要保护：允许的 SQL 可以省略它。

列权限提供另一边界。参展商可改自己的联系人和数量，但摊位及费用由市集团队分配。即使针对 Fern 自己行，以下摊位修改也必须产生表权限错误：

./db fern -c "BEGIN; UPDATE fair.registrations SET booth_label = 'WRONG'; ROLLBACK;"

预期 permission denied for table registrations，而非拟写入行策略错误。同样，Fern 连接不能仅通过指定角色名就成为 Cedar：

./db fern -c "SET ROLE cedar_works;"

预期 permission denied to set role。预备参展商是普通登录，没有跨账号成员关系、表所有权或 BYPASSRLS。修改可自由控制的应用设置，不会使连接获得另一个登录身份；策略比较的是真实数据库角色。

确认可撤销探测保留了原始业务事实：

./db organizer -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

原始四行、联系人、数量、摊位和费用必须与初始值相同。运行写边界检查：

sudo python3 /opt/labex-exhibitors/verify.py writes

它检查两个普通身份、合法写入、变化的数据、跨归属写入及组织者访问。全部通过后，就可以用受保护表开展正常工作。

通过受保护账号完成登记

本步骤中，你将使用有效权限准备两条真实装卸登记。从 Terminal 开始，工作目录为 /home/labex/project/exhibitor-desk。只允许添加或修正这两条新业务记录；保留每条原始行。

Fern 先以已知零货箱数创建新登记。组织者分配前，预备默认值使摊位为 Pending、费用为 0：

./db fern -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R301', 'fern_studio', 'Fern loading', 0) RETURNING registration_id, owner_role, contact, crates, booth_label, fee_cents;"

预期 R301、fern_studio、Fern loading、0、Pending、0。这是真实已提交 INSERT，与此前事务探测不同。重复会产生重复键错误；它不是幂等创建命令。

用定向普通更新将最终货箱数设为四：

./db fern -c "UPDATE fair.registrations SET crates = 4 WHERE registration_id = 'R301' RETURNING registration_id, owner_role, crates;"

Cedar 创建自己的新登记，货箱数为一：

./db cedar -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R302', 'cedar_works', 'Cedar freight', 1) RETURNING registration_id, owner_role, crates;"

预期 R302、cedar_works、1。每家参展商现在有三条可见记录，包括原有两条。

组织者用自己的普通身份，为新记录分配约定摊位和费用。费用以整数分表示；给定金额是场景事实，不是税费或会计计算：

./db organizer -c "UPDATE fair.registrations SET booth_label = 'C3', fee_cents = 1800 WHERE registration_id = 'R301' RETURNING registration_id, booth_label, fee_cents;"

./db organizer -c "UPDATE fair.registrations SET booth_label = 'D2', fee_cents = 600 WHERE registration_id = 'R302' RETURNING registration_id, booth_label, fee_cents;"

预期 R301/C3/1800 和 R302/D2/600。此工作使用 fair_team，不是表所有者；所有者绕过限制不能证明组织者策略仍可用。

通过各参展商读取最终完整记录：

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

Fern 看到 R101/R102/R301，新 R301 数量为 4、摊位 C3、费用 1800。Cedar 看到 R201/R202/R302，新 R302 数量为 1、摊位 D2、费用 600。原始记录必须不变。

不小心重复 INSERT 时，用对应账号检查指定新记录。已存在时，定向 UPDATE 联系人和数量，不要删除或重置数据库。例如，可用 UPDATE fair.registrations SET contact = 'Fern loading', crates = 4 WHERE registration_id = 'R301'; 修正 Fern 新记录。只修正指定新记录；组织者摊位或费用分配出错时，重跑对应定向分配。保存迁移也可修正后重跑，无需重建数据。

运行最终结果检查：

sudo python3 /opt/labex-exhibitors/verify.py business

预期确认两条新登记具有正确归属、数量、摊位及费用，已经就绪。你拥有可复用策略 SQL，以及普通参展商和组织者真实可用的共享登记表。独立挑战将相同边界应用于另一份手作商户登记表。

总结

你区分了表、列权限与行归属，撤除了宽泛允许策略，并通过真实普通登录检查已有行和拟写入值。两条新登记及组织者分配的摊位已可正常使用，同时没有暴露或改变另一参展商记录。

边界是已认证数据库身份。表所有者和管理员仍被信任，本练习不提供网站登录，也不保证抵御所有数据库侧信道。