Review Cloud SQL PostgreSQL disconnection logging

Description

PostgreSQL log_disconnections logs session termination and duration. Without these records, investigating connection history and session lifetimes can be harder.

Termination logs alone do not explain every abnormal termination or show the SQL executed. Use them alongside required connection and error records.

Potential impact

Evidence of when sessions ended and how long they lasted may be missing.
Investigating connection problems or unusual session use can take longer.

Remediation

Set log_disconnections to on when session termination records are required. Review the connection and error logs needed alongside them.
Connect and end a test session to verify log receipt and recorded duration. Manage retention, access permissions and log volume.

Examples

These examples show part of the instance settings. Choose a supported engine version and machine type, and provide omitted required configuration.

Before

hcl

resource "google_sql_database_instance" "db" {
  name             = "postgres-instance"
  database_version = "POSTGRES_15"
  region           = "us-central1"

  settings {
    database_flags {
      name  = "log_disconnections"
      value = "off"
    }
  }
}

After

hcl

resource "google_sql_database_instance" "db" {
  name             = "postgres-instance"
  database_version = "POSTGRES_15"
  region           = "us-central1"

  settings {
    tier = "db-f1-micro"

    database_flags {
      name  = "log_disconnections"
      value = "on"
    }
  }
}

Explanation:

Before: Disconnection logging is disabled.
After: Disconnection logging is enabled. The setting does not itself block abnormal sessions.