How to Fix PostgreSQL FATAL: database 'template1' is being accessed by other users

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.

---

The Incident (What does the error mean?)

Raw error output:

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

PostgreSQL uses template1 as the default template for CREATE DATABASE. Any DDL that touches this template requires an AccessExclusiveLock. If even a single idle client — a pgBouncer pre-connect probe, a monitoring agent, a hung migration runner — has an open connection to template1, Postgres refuses the operation entirely. Your provisioning pipeline is now dead in the water.

---

The Attack Vector / Blast Radius

This is not a security exploit vector, but the blast radius in production is severe:

• CI/CD pipelines stall. Any CREATE DATABASE in your migration job fails, blocking every downstream deploy.
• pg_upgrade halts. A failed major-version upgrade mid-flight leaves your cluster in an inconsistent state.
• Connection pool misconfiguration is the usual culprit. pgBouncer, RDS Proxy, or application pools configured to connect to template1 as a health-check target will continuously re-open connections, making the lock contention persistent and self-healing against naive fixes.
• In containerized envs (Kubernetes, ECS): Liveness probes hitting template1 will race against your init container running createdb, causing a thundering herd of FATAL errors at startup.

---

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 configs into ChatGPT? Generic AI tools log your company's ARNs, DB strings, and private keys. StackEngine is a zero-backend, pure Client-Side WASM utility. Drop your failing config into the sandbox above. We redact your secrets locally in the browser and auto-generate the refactored code using your own API key.

---

Prevention in CI/CD

1. Checkov / Terraform static analysis: If you're provisioning RDS or Aurora via Terraform, add a Checkov custom check that asserts no db_name parameter equals template1 or template0.

2. OPA/Conftest policy for Kubernetes manifests:

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])
}

3. Pre-migration hook in your CI pipeline:

#!/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

Plug this into your GitHub Actions, GitLab CI, or Tekton pipeline before any createdb or alembic upgrade step.

4. Alerting: Add a Prometheus alert via pg_stat_activity exporter:

- 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"