planetscale-postgres-safety-review

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Postgres safety review

Postgres安全审查

Purpose

目的

Recommend best practices for a PlanetScale Postgres database. Focus on availability protection, application isolation, recovery, safe automation, and observability. Do not apply changes.

为PlanetScale Postgres数据库推荐最佳实践。重点关注可用性保护、应用隔离、恢复、安全自动化和可观测性。请勿执行任何变更。

Branch and schema workflow

分支与Schema工作流

PlanetScale Postgres branches do not use Vitess-style deploy requests. Schema changes are made directly to each branch, and production schema changes should be managed through the application’s normal migration workflow with a branch validation step.

Check:

Whether a development or test branch exists.
Whether branches are empty or restored from backup.
Whether migrations are tested against a branch before production.
Whether application migrations are reversible or have a documented rollback strategy.
Whether production DDL is manually reviewed.

Recommend:

Create or use a non-production branch for migration testing.
Run migration validation and application tests against that branch.
Treat production migration application as an explicit human-approved deployment step.
Use PITR/backup restore branches for incident recovery, not as an automatic rollback mechanism.

Do not create branches, run migrations, or restore backups without approval.

PlanetScale Postgres分支不使用Vitess风格的部署请求。Schema变更直接在各分支上进行，生产环境Schema变更应通过应用的常规迁移工作流进行，并包含分支验证步骤。

检查项：

是否存在开发或测试分支。
分支是否为空或从备份恢复而来。
迁移是否在生产前针对分支进行测试。
应用迁移是否可逆或有文档化的回滚策略。
生产环境DDL是否经过人工审核。

建议：

创建或使用非生产分支进行迁移测试。
在该分支上运行迁移验证和应用测试。
将生产环境迁移应用视为明确的人工批准部署步骤。
使用PITR/备份恢复分支进行事件恢复，而非自动回滚机制。

未经批准，请勿创建分支、运行迁移或恢复备份。

Roles and least privilege

角色与最小权限

Check whether the application connects with the default role. Flag this as a safety gap.

Recommend:

Use user-defined application roles, not the default role, for application servers.
Separate roles by service, environment, and access pattern.
Use read-only roles for analytics, dashboards, reporting, and agents that do not need writes.
Use short-lived or purpose-limited roles for automation.
When Terraform manages PlanetScale Postgres roles, prefer
```
planetscale_postgres_redacted_branch_role
```
for roles whose password should stay out of Terraform state; reset the password through the API or dashboard and store it in the team's secret manager.
Document credential rotation without application downtime.

Do not create, reset, delete, or rotate roles without approval.

检查应用是否使用默认角色连接。将此标记为安全缺口。

建议：

使用用户定义的应用角色而非默认角色供应用服务器使用。
按服务、环境和访问模式分离角色。
为分析、仪表盘、报表以及无需写入权限的Agent使用只读角色。
为自动化流程使用短期或有限用途的角色。
当使用Terraform管理PlanetScale Postgres角色时，对于密码不应出现在Terraform状态中的角色，优先使用
```
planetscale_postgres_redacted_branch_role
```
；通过API或控制台重置密码，并存储在团队的密钥管理器中。
记录无需应用停机的凭证轮换流程。

未经批准，请勿创建、重置、删除或轮换角色。

pg_strict

Check whether pg_strict is enabled for application roles.

Recommend enabling pg_strict for production application roles when the workload can tolerate blocking dangerous

UPDATE

DELETE

without

WHERE

Recommended rollout:

Enable warning mode or evaluate in non-production where possible.
Fix queries that would be blocked.
Enable strict blocking for application roles.
Document approved one-off override procedure.

Do not enable pg_strict without approval because it can block application queries after new connections are established.

检查应用角色是否启用了pg_strict。

当工作负载可以容忍阻止无

WHERE

子句的危险

UPDATE

或

DELETE

操作时，建议为生产环境应用角色启用pg_strict。

推荐部署步骤：

尽可能在非生产环境启用警告模式或进行评估。
修复会被阻止的查询。
为应用角色启用严格阻止模式。
记录已批准的一次性覆盖流程。

未经批准请勿启用pg_strict，因为新连接建立后它可能会阻止应用查询。

Query Insights and pginsights

查询洞察与pginsights

Review:

Slow queries.
High rows-read queries.
High CPU query patterns (
```
sort=cpuTime
```
or
```
sort=percentCpuTime
```
on the Insights API).
High-frequency queries.
Erroring queries.
Active anomalies.
Query tags.
Whether literal/raw query collection is enabled.

Raw query collection is governed by the

pginsights.raw_queries

cluster parameter, configured per branch in the dashboard Extensions tab. The database API object also carries an

insights_raw_queries

field; when the two differ, the cluster parameter is the effective collection state. Report the effective state only — never describe the two surfaces as a contradiction or inconsistency.

Recommend:

Treat raw query collection as a capability, per
```
../04-query-insights-and-tags/SKILL.md
```
: when pattern-level data cannot isolate a pathological invocation, raw collection is the mechanism that can. Where the customer's data-handling requirements constrain it, scoped enablement (incident windows, defined retention) and leaving collection disabled are both valid outcomes; record the rationale.
Use tags for attribution and raw collection for invocation-level drill-down; they are complementary instruments.
Use deploy SHA and route/job tags to correlate regressions with application deploys.

审查内容：

慢查询。
高读取行数查询。
高CPU查询模式（Insights API上的
```
sort=cpuTime
```
或
```
sort=percentCpuTime
```
）。
高频查询。
出错查询。
活跃异常。
查询标签。
是否启用了字面量/原始查询收集。

原始查询收集由

pginsights.raw_queries

集群参数控制，可在控制台的Extensions标签页按分支配置。数据库API对象也包含

insights_raw_queries

字段；当两者不一致时，集群参数为有效收集状态。仅报告有效状态——切勿将这两个层面描述为矛盾或不一致。

建议：

将原始查询收集视为一种能力，参考
```
../04-query-insights-and-tags/SKILL.md
```
：当模式级数据无法定位异常调用时，原始收集是可行的机制。如果客户的数据处理要求对此有限制，范围化启用（事件窗口、定义保留期）和保持收集禁用都是有效的结果；请记录理由。
使用标签进行归因，使用原始收集进行调用级别的深入分析；它们是互补的工具。
使用部署SHA和路由/作业标签将性能退化与应用部署关联起来。

Query tags

查询标签

Evaluate whether SQL comments contain structured SQLCommenter tags.

Recommend tags that support both Insights and Traffic Control:

```
application
```
```
service
```
```
route
```
using normalized route templates, not concrete URLs
```
controller
```
and
```
action
```
where relevant
```
job
```
or
```
queue
```
for background workers
```
feature
```
for expensive features like exports or reports
```
environment
```
```
release_sha
```
```
tenant_tier
```
only if cardinality is bounded
```
source
```
for agents, scripts, BI tools, integrations, and MCP

Avoid high-cardinality or sensitive tags:

User ID
Request ID
Email
Session ID
Tenant ID unless explicitly bounded and accepted
Raw URL paths with identifiers
Access tokens, secrets, or API keys

评估SQL注释是否包含结构化的SQLCommenter标签。

推荐支持洞察与流量控制的标签：

```
application
```
```
service
```
```
route
```
：使用规范化的路由模板，而非具体URL
```
controller
```
和
```
action
```
（如适用）
```
job
```
或
```
queue
```
：用于后台工作进程
```
feature
```
：用于导出或报表等资源密集型功能
```
environment
```
```
release_sha
```
```
tenant_tier
```
：仅当基数有限时使用
```
source
```
：用于Agent、脚本、BI工具、集成和MCP

避免高基数或敏感标签：

用户ID
请求ID
邮箱
会话ID
租户ID（除非明确限定并被接受）
包含标识符的原始URL路径
访问令牌、密钥或API密钥

Database Traffic Control

数据库流量控制

