PostgreSQL Log Disconnections Flag Should Be Disabled

More Info:

Ensure that the log_disconnections database flag for Cloud SQL PostgreSQL instance is set to on.

Risk Level

Medium

Address

Security

Compliance Standards

• CIS GCP
• CIS GCP 2.0.0
• Cloudanix Best Practice
• NIST CSF
• PCI
• SOC2

Triage and Remediation

Remediation

Remediation

Using Console

To remediate the PostgreSQL Log Disconnections Flag misconfiguration on GCP using the GCP console, please follow the steps below:

1. Open the GCP console and navigate to the Cloud SQL instances page.
2. Select the instance that has the PostgreSQL database with the misconfiguration.
3. Click on the "Edit" button at the top of the page to edit the instance settings.
4. In the "Flags" section, locate the "log_disconnections" flag.
5. Set the value of "log_disconnections" to "off" to disable the flag.
6. Click on the "Save" button to save the changes.

Once the changes are saved, the PostgreSQL Log Disconnections Flag misconfiguration will be remediated for the GCP instance.

Using CLI

To remediate the PostgreSQL Log Disconnections Flag Should Be Disabled misconfiguration in GCP using GCP CLI, you can follow the below steps:

1. Open the Cloud Shell in the GCP console.
2. Run the following command to authenticate the gcloud CLI tool:

gcloud auth login

3. Set the project where the PostgreSQL instance is located:

gcloud config set project [PROJECT_ID]

4. Get the instance name of the PostgreSQL instance:

gcloud sql instances list

5. Get the current value of the log_disconnections flag for the PostgreSQL instance:

gcloud sql instances describe [INSTANCE_NAME] --format="value(settings.userVariables.log_disconnections)"

6. If the output of the above command is ON, then run the following command to disable the flag:

gcloud sql instances patch [INSTANCE_NAME] --database-flags log_disconnections=off

7. Verify that the log_disconnections flag is disabled by running the following command:

gcloud sql instances describe [INSTANCE_NAME] --format="value(settings.userVariables.log_disconnections)"

The output of the above command should be OFF, which indicates that the flag has been successfully disabled.

Using Python

To remediate the PostgreSQL Log Disconnections Flag Should Be Disabled misconfiguration on GCP using Python, you can follow these steps:

1. Import the necessary libraries:

from google.cloud import logging_v2
from google.cloud.logging_v2 import enums
from google.protobuf import field_mask_pb2

2. Initialize the Logging client:

client = logging_v2.LoggingServiceV2Client()

3. Define the project ID and the log name:

project_id = "your-project-id"
log_name = "cloudsql.googleapis.com%2Fpostgres.log"

4. Define the filter to search for the log entries related to the disconnections flag:

filter_str = 'resource.type="cloudsql_database" AND log_name="{}" AND textPayload:"disconnection".format(log_name)

5. Retrieve the log entries that match the filter:

entries = client.list_log_entries(
    project_id,
    filter_=filter_str,
    order_by=field_mask_pb2.FieldMask(paths=["timestamp"]).serialize(),
).entries

6. For each log entry, extract the instance ID and the zone:

for entry in entries:
    instance_id = entry.resource.labels["database_id"]
    zone = entry.resource.labels["zone"]

7. Disable the PostgreSQL log disconnections flag for each instance:

instances_client = client.instances_client()
instance_path = instances_client.instance_path(project_id, zone, instance_id)

update_mask = field_mask_pb2.FieldMask(paths=["settings"])
settings = instances_client.get(instance_path).settings
settings.database_flags = [
    flag for flag in settings.database_flags if not flag.startswith("log_disconnections=")
]
instances_client.update(instance_path, {"settings": settings}, update_mask)

Note that you will need to authenticate with GCP and have the necessary permissions to perform these actions. Also, make sure to test this remediation in a non-production environment before applying it to your production environment.

Using Terraform

resource "google_sql_database_instance" "POSTGRES_INSTANCE" {
  name             = "POSTGRES_INSTANCE_NAME"        # replace with your instance name
  database_version = "POSTGRES_14"                   # replace with your version
  region           = "INSTANCE_REGION"               # replace with your region

  settings {
    tier = "DB_TIER"                                 # e.g. "db-custom-2-7680"

    # Other existing settings ...

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

    # Keep any other database_flags you already use; do not duplicate names.
  }
}

Changing database_flags will cause a database restart but not a full instance replacement.

After updating, terraform plan should show an in-place update on google_sql_database_instance.POSTGRES_INSTANCE with settings.0.database_flags changing log_disconnections from its previous value to "on".