Sql log connections flag remediation

Using Console

To remediate the “PostgreSQL Log Connections Flag Should Be On” misconfiguration on GCP using the GCP console, follow these steps:

Go to the GCP Console and select the project that contains the PostgreSQL instance that needs to be remediated.
In the left navigation pane, select “SQL” under the “Storage” section.
Select the PostgreSQL instance that needs to be remediated.
Click on the “Edit” button at the top of the page.
Scroll down to the “Flags” section and click on the “Add Flag” button.
In the “Name” field, enter “log_connections”.
In the “Value” field, enter “on”.
Click on the “Save” button at the bottom of the page to apply the changes.
Wait for a few minutes for the changes to take effect.

After completing these steps, the “PostgreSQL Log Connections Flag Should Be On” misconfiguration will be remediated for the PostgreSQL instance on GCP.

Using CLI

To remediate the PostgreSQL Log Connections Flag Should Be On misconfiguration for GCP using GCP CLI, follow these steps:

Open the Cloud Shell in the GCP Console.
Run the following command to list the available PostgreSQL instances in your project:
gcloud sql instances list
Note down the instance name of the PostgreSQL instance you want to remediate.
Run the following command to enable the log_connections flag for the PostgreSQL instance:
gcloud sql instances patch [INSTANCE_NAME] --database-flags log_connections=on
Replace [INSTANCE_NAME] with the name of your PostgreSQL instance.
Confirm that the log_connections flag has been enabled by running the following command:
gcloud sql instances describe [INSTANCE_NAME] | grep log_connections
Replace [INSTANCE_NAME] with the name of your PostgreSQL instance. The output should show that the log_connections flag is set to “on”.
log_connections: on
Your PostgreSQL instance is now remediated with the log_connections flag enabled.

Using Python

To remediate the PostgreSQL log connections flag misconfiguration in GCP using Python, follow these steps:

Install the google-cloud-secret-manager and google-auth Python libraries using pip:
pip install google-cloud-secret-manager google-auth

Import the necessary libraries and authenticate to the GCP project:

from google.cloud import secretmanager
from google.oauth2 import service_account

credentials = service_account.Credentials.from_service_account_file('/path/to/key.json')
client = secretmanager.SecretManagerServiceClient(credentials=credentials)

Replace /path/to/key.json with the path to your GCP service account key file.

Retrieve the value of the postgres-config secret:

secret_name = "postgres-config"
project_id = "my-project-id"
version = "latest"

name = f"projects/{project_id}/secrets/{secret_name}/versions/{version}"
response = client.access_secret_version(request={"name": name})
config = response.payload.data.decode("UTF-8")

Update the postgresql.conf file to enable logging of connections:

config_lines = config.split("\n")
new_config_lines = []

for line in config_lines:
    if line.startswith("#log_connections = off"):
        new_config_lines.append("log_connections = on")
    else:
        new_config_lines.append(line)

new_config = "\n".join(new_config_lines)

Write the updated configuration back to the secret:

parent = f"projects/{project_id}"
payload = {"data": new_config.encode("UTF-8")}
response = client.update_secret(request={"secret": {"name": secret_name, "payload": payload}, "update_mask": {"paths": ["payload"]}, "parent": parent})

This will update the postgres-config secret with the new configuration that enables logging of connections.

Verify that the configuration was updated successfully by checking the postgresql.conf file on the PostgreSQL server.

# Connect to the PostgreSQL server
psql -h <hostname> -U <username> -d <database>

# Check the value of the log_connections parameter
SHOW log_connections;

The output should be on.

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           = "GCP_REGION"                      # e.g. "us-central1"

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

    # keep any existing flags and add/ensure log_connections is on
    database_flags {
      name  = "log_connections"
      value = "on"
    }

    # example of another existing flag you might already have
    # database_flags {
    #   name  = "max_connections"
    #   value = "100"
    # }
  }
}

Changing database_flags is an in‑place update for Cloud SQL (no Terraform resource replacement), but it may cause a brief restart of the instance and momentary connection disruption.To verify, terraform plan should show an update to google_sql_database_instance.POSTGRES_INSTANCE.settings[0].database_flags adding or changing a flag with name = "log_connections" and value = "on".

GCP Introduction

GCP Pricing

GCP Threats

GCP Misconfigurations

Resources

GCP Threats

Sql log connections flag remediation

Triage and Remediation

Remediation

​Triage and Remediation

​Remediation

​

Triage and Remediation

Remediation