Spatial IaC Terraform & Pulumi for GIS

Rotating PostGIS Credentials in Terraform Without Downtime

You need to rotate the PostGIS master or application password on a schedule, but GeoServer and your tile-rendering fleet hold long-lived pooled connections that a naive rotation would drop mid-query — turning a routine security task into a burst of HTTP 500s on your map. This guide shows how to run AWS Secrets Manager managed rotation with a dual-secret (blue/green) strategy so the old credential stays valid until every consumer has moved to the new one, keeping spatial queries alive throughout. It is the hands-on companion to Secrets and Credential Management for Spatial Pipelines and sits within the CI/CD, Drift Detection & Policy Governance framework.

Symptom Identification and Triage

A rotation that goes wrong announces itself in the space of a few seconds around the rotation timestamp. Classify what you are seeing before you touch the database, because the remediation differs sharply between a rotation that dropped connections and one that never completed.

  • PSQLException: FATAL: password authentication failed in GeoServer logs, starting exactly at the rotation time: consumers are still presenting the old password after the database changed it. This is the classic single-secret rotation drop — the value changed everywhere at once and pooled connections went stale.
  • Rotation Lambda CloudWatch log shows setSecret succeeded but testSecret failed: the new credential was written to the database but cannot authenticate, usually because a dependent role or pg_hba rule was not updated. The AWSPENDING stage is stuck and the secret never promoted to AWSCURRENT.
  • Intermittent auth failures that decay over a few minutes, then stop: connection pools are draining stale connections and reopening with the new credential. If you rotated without a dual-secret grace window, this is the visible tail of the outage.
  • SecretsManager RotationFailed event in CloudTrail with no database change: the Lambda could not reach the database — a security-group or subnet path problem — so nothing rotated. Route this to network triage, not credential logic.
Triage: classify a PostGIS rotation failure before acting A top-down decision tree. The symptom of authentication failures after rotation leads to checking the rotation Lambda CloudWatch log and CloudTrail events, which feeds a decision on which signal dominates. Three outcomes branch out: a testSecret failure means the pending credential is invalid and rotation is stuck at AWSPENDING, routed to fixing the setSecret step; immediate auth failures precisely at the rotation timestamp mean stale pooled connections were dropped, routed to the dual-secret grace window and pool reload; a RotationFailed event with no database change means the Lambda could not reach the database, routed to network and security-group triage. Auth failures after rotation on GeoServer / tile fleet Check rotation Lambda log + CloudTrail rotation events Which signal? Pending invalid stuck at AWSPENDING fix setSecret / grants Stale connections dual-secret grace window + pool reload Lambda unreachable RotationFailed, no change network / SG triage testSecret failed drop at rotation time no DB change

Prerequisites and Environment Assumptions

This procedure assumes an AWS target: an RDS PostgreSQL instance with PostGIS, its master credential in AWS Secrets Manager, and GeoServer plus a tile-rendering fleet consuming the database through connection pools. The database itself should follow the PostGIS Cluster Provisioning baseline. To execute the rotation you will need:

  • Terraform >= 1.10.0 with the AWS provider pinned. Unpinned providers are a common source of phantom rotation-resource diffs:

    terraform {
  required_version = ">= 1.10.0"
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 5.60"
    }
  }
}

  • The alternating-users rotation strategy. AWS provides a canonical SecretsManagerRDSPostgreSQLRotationMultiUser Lambda. It rotates between two database users (gis_app_blue and gis_app_green) so that while one credential is being changed, the other remains valid — this is what makes zero-downtime rotation possible. A superuser secret must exist for the Lambda to create and alter the app users.

  • IAM permissions for the rotation Lambda: secretsmanager:GetSecretValue, PutSecretValue, UpdateSecretVersionStage on the app secret, and GetSecretValue on the superuser secret; plus VPC execution permissions so it runs inside the database’s subnets.

  • Network reachability: the Lambda must reach RDS on port 5432 through the database security group, and Secrets Manager through a VPC endpoint. A missing path here surfaces as the RotationFailed/no-change signal from triage above.

  • Pool configuration you can reload without a full restart — GeoServer’s JNDI datastore or a PgBouncer layer — so consumers pick up a new credential by reconnecting, not by bouncing the whole service.

Step-by-Step Remediation

The strategy is dual-secret alternating-users rotation: two database roles, only one active at a time, with the inactive one being rotated. Consumers always read the current secret, so a reconnect after rotation lands on a valid credential without any window where the presented password is wrong.

  1. Confirm the two app roles exist. The multi-user Lambda alternates between two users; if only one exists, the first rotation will fail its createSecret step. Create both roles with identical privileges so either can serve production traffic.

    -- Run once as the master/superuser. Both roles are interchangeable app users.
CREATE ROLE gis_app_blue  LOGIN PASSWORD 'placeholder-rotated-immediately';
CREATE ROLE gis_app_green LOGIN PASSWORD 'placeholder-rotated-immediately';
GRANT CONNECT ON DATABASE gis TO gis_app_blue, gis_app_green;
GRANT USAGE ON SCHEMA public TO gis_app_blue, gis_app_green;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public
  TO gis_app_blue, gis_app_green;
-- PostGIS metadata tables must be readable so ST_ functions resolve SRIDs.
GRANT SELECT ON spatial_ref_sys TO gis_app_blue, gis_app_green;

    The spatial_ref_sys grant matters specifically for spatial workloads: without read access to it, a rotated role can authenticate but every ST_Transform fails to resolve its SRID, which looks like a rotation success but breaks reprojection.

  2. Declare the app secret with the multi-user JSON shape. The rotation Lambda expects a specific structure so it knows the current user and the superuser that can alter it.

    resource "aws_secretsmanager_secret" "gis_app" {
  name       = "prod/gis/postgis-app"
  kms_key_id = aws_kms_key.secrets.arn
}

