企业官网建设流程全解析

• 后端
• 前端
• 移动开发

本文是 SparkyFitness 服务端对接自有 PostgreSQL 实例（AWS RDS、Azure Database、CloudNativePG 或本地自装）的实操指南，涵盖双数据库角色（DB User / App User）的两种初始化方案、PgBouncer 会话池约束、Unix Socket 直连、以及首次启动后的CREATEROLE撤销加固。读完本文，你将能够独立完成外部数据库的建库、授权、环境变量配置与启动验证，并理解迁移器、权限授予脚本和 RLS 策略在底层是如何协同工作的。

[!WARNING]社区支持声明：外部与托管数据库配置未经核心维护者官方测试与支持，请自行评估风险后使用本指南。

1. 数据库与用户：双角色模型

SparkyFitness 使用两个独立的 PostgreSQL 角色：

这一设计在服务端源码中体现得非常明确：连接池管理器 在模块加载时同时创建两个连接池：

• Owner 池：使用SPARKY_FITNESS_DB_USER/SPARKY_FITNESS_DB_PASSWORD，供迁移器与系统级操作使用（见getSystemClient）；
• App 池：使用SPARKY_FITNESS_APP_DB_USER/SPARKY_FITNESS_APP_DB_PASSWORD，供业务查询使用（见getClient，且每个业务连接都会先执行SELECT public.set_app_context($1, $2)以设置 RLS 上下文）。

两个池默认max: 10、idleTimeoutMillis: 30000、connectionTimeoutMillis: 5000，端口缺省5432（源码第 28/45 行）。

[!IMPORTANT] 应用会在首次启动时自动创建 App 用户（SPARKY_FITNESS_APP_DB_USER）并授予全部必要权限；因此DB 用户（Owner）必须拥有CREATEROLE权限。

Option A —— 标准方案（应用自动创建 App 用户）

以数据库超级用户身份执行：

-- 1. 创建数据库 CREATE DATABASE sparkyfitness_db; -- 2. 创建 DB Owner 用户（对应 .env 中的 SPARKY_FITNESS_DB_USER） CREATE USER sparky_admin WITH PASSWORD 'your_secure_password'; -- 3. 授予数据库所有权，使 sparky_admin 可以创建 schema 与表 ALTER DATABASE sparkyfitness_db OWNER TO sparky_admin; -- 4. 授予角色创建权限 -- 应用首次启动时据此自动创建 SPARKY_FITNESS_APP_DB_USER --（对应后端实现 SparkyFitnessServer/utils/dbMigrations.ts） ALTER USER sparky_admin CREATEROLE; -- 5. 仅 PostgreSQL 15+ 需要：PG 15 起 public schema 默认权限发生变化， -- 需显式授予 owner 创建权限： GRANT ALL ON SCHEMA public TO sparky_admin;

启动时应用会检查角色是否已存在（SELECT 1 FROM pg_roles WHERE rolname = $1），不存在则执行CREATE ROLE，并在创建成功后输出日志Successfully created role: "<app user>"（见 迁移器源码）。

Option B —— 最小权限方案（手动预创建 App 用户）

如果托管环境不允许CREATEROLE（如严格管控的托管数据库），可以手动预创建 App 用户。应用启动时会先检测角色是否已存在——若角色已存在则完全跳过CREATE ROLE，此时不需要CREATEROLE。

-- 1. 创建数据库 CREATE DATABASE sparkyfitness_db; -- 2. 创建 DB Owner 用户（对应 .env 中的 SPARKY_FITNESS_DB_USER） CREATE USER sparky_admin WITH PASSWORD 'your_secure_password'; -- 3. 授予数据库所有权 ALTER DATABASE sparkyfitness_db OWNER TO sparky_admin; -- 4. 仅 PostgreSQL 15+ 需要 GRANT ALL ON SCHEMA public TO sparky_admin; -- 5. 预创建 App 用户（对应 .env 中的 SPARKY_FITNESS_APP_DB_USER） -- 应用检测到该角色已存在即跳过 CREATE ROLE， -- 因此 sparky_admin 不需要 CREATEROLE。 CREATE USER sparky_app WITH PASSWORD 'another_secure_password';

然后在.env中设置两个用户：

SPARKY_FITNESS_DB_USER=sparky_admin SPARKY_FITNESS_DB_PASSWORD=your_secure_password SPARKY_FITNESS_APP_DB_USER=sparky_app SPARKY_FITNESS_APP_DB_PASSWORD=another_secure_password

[!NOTE] Option B 下，sparky_admin仍需数据库所有权来运行迁移（建表、schema、函数、索引）；唯一不再需要的权限是CREATEROLE。

密码校验与同步机制（源码级解读）

迁移器对已存在角色的处理并非简单跳过，而是先做认证探测（appRoleCanAuthenticate，见 dbMigrations.ts）：

