Spatial IaC Terraform & Pulumi for GIS

Vault Dynamic Credentials for PostGIS

A static database password shared by a tile renderer fleet, a raster ingestion job and an analyst notebook has three problems that no amount of careful storage fixes. It is long-lived, so a leak is unbounded in time. It is shared, so revoking it for one consumer revokes it for all. And it is anonymous in the database’s own logs, because pg_stat_activity shows one role for every consumer. Dynamic credentials replace it: each consumer requests a credential when it starts, gets a unique role valid for hours, and the credential is revoked automatically when its lease expires. This guide extends Secrets Management for Spatial Pipelines within CI/CD Automation and Governance.

How dynamic credentials work, and what breaks

Vault’s database secrets engine holds one privileged connection to PostGIS and uses it to create short-lived roles on demand. A consumer authenticates to Vault with a workload identity, requests a credential for a named role, and receives a generated username and password with a lease. When the lease expires — or is revoked — Vault drops the role.

Two things break in practice and both are specific to a database rather than to Vault.

Long-running connections outlive their credential. A connection pool that authenticated at start-up holds an open session; when the lease expires the role is dropped, and depending on how the drop is performed the session may be terminated mid-query. For a tile renderer that means a burst of failed tiles at a lease boundary, which looks like an intermittent database problem and is actually a credential lifecycle problem. The fix is for the consumer to renew the lease and reconnect before expiry, not to make leases long.

Object ownership follows the ephemeral role. If a dynamic role creates a table, that table is owned by a role that will shortly cease to exist. For read-only tile rendering this never arises; for an ingestion job that writes, the revocation statement must reassign ownership, or the drop fails and the lease revocation silently backs up.

The lifecycle of a dynamic database credential A workload authenticates to Vault using its own platform identity rather than a shared secret. Vault, holding one privileged connection to the database, creates a uniquely named role with a generated password and returns it with a lease. The workload connects using that credential. Before the lease expires it renews and reconnects, which is the step that avoids the characteristic failure: a connection pool that authenticated at start-up and holds sessions past the lease boundary sees those sessions terminated when the role is dropped, producing a burst of failures that looks like an intermittent database fault. On expiry or explicit revocation Vault drops the role, and any object the role created must have been reassigned first or the drop fails. Workload its own identity Vault creates a unique role PostGIS role valid for hours renew and reconnect before expiry The failure mode a pool holding sessions past the lease boundary It presents as an intermittent database fault on a schedule. It is a credential lifecycle problem. The remedy is renewal and reconnection, not longer leases.

Prerequisites and environment assumptions

Terraform 1.6 or later with hashicorp/vault pinned at ~> 4.2 and the AWS provider at ~> 5.60. A running Vault with the database secrets engine available. A PostGIS cluster reachable from Vault, with a privileged role Vault uses to create and drop roles — and that role should be dedicated to Vault, so its activity is distinguishable in the audit log. Workload identities that Vault can authenticate: a Kubernetes service account, an instance role, or a JWT, so that no bootstrap secret is required.

Size the lease against the consumer. A tile renderer with a long-lived pool wants a lease measured in hours with renewal well before expiry. A batch job that runs for twenty minutes wants a lease slightly longer than its runtime and no renewal at all. Using one lease duration for both guarantees it is wrong for one of them.

Step-by-step implementation

  1. Create a dedicated privileged role for Vault in the database. It needs CREATEROLE and the ability to grant the privileges the dynamic roles will hold. Giving Vault a superuser is common and unnecessary, and it removes the ability to tell Vault’s activity from anything else.

  2. Configure the database secrets engine with a connection and allowed roles. The connection names which Vault roles may use it, which is the boundary that stops a role definition for one database being pointed at another.

  3. Write creation and revocation statements per role. The creation statement grants exactly what the consumer needs — for tile rendering, SELECT on the publishing schema and nothing more. The revocation statement must reassign owned objects before dropping, or a writing role’s revocation fails and leases accumulate.

  4. Bind each workload identity to exactly one Vault role. The renderer can request the read-only credential and nothing else. This is what turns “we use dynamic credentials” into per-consumer authorisation.

  5. Make the consumer renew and reconnect. The consumer library or sidecar must renew the lease at a fraction of its duration and refresh the pool before expiry. Without this step the design produces scheduled failures, and the failures will be blamed on the database.

terraform {
  required_version = ">= 1.6.0"
  required_providers {
    vault = { source = "hashicorp/vault", version = "~> 4.2" }
  }
}

resource "vault_mount" "db" {
  path = "postgis"
  type = "database"
}

resource "vault_database_secret_backend_connection" "postgis" {
  backend = vault_mount.db.path
  name    = "spatial-prod"
  # Naming the allowed roles here is the boundary that stops a role definition
  # for one database being pointed at another.
  allowed_roles = ["tile-renderer", "raster-ingest"]

  postgresql {
    connection_url = "postgresql://{{username}}:{{password}}@postgis.internal:5432/gis?sslmode=verify-full"
    username       = var.vault_db_user # dedicated, not superuser
    password       = var.vault_db_password
  }
}

