Skip to main content

SQL Log Planner Stats Flag Remediation

Triage and Remediation​

Remediation​

Using Console

To remediate the PostgreSQL Log Planner Stats Flag misconfiguration for GCP using GCP console, you can follow the below steps:

  1. Login to your GCP console.
  2. Navigate to the Cloud SQL instances page.
  3. Select the instance that you want to remediate.
  4. Click on the Edit button at the top of the page.
  5. Scroll down to the Flags section and click on the Add flag button.
  6. In the Flag name field, enter "log_planner_stats" (without quotes).
  7. In the Flag value field, enter "off" (without quotes).
  8. Click on the Save button at the bottom of the page to save the changes.

Once you have completed these steps, the PostgreSQL Log Planner Stats flag will be set to off, and the misconfiguration will be remediated.

Using CLI

To remediate the PostgreSQL Log Planner Stats Flag misconfiguration in GCP using GCP CLI, you can follow the below steps:

  1. Open the Cloud Shell in your GCP console.

  2. Run the following command to list all the instances in your project:

    gcloud sql instances list

  3. Identify the instance for which you want to remediate the misconfiguration and note down its instance name.

  4. Run the following command to get the current value of the PostgreSQL log_planner_stats flag:

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

    Replace [INSTANCE_NAME] with the name of your instance.

  5. If the output of the above command is "on", then run the following command to turn off the PostgreSQL log_planner_stats flag:

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

    Replace [INSTANCE_NAME] with the name of your instance.

  6. Verify the PostgreSQL log_planner_stats flag has been turned off by running the following command:

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

    Replace [INSTANCE_NAME] with the name of your instance.

  7. Ensure that the PostgreSQL log_planner_stats flag is turned off for all the instances in your project to avoid any further misconfiguration.

By following the above steps, you can remediate the PostgreSQL Log Planner Stats Flag misconfiguration in GCP using GCP CLI.

Using Python

To remediate the "PostgreSQL Log Planner Stats Flag Should Be Off" misconfiguration for GCP using Python, you can follow the below steps:

Step 1: Import the required libraries

from google.cloud import bigquery
from google.oauth2 import service_account

Step 2: Set up the authentication credentials

credentials = service_account.Credentials.from_service_account_file('path/to/credentials.json')

Step 3: Create a BigQuery client object

client = bigquery.Client(project='project_id', credentials=credentials)

Step 4: Run the following query to check the current value of the log_planner_stats flag:

query = """
SELECT name, setting
FROM pg_settings
WHERE name = 'log_planner_stats'
"""
query_job = client.query(query)
results = query_job.result()
for row in results:
    print("{}: {}".format(row.name, row.setting))

Step 5: If the value of log_planner_stats is on, run the following query to turn it off:

update_query = """
ALTER SYSTEM SET log_planner_stats = off
"""
update_job = client.query(update_query)
update_job.result()

Note: This query will update the configuration file for PostgreSQL, but it will not take effect until the database is restarted.

Step 6: Verify that the log_planner_stats flag is now set to off by running the query in Step 4 again.

By following the above steps, you can remediate the "PostgreSQL Log Planner Stats Flag Should Be Off" misconfiguration for GCP using Python.

Using Terraform
resource "google_sql_database_instance" "POSTGRES_INSTANCE" {
  name             = "POSTGRES_INSTANCE_NAME"   # replace with your instance name
  database_version = "POSTGRES_15"             # replace with your version
  region           = "INSTANCE_REGION"         # e.g. us-central1

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

    # Other settings …

    database_flags {
      name  = "log_planner_stats"
      value = "off"
    }
  }
}

Changing this flag may trigger a Cloud SQL restart, causing a brief outage.

To verify, terraform plan should show a change on google_sql_database_instance.POSTGRES_INSTANCE.settings[0].database_flags with log_planner_stats value moving from "on" (or absent) to "off".