resource "aws_secretsmanager_secret_version" "gis_app" {
  secret_id = aws_secretsmanager_secret.gis_app.id
  secret_string = jsonencode({
    engine               = "postgres"
    host                 = aws_db_instance.postgis.address
    port                 = 5432
    dbname               = "gis"
    username             = "gis_app_blue"        # active role; Lambda flips this
    password             = "placeholder-rotated" # replaced on first rotation
    masterarn            = aws_secretsmanager_secret.postgis_master.arn
  })

  lifecycle {
    ignore_changes = [secret_string] # rotation owns the value after creation
  }
}

    Note: ignore_changes = [secret_string] is essential — without it, every terraform apply would overwrite the rotated password with the placeholder, silently reverting the rotation and dropping consumers on the next plan.

  3. Attach managed rotation with a schedule. The rotation resource points at the Lambda and sets the cadence. A 30-day schedule with a defined rotation window keeps the change predictable.

    resource "aws_secretsmanager_secret_rotation" "gis_app" {
  secret_id           = aws_secretsmanager_secret.gis_app.id
  rotation_lambda_arn = aws_lambda_function.rds_rotation.arn

  rotation_rules {
    automatically_after_days = 30
  }
}

  4. Make consumers read the secret at connection time, not at deploy time. GeoServer and the tile workers must resolve the credential from Secrets Manager (or a cached local copy refreshed on auth failure), so that when the active user flips from blue to green, a reconnect picks up the new value. Configure the datastore to fetch on connect and to retry once on an authentication error, which converts a stale-connection failure into a transparent reconnect rather than a 500.

  5. Reload the pool after rotation instead of restarting the service. Trigger a pool refresh so idle connections are recycled onto the new credential without dropping in-flight spatial queries. With PgBouncer, RELOAD; re-reads the credential; with GeoServer’s JNDI pool, invalidate idle connections so new ones authenticate fresh. In-flight queries on the still-valid old role complete normally because the old credential is not revoked until the next rotation cycle.

Verification

Confirm the rotation completed and that spatial queries actually work under the new credential before closing out.

  1. Check the secret promoted cleanly. The new version must carry AWSCURRENT and the previous one AWSPREVIOUS, with no lingering AWSPENDING:

    aws secretsmanager describe-secret --secret-id prod/gis/postgis-app \
  --query 'VersionIdsToStages'

  2. Run a real spatial query as the rotated role. Authentication alone is not proof — confirm PostGIS functions resolve, which exercises the spatial_ref_sys grant from step 1:

    PGPASSWORD="$(aws secretsmanager get-secret-value \
  --secret-id prod/gis/postgis-app --query SecretString --output text \
  | python3 -c 'import sys,json; print(json.load(sys.stdin)["password"])')" \
psql -h "$PGHOST" -U gis_app_blue -d gis -c \
  "SELECT ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(-73.98,40.75),4326),3857));"

    A returned projected coordinate proves the credential authenticates and the SRID catalog is reachable.

  3. Probe a live tile. Request a known tile through GeoServer and assert a 200 with image/png, confirming the rendering path reconnected on the new credential without dropping requests. A flat request-error rate across the rotation timestamp in your metrics is the definitive signal that the rotation was non-disruptive.

Preventing Recurrence

  • Keep the dual-secret grace window. Never revoke the old role’s password in the same operation that promotes the new one. The alternating-users strategy inherently keeps the previous credential valid for a full cycle, so consumers always have a working credential to reconnect with.
  • Add a scheduled rotation health check. Run a canary that fetches the current secret and executes a spatial query hourly; alert if it fails, so a stuck AWSPENDING is caught long before the next scheduled rotation compounds it.
  • Guard the ignore_changes rule with policy. A Policy as Code for Spatial Resources rule that flags any rotation-managed secret version missing ignore_changes = [secret_string] prevents the regression where an apply reverts a rotated password.
  • Never log the resolved credential. Keep the rotation and consumer pipelines free of debug logging, per Secrets and Credential Management for Spatial Pipelines, so a rotated value is never archived in a CI log the moment after it is created.

Frequently Asked Questions

Why use two database users instead of just changing one password?

With a single user, the moment the password changes in the database every pooled connection presenting the old value fails, and there is no valid credential to reconnect with until consumers refresh. Alternating between two users means the inactive user is rotated while the active one keeps serving, so there is always a working credential and reconnects are transparent.

Will a rotation drop in-flight spatial queries?

No, if you keep the grace window. In-flight queries run on already-authenticated connections and complete normally; the old credential is not revoked until the next rotation cycle. Only new connections use the new credential, and they authenticate cleanly because the active role’s password is valid throughout.

Why did my rotated role authenticate but ST_Transform start failing?

The new role lacks SELECT on spatial_ref_sys, so it can log in but cannot resolve SRIDs. Grant read on spatial_ref_sys to both alternating roles when you create them, so every rotated credential can run the full PostGIS function set, not just connect.

Do I need to run terraform apply to rotate?

No. Once aws_secretsmanager_secret_rotation is in place, Secrets Manager drives rotation on its schedule independently of Terraform. The ignore_changes = [secret_string] lifecycle rule ensures a routine apply does not fight the rotation by rewriting the value.