# Read-only: tile rendering needs SELECT and nothing else. A lease measured in
# hours suits a long-lived pool that renews.
resource "vault_database_secret_backend_role" "tile_renderer" {
  backend             = vault_mount.db.path
  name                = "tile-renderer"
  db_name             = vault_database_secret_backend_connection.postgis.name
  default_ttl         = 14400 # 4h
  max_ttl             = 86400 # 24h

  creation_statements = [
    "CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' IN ROLE tile_reader;",
    "GRANT USAGE ON SCHEMA tiles TO \"{{name}}\";",
    "GRANT SELECT ON ALL TABLES IN SCHEMA tiles TO \"{{name}}\";",
    "ALTER ROLE \"{{name}}\" SET search_path = tiles;",
  ]

  revocation_statements = [
    "REVOKE ALL PRIVILEGES ON SCHEMA tiles FROM \"{{name}}\";",
    "REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA tiles FROM \"{{name}}\";",
    "DROP ROLE IF EXISTS \"{{name}}\";",
  ]
}

# Writing role: the revocation MUST reassign owned objects first, or the drop
# fails and revocations silently accumulate.
resource "vault_database_secret_backend_role" "raster_ingest" {
  backend     = vault_mount.db.path
  name        = "raster-ingest"
  db_name     = vault_database_secret_backend_connection.postgis.name
  default_ttl = 3600  # slightly longer than the job, and no renewal
  max_ttl     = 7200

  creation_statements = [
    "CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' IN ROLE ingest_writer;",
    "GRANT USAGE ON SCHEMA staging TO \"{{name}}\";",
    "GRANT INSERT, UPDATE, SELECT ON ALL TABLES IN SCHEMA staging TO \"{{name}}\";",
  ]

  revocation_statements = [
    # Without this, a table created by the ephemeral role blocks the drop.
    "REASSIGN OWNED BY \"{{name}}\" TO ingest_writer;",
    "DROP OWNED BY \"{{name}}\";",
    "DROP ROLE IF EXISTS \"{{name}}\";",
  ]
}

# Each workload identity may request exactly one role. This is what turns
# dynamic credentials into per-consumer authorisation.
resource "vault_policy" "tile_renderer" {
  name = "tile-renderer"
  policy = <<-EOT
    path "postgis/creds/tile-renderer" {
      capabilities = ["read"]
    }
  EOT
}
What the database sees, before and after With a shared static password, every session in pg_stat_activity reports the same role name regardless of which service opened it, so a query consuming the whole connection budget cannot be attributed to a consumer and revoking the credential affects every consumer at once. With dynamic credentials, each consumer's sessions carry a uniquely generated role name derived from the Vault role it requested, so the offending consumer is identifiable from the database's own view and its lease can be revoked without touching anything else. Shared static password gis_app · renderer session gis_app · ingest session gis_app · analyst session indistinguishable, and revocable only together Dynamic credentials v-tile-renderer-a91f · renderer v-raster-ingest-3c02 · ingest v-analyst-7d55 · analyst attributable, and revocable one at a time

Verification

Request a credential as the renderer identity and confirm you receive a generated username with a lease. Connect with it and run a tile query. Then confirm the boundary: request the raster-ingest credential with the renderer’s identity and require a permission denial, because that is the difference between dynamic credentials and merely rotating ones.

Confirm the revocation actually works, which is where writing roles fail. Take an ingest credential, create a table with it, revoke the lease, and confirm the role is dropped rather than left behind by a failed revocation — Vault reports revocation failures, but only if someone is looking. Then check pg_roles after a day of operation for accumulated v- prefixed roles; a growing count is the signature of revocations that are failing quietly.

Finally, test the lease boundary deliberately. Let a renderer run through a lease expiry with renewal disabled and observe the failure, so you know its shape; then enable renewal and confirm the failure disappears. Knowing what it looks like is what stops it being misdiagnosed as a database fault at 3am.

The count of generated roles is the health signal In steady operation the number of Vault-generated roles present in the database should be roughly stable, tracking the number of live leases. A count that climbs week over week means revocations are failing and roles are being left behind. The usual cause is a writing role that created an object the revocation statement did not reassign, so the drop fails and the lease revocation quietly backs up. Vault reports revocation failures, but only to whoever is looking, whereas the role count in the database is a number that can be alarmed on without anyone remembering to check. Healthy role count roughly stable tracking the number of live leases revocations are completing Failing quietly role count climbs week over week a writing role owned an object the revocation never reassigned Vault reports revocation failures — but only to whoever is looking. The role count can be alarmed on.

Preventing recurrence

  • Alarm on the count of dynamic roles in the database. It should be roughly stable. A rising count means revocations are failing, and it is the only early signal.
  • Set lease duration per consumer, not per platform. A single value is wrong for either the long-lived pool or the short batch job.
  • Reassign ownership in every writing role’s revocation. It is the difference between a working revocation and one that fails silently every time.
  • Keep the Vault database role dedicated and non-superuser. It bounds what a Vault compromise reaches and makes its activity distinguishable in the audit trail.

Frequently Asked Questions

Why do tiles fail in a burst every few hours?

Almost certainly a lease expiry with no renewal. The pool authenticated at start-up, the role was dropped when the lease ended, and the open sessions failed together. Enable renewal with reconnection well before expiry; lengthening the lease only moves the burst.

Do dynamic credentials replace the connection pool?

No, and the interaction is the main design consideration. The pool must be refreshable — able to drain and re-establish connections with a new credential — because otherwise it holds sessions authenticated with a credential that no longer exists.

Is this worth it for a read-only tile renderer?

Yes, for attribution as much as for security. Being able to see in pg_stat_activity which consumer is holding connections during a saturation incident is worth the setup on its own, and it is impossible with a shared role.

What if Vault is unavailable?

Running workloads keep their current credential until its lease expires, so a short outage is survivable; new workloads cannot start. That makes Vault a dependency of deployment rather than of steady-state serving, which is the right shape — but it does mean Vault’s availability belongs in the platform’s own incident planning.