Support PgBouncer deployments in VPCs without NAT gateways

从 lib/database/PgBouncer.ts 和 lib/database/pgbouncer-setup.sh 开始，然后跟踪现有的 pgbouncerAmiSsmParameter 路径以及 zero-NAT 部署要求。比较候选方案并确认预期的契约；完成的标准是具备健康的 zero-NAT 部署、覆盖连接性和回归检查，并记录受支持的拓扑以及 direct-RDS workaround。

[!NOTE]
Written by GPT 5.6 Terra

Summary

PgStacDatabase enables PgBouncer by default, but its PgBouncer EC2 instance cannot complete its first-boot setup when the VPC has zero NAT gateways and the instance is deployed in a private subnet. This makes the default database configuration unusable in private, no-NAT deployments.

Current behavior

lib/database/PgBouncer.ts places the instance in a PRIVATE_WITH_EGRESS subnet by default and supplies lib/database/pgbouncer-setup.sh as EC2 user data. The script requires outbound access during boot to:

• add the PostgreSQL APT repository and download its signing key;
• run apt-get update, apt-get upgrade, and install pgbouncer and jq;
• install the AWS CLI through Snap;
• download the CloudWatch agent package from S3.

After package installation, it also reads the database secret from Secrets Manager and sends CloudWatch telemetry. Those AWS API calls require either Internet/NAT egress or the relevant VPC endpoints.

With no NAT gateway, the user-data script fails before PgBouncer becomes healthy. The subsequent health-check custom resource fails the CloudFormation deployment.

Expected behavior

PgBouncer should be usable in a VPC with zero NAT gateways, without requiring a public subnet or general Internet egress. Any required private AWS connectivity should be explicit and documented.

Steps to reproduce

1. Deploy an eoapi-cdk stack into a VPC configured with zero NAT gateways.
2. Leave PgBouncer enabled (addPgbouncer defaults to true).
3. Deploy the stack.
4. Observe that the PgBouncer user-data setup cannot download required packages and the PgBouncer health check fails.

Impact

Users choosing a zero-NAT network design for cost or security cannot use the default PgBouncer configuration. They must disable PgBouncer and connect directly to RDS, or add NAT/public Internet access.

Notes / possible cause

The immediate blockers are the public APT, Snap, and S3 downloads in lib/database/pgbouncer-setup.sh. A no-NAT solution will also need to account for the instance's ongoing calls to Secrets Manager, Systems Manager, and CloudWatch.

The construct currently does not provision these dependencies or document them as prerequisites.

Candidate solutions

1. Prebuilt PgBouncer AMI with caller-managed endpoints

Provide or document a supported AMI containing PgBouncer, AWS CLI, jq, and the CloudWatch agent. User data would only configure PgBouncer and retrieve the database secret. Callers would supply the required VPC endpoints.

This is the preferred direction: it preserves the current EC2 architecture and keeps ownership of the shared VPC with the caller. It requires an explicit AMI release and patching lifecycle, because unattended upgrades cannot use public package repositories.

AMI distribution and lifecycle

Do not initially publish a DevSeed-owned public AMI. Maintaining a cross-account, multi-region image distribution introduces AMI-copy, EBS snapshot-sharing, encryption, release, and patch-lifecycle obligations that exceed this construct's scope.

Instead, publish a reproducible Packer or EC2 Image Builder recipe that callers run in each target account and region. The recipe starts from the pinned Ubuntu base image and installs PgBouncer, jq, AWS CLI, and the CloudWatch agent. Callers store the resulting AMI ID in an SSM parameter and pass that parameter through the existing pgbouncerAmiSsmParameter property.

A baked-image mode is still required: PgBouncer must skip the package-installation portion of pgbouncer-setup.sh while retaining runtime configuration and Secrets Manager retrieval. This runtime path requires caller-managed interface endpoints for Secrets Manager, SSM, SSM Messages, EC2 Messages, and CloudWatch Logs.

2. Offline packages delivered through CDK/S3

Package the required .deb files as a CDK asset and retrieve them through an S3 gateway endpoint at boot.

This avoids an AMI build pipeline, but makes package dependency management, Ubuntu-version compatibility, and security updates the construct's responsibility.

3. Replace EC2 PgBouncer with RDS Proxy

Use RDS Proxy instead of a self-managed EC2 pooler, eliminating host bootstrap and public package downloads.

This is a larger product change with different connection-pooling behavior, configuration, limits, and cost. It needs a compatibility evaluation rather than being treated as a direct replacement.

Acceptance criteria

• A stack with PgBouncer enabled deploys and becomes healthy in a VPC with zero NAT gateways, without public Internet egress.
• The deployment's required AWS service connectivity is provided or clearly documented.
• The PgBouncer setup no longer depends on public package downloads at instance boot, or an equivalent private provisioning path is used.
• An integration or deployment-level regression check covers the zero-NAT configuration.
• Documentation explains the supported network topology and the direct-RDS workaround (addPgbouncer: false) if applicable.

Decision needed

Confirm whether caller-managed VPC endpoints plus a supported prebuilt AMI is the intended no-NAT contract, or select one of the alternatives above.