• 用SPARKY_FITNESS_APP_DB_USER+SPARKY_FITNESS_APP_DB_PASSWORD新建一次性客户端尝试连接；
• 仅当返回 SQLSTATE28P01（密码错误）或28000（认证失败）时判定密码失配，其余错误（主机不可达、库不存在等）直接抛出，避免误判；
• 密码匹配 → 输出Role "<app user>" already exists.，不执行任何ALTER ROLE，Owner 无需CREATEROLE；
• 密码失配 → 执行ALTER ROLE ... WITH LOGIN PASSWORD '...'；若因缺少CREATEROLE报42501，会抛出带明确指引的错误：要么把.env中的密码改回该角色实际使用的密码，要么以超级用户手动执行ALTER ROLE "<app user>" WITH PASSWORD '<new password>';。

2. 环境变量完整配置

在.env中指向你的外部数据库：

SPARKY_FITNESS_DB_HOST=your-db-host SPARKY_FITNESS_DB_NAME=sparkyfitness_db SPARKY_FITNESS_DB_USER=sparky_admin SPARKY_FITNESS_DB_PASSWORD=your_secure_password SPARKY_FITNESS_DB_PORT=5432 # App 用户 —— Option A 自动创建，或 Option B 由你预创建 SPARKY_FITNESS_APP_DB_USER=sparky_app SPARKY_FITNESS_APP_DB_PASSWORD=another_secure_password

两个 App 变量必须显式设置

SPARKY_FITNESS_APP_DB_USER与SPARKY_FITNESS_APP_DB_PASSWORD在标准 Docker Compose 安装下是可选的——服务端会默认取sparky_app并每次启动临时生成密码。但外部数据库必须两者都显式设置，因为角色的生命周期由你掌控：

• Option A：应用使用这两个值自动创建该角色；
• Option B：角色已存在，.env中的密码必须与创建时设置的一致。服务端启动时会以该角色身份连接验证；校验通过则不会发出ALTER ROLE，Owner 依然不需要CREATEROLE。

如果.env中的密码与角色实际密码失配，服务端会更新角色以匹配——这需要CREATEROLE。在刻意收回该权限的数据库上，请自行保持两者同步，否则服务端会报错并提示你执行哪条ALTER ROLE。

这一默认行为在 预检脚本 中实现：未设置SPARKY_FITNESS_APP_DB_USER时默认sparky_app；未设置SPARKY_FITNESS_APP_DB_PASSWORD时用crypto.randomBytes(32)生成临时密码——并明确提示：若多个服务端共享同一数据库，必须显式设置。

PgBouncer：必须使用 Session 模式

若通过 PgBouncer 连接，必须使用 session pooling。事务池（transaction pooling）与语句池（statement pooling）均与 SparkyFitness 基于会话的用户权限和启动迁移锁不兼容——应用连接与迁移连接都必须保留各自的 PostgreSQL 会话。

断连恢复与 TCP Keepalive

如果某个服务端节点在数据库初始化期间故障且未关闭连接，其他实例会一直等待，直到 PostgreSQL 检测到连接丢失并释放启动锁。Linux 默认 TCP keepalive 设置下，空闲连接约需两小时才能被检测到。可按恢复需求调整 PostgreSQL 的 TCP keepalive 配置（tcp_keepalives_idle等）；client_connection_check_interval能让运行中的查询及时感知已检测到的断连；idle_session_timeout可终止事务外空闲的遗弃会话，但不覆盖运行中的查询或空闲事务，且对连接池场景需谨慎使用。若使用代理，还需配置代理对客户端失连的检测。

通过 Unix Socket 连接

若 PostgreSQL 与后端同宿主机，可通过 Unix 域套接字代替 TCP 连接。把SPARKY_FITNESS_DB_HOST设为套接字目录（任何以/开头的值都被视为套接字路径而非主机名）：

SPARKY_FITNESS_DB_HOST=/var/run/postgresql SPARKY_FITNESS_DB_PORT=5432

驱动会在该目录后追加.s.PGSQL.<port>，因此SPARKY_FITNESS_DB_PORT仍然重要——它决定套接字文件名而非 TCP 端口。这一机制同时适用于应用连接池与内置备份/恢复（后者会以子进程方式调用pg_dump、psql、dropdb、createdb，见 备份服务实现）。

[!IMPORTANT]SPARKY_FITNESS_DB_PASSWORD与SPARKY_FITNESS_APP_DB_PASSWORD始终为必填——即使pg_hba.conf的 local 行使用peer或trust认证、实际不发送密码，服务端也会拒绝启动。请设置占位值，或改用scram-sha-256并设置真实密码。

[!NOTE]Docker 场景：容器内无法使用peer认证（容器 UID 不会匹配数据库角色）。请在pg_hba.conf的 local 行使用scram-sha-256，并把套接字目录绑定挂载进容器，例如- /var/run/postgresql:/var/run/postgresql。

3. PostgreSQL 扩展：零依赖

