PostgreSQL Template1 Concurrent Access Fix

Threat/Impact Level: HIGH | Downtime Risk: HIGH | Time to Fix: 5–15 mins

TL;DR

• What broke: A CREATE DATABASE, DROP DATABASE, or pg_upgrade operation requires exclusive access to template1, but one or more idle/active backend connections are holding it open.
• How to fix it: Terminate all connections to template1 via pg_terminate_backend(), then re-run your DDL.
• Shortcut: Use our Client-Side Sandbox above to auto-refactor your connection pool config and generate the termination script without pasting secrets into a third-party AI.

FATAL:  database "template1" is being accessed by other users
DETAIL:  There is 1 other session using the database.

How to Fix It (The Solution)

Step 1: Identify the blocking backends

SELECT pid, usename, application_name, client_addr, state, query_start, query
FROM pg_stat_activity
WHERE datname = 'template1';

Step 2 — Basic Fix: Terminate blocking connections

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'template1'
  AND pid <> pg_backend_pid();

Then immediately re-run your CREATE DATABASE or DROP DATABASE command. You have a narrow window before pools reconnect.

Step 3 — Enterprise Best Practice: Fix the root cause in your connection pool

The real fix is ensuring no pool or probe ever connects to template1.

pgBouncer (pgbouncer.ini):

[databases]
- template1 = host=127.0.0.1 port=5432 dbname=template1
+ ; Remove template1 entirely. Point health checks at a dedicated probe DB.
+ probe_db = host=127.0.0.1 port=5432 dbname=postgres

[pgbouncer]
- auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
+ server_connect_query = SET application_name='pgbouncer_probe';

Kubernetes liveness probe (Deployment spec):

livenessProbe:
  exec:
    command:
-     - psql
-     - -U postgres
-     - -d template1
-     - -c
-     - "SELECT 1"
+     - psql
+     - -U postgres
+     - -d postgres
+     - -c
+     - "SELECT 1"

RDS / Aurora: If using RDS Proxy, ensure the proxy target group points to your application database, not template1 or postgres unless explicitly required.

Restrict connections to template1 at the Postgres level (nuclear option for hardened envs):

# pg_hba.conf
+ # Block all non-superuser connections to template1
+ local   template1   all                    reject
+ host    template1   all   0.0.0.0/0        reject
+ host    template1   all   ::/0             reject

Then pg_reload_conf() or SELECT pg_reload_conf();.

💡 Tired of pasting proprietary infrastructure configs into public LLMs? Generic AI interfaces log your company's ARNs, connection strings, and private keys. StackEngine enforces client-side zero-trust boundaries—the sandbox above executes 100% locally in your browser. Drop your broken manifest above to redact secrets locally and synthesize deterministic patch drafts with your own API key.

(For CI/CD pipeline automation, install via npm i -g stackengine).

package k8s.probes

deny[msg] {
  container := input.spec.containers[_]
  probe := container.livenessProbe.exec.command
  probe[_] == "template1"
  msg := sprintf("Container '%v' liveness probe must not target template1", [container.name])
}

#!/bin/bash
# pre-migrate.sh — fail fast if template1 has active connections
CONNECTIONS=$(psql "$DATABASE_URL" -tAc \
  "SELECT count(*) FROM pg_stat_activity WHERE datname='template1' AND pid <> pg_backend_pid();")
if [ "$CONNECTIONS" -gt "0" ]; then
  echo "FATAL: $CONNECTIONS connection(s) open on template1. Aborting migration."
  exit 1
fi

- alert: Template1ActiveConnections
  expr: pg_stat_activity_count{datname="template1"} > 0
  for: 1m
  labels:
    severity: warning
  annotations:
    summary: "Active connections on template1 — DDL operations will fail"