For Postgres, recommend Traffic Control when the database has any of these patterns:

Public or customer-triggered expensive features.
Exports, reports, analytics, or ad hoc search sharing the OLTP database.
Background jobs that can starve interactive traffic.
Third-party integrations with unpredictable query volume.
Agent-generated queries.
Known query fingerprints that occasionally run away.
Tenant or route classes that need bounded database resource use.

Default recommendation:

Start budgets in
```
warn
```
mode.
Use query tags where possible.
Use fingerprint-specific rules for known offenders.
Use enforce mode only after observing warnings and confirming no critical traffic is blocked.
Maintain an emergency disable procedure.

Do not create budgets or enforce rules without approval.

对于Postgres，当数据库存在以下任何模式时，建议使用流量控制：

公共或客户触发的资源密集型功能。
导出、报表、分析或即席搜索与OLTP数据库共享资源。
可能抢占交互式流量的后台作业。
查询量不可预测的第三方集成。
Agent生成的查询。
偶尔会失控的已知查询指纹。
需要限制数据库资源使用的租户或路由类别。

默认建议：

以
```
warn
```
模式启动预算。
尽可能使用查询标签。
针对已知问题查询使用指纹特定规则。
仅在观察到警告并确认无关键流量被阻止后，才使用强制模式。
维护紧急禁用流程。

未经批准，请勿创建预算或强制执行规则。

Backups and PITR

备份与PITR

Check:

Automated backup schedule.
Retention window.
WAL/PITR availability.
Manual backups that prevent deletion.
Restore drill history.
Recovery runbook.

Recommend:

Confirm default backups meet the customer’s RPO/RTO.
Increase retention or add backup schedules if the customer’s recovery window exceeds defaults.
If Terraform is the customer's source of truth, manage backup policies in Terraform so retention and schedule changes are reviewed with the rest of the infrastructure code.
Run a restore drill to a new branch.
Document the exact application cutover procedure after restore.

Do not restore or create emergency backups without approval. Emergency backups may affect performance and should be treated as an operational action.

检查项：

自动备份计划。
保留窗口。
WAL/PITR可用性。
防止删除的手动备份。
恢复演练历史。
恢复手册。

建议：

确认默认备份满足客户的RPO/RTO要求。
如果客户的恢复窗口超过默认值，增加保留期或添加备份计划。
如果Terraform是客户的事实数据源，在Terraform中管理备份策略，以便保留期和计划变更与其他基础设施代码一同接受审查。
运行恢复演练至新分支。
记录恢复后的具体应用切换流程。

未经批准，请勿恢复或创建紧急备份。紧急备份可能影响性能，应视为操作行为。

Connections, pooling, and network safety

连接、池化与网络安全

Check:

Whether app uses direct port 5432 or PgBouncer port 6432.
Whether connection pool size matches runtime and deployment model.
Whether serverless or edge environments can create connection storms.
Live connection/session pressure through
```
pscale branch connections top
```
, including blockers and idle-in-transaction sessions when diagnosing active incidents.
Whether private connectivity is configured.
Whether IP restrictions are configured.
Whether public access remains available unexpectedly.

Recommend:

Use PgBouncer for high-churn application connections where transaction-pooling limitations are acceptable.
Use direct connections for session-dependent features that PgBouncer transaction mode cannot support.
Use AWS PrivateLink or GCP Private Service Connect for private network requirements.
Use IP restrictions to reduce public exposure.
Be explicit that private connectivity does not automatically block public access; IP restrictions or equivalent controls are required for private-only posture.

Do not change network restrictions without approval. Network changes can break application connectivity.

检查项：

应用是否使用直接端口5432或PgBouncer端口6432。
连接池大小是否匹配运行时和部署模型。
无服务器或边缘环境是否可能引发连接风暴。
通过
```
pscale branch connections top
```
查看实时连接/会话压力，包括诊断活跃事件时的阻塞和事务中闲置的会话。
是否配置了私有连接。
是否配置了IP限制。
公共访问是否意外保持可用。

建议：

当事务池限制可接受时，对高变动的应用连接使用PgBouncer。
对PgBouncer事务模式无法支持的会话相关功能使用直接连接。
对于私有网络要求，使用AWS PrivateLink或GCP Private Service Connect。
使用IP限制减少公共暴露。
明确说明私有连接不会自动阻止公共访问；仅私有部署需要IP限制或等效控制。

未经批准，请勿更改网络限制。网络变更可能会中断应用连接。

Extensions

扩展

Review enabled and available extensions relevant to safety and observability:

```
pginsights
```
```
pg_strict
```
```
pg_stat_statements
```
```
auto_explain
```
```
pg_squeeze
```
```
pg_cron
```
```
pg_partman_bgw
```
```
pg_hint_plan
```
TimescaleDB, if time-series features are relevant

Recommend extensions only when use case is clear.

auto_explain

is available for PlanetScale Postgres and can log execution plans for slow queries when configured with parameters such as

auto_explain.log_min_duration

; recommend it when slow-query plan capture would materially improve diagnosis and the logging volume is acceptable. When Terraform is the customer's source of truth, Postgres branch parameters and supported extensions can be managed there, but parameter or extension changes still require the same approval and restart impact review as dashboard changes. Some extension activation paths require dashboard changes and database restarts; do not enable them without approval.

审查与安全和可观测性相关的已启用和可用扩展：

```
pginsights
```
```
pg_strict
```
```
pg_stat_statements
```
```
auto_explain
```
```
pg_squeeze
```
```
pg_cron
```
```
pg_partman_bgw
```
```
pg_hint_plan
```
TimescaleDB（如果需要时序功能）

仅当用例明确时才推荐扩展。

auto_explain

可用于PlanetScale Postgres，配置

auto_explain.log_min_duration

等参数后，可记录慢查询的执行计划；当慢查询计划捕获能显著提升诊断能力且日志量可接受时，推荐使用。如果Terraform是客户的事实数据源，Postgres分支参数和支持的扩展可在其中管理，但参数或扩展变更仍需与控制台变更相同的批准和重启影响审查。部分扩展激活路径需要控制台变更和数据库重启；未经批准请勿启用。

Webhook recommendations for Postgres

Postgres的Webhook建议

Evaluate and recommend webhooks for:

```
branch.anomaly
```
```
branch.out_of_memory
```
```
branch.primary_promoted
```
```
branch.ready
```
```
branch.start_maintenance
```
```
cluster.storage
```
```
database.access_request
```
```
branch.schema_recommendation
```
if available
```
webhook.test
```
for setup validation

Recommended automation behavior:

Alerts: anomaly, out-of-memory, primary promotion, storage, maintenance.
Agent intake: anomaly, schema recommendation.
Human approval: any generated Traffic Control, schema, role, or network change.

评估并推荐以下Webhook：

```
branch.anomaly
```
```
branch.out_of_memory
```
```
branch.primary_promoted
```
```
branch.ready
```
```
branch.start_maintenance
```
```
cluster.storage
```
```
database.access_request
```
```
branch.schema_recommendation
```
（如可用）
```
webhook.test
```
：用于设置验证

推荐的自动化行为：

告警：异常、内存不足、主节点提升、存储、维护。
Agent接收：异常、Schema建议。
人工批准：任何生成的流量控制、Schema、角色或网络变更。

Output

输出

Return:

Current Postgres safety posture.
Highest-risk availability gaps.
Recommended Traffic Control plan.
Recommended role and pg_strict plan.
Recommended backup/PITR plan.
Recommended network posture plan.
Recommended query tagging plan.
Proposed changes requiring approval.

End with:

“No Postgres changes have been applied.”

当前Postgres安全状态。
最高风险的可用性缺口。
推荐的流量控制计划。
推荐的角色与pg_strict计划。
推荐的备份/PITR计划。
推荐的网络部署计划。
推荐的查询标签计划。
需要批准的拟议变更。

结尾需包含：

“No Postgres changes have been applied.”