[!NOTE]不需要任何扩展。自 v0.17.0 起，SparkyFitness 已不再依赖uuid-ossp、pgcrypto与pg_stat_statements：

• UUID 生成改用内置的gen_random_uuid()（PostgreSQL 13+），无需任何扩展；
• 加密完全在应用代码层完成（Node.jscrypto的 AES-256-GCM），不使用数据库函数。

这一点由迁移脚本 20260618000000_remove_superuser_extensions.sql 完整承载，且该脚本对托管数据库非常友好：

• Step 1：把仍使用uuid_generate_v4()作为默认值的 8 张表（如food_entry_meals、sleep_entries、exercise_preset_entries等）的列默认值改为gen_random_uuid()，全程IF EXISTS幂等守卫；
• Step 2：若pgcrypto已安装，先把gen_random_uuid()默认值重新绑定到pg_catalog内置函数（避免删除 pgcrypto 后旧 PG 安装的 OID 悬空导致插入失败）；
• Step 3：尝试DROP EXTENSION三个旧扩展，并用EXCEPTION WHEN insufficient_privilege / dependent_objects_still_exist / OTHERS优雅跳过——非超级用户环境会自动跳过DROP EXTENSION，扩展即使残留也无害。

如果你是从旧版本升级且已安装这些扩展，该迁移会自动尝试移除；无法移除时跳过，不影响运行。

4. Row Level Security（RLS）与表所有权

sparky_admin必须是表的所有者，才能启用并管理 RLS 策略。ALTER DATABASE ... OWNER TO sparky_admin确保迁移期间创建的所有表自动归属于sparky_admin。

RLS 策略的单一事实来源是 rls_policies.sql，它在每次服务端启动、迁移完成后执行，以保证安全状态一致：先清理publicschema 的全部既有策略，再对exercise_entries、food_entries、check_in_photos、family_access等业务表统一ENABLE ROW LEVEL SECURITY并重建策略。而业务连接在 poolManager.ts 的getClient中通过SELECT public.set_app_context($1, $2)注入当前用户上下文，由 RLS 完成行级隔离。这也解释了为什么 App 用户只拥有受限权限、而 Owner 必须拥有表——RLS 策略的管理（创建、ALTER）天然要求表所有者权限。

权限授予的完整清单见 grantPermissions.ts：迁移完成后会对public、auth、system三个 schema 的现有对象及默认权限授予USAGE、增删改查、序列使用与函数执行权限，并单独授予system.schema_migrations的SELECT供应用检查已应用迁移。

5. 安全加固（仅适用于 Option A）

CREATEROLE仅在初始安装或未来更新需要创建新的专用角色时才必要。

撤销 CREATEROLE

应用首次启动成功后，看到日志Successfully created role，即可撤销该权限：

ALTER USER sparky_admin NOCREATEROLE;

[!CAUTION]撤销后的潜在问题：若未来应用更新需要创建新的数据库角色，撤销后升级会失败。若后续升级时遇到 "Permission Denied" 错误，请临时重新授予CREATEROLE，或以超级用户手动创建所需角色，然后再撤销。

6. 启动验证与常见问题排查

完成上述配置后，可按以下顺序验证：

1. 预检：服务端启动先执行runPreflightChecks（preflightChecks.ts）。它会补齐缺失的连接默认值（SPARKY_FITNESS_DB_HOST默认sparkyfitness-db、SPARKY_FITNESS_DB_NAME默认sparkyfitness_db、SPARKY_FITNESS_DB_USER默认sparky），并强制校验SPARKY_FITNESS_DB_PASSWORD、SPARKY_FITNESS_FRONTEND_URL、SPARKY_FITNESS_API_ENCRYPTION_KEY、BETTER_AUTH_SECRET四项必填变量——缺失即 FATAL 拒绝启动；
2. 角色日志：观察启动日志中的Creating role/Role already exists/Successfully updated password分支，确认走了预期的 Option A 或 Option B 路径；
3. 权限日志：确认输出Permissions granted to application user.，说明grantPermissions已按清单完成授权；
4. 备份验证：外部数据库同样支持内置备份/恢复（pg_dump/psql/dropdb/createdb子进程），若走 Unix Socket 路径，请确认SPARKY_FITNESS_DB_HOST指向的目录可被运行备份的进程访问。

典型报错对照：

本文档对应仓库中的原始指南位于 docs/src/install/external-database.md，配套后端实现可参阅 SparkyFitnessServer/utils/dbMigrations.ts、SparkyFitnessServer/db/poolManager.ts 与 SparkyFitnessServer/db/grantPermissions.ts，迁移脚本位于 SparkyFitnessServer/db/migrations。关于 Docker Compose 自带的数据库编排方式，可对照 docker/docker-compose.dev.yml 与 docker/docker-compose.prod.yml 理解两套方案的差异。

• 后端
• 前端
• 移动开发