PostgreSQL Log Statement Flag Should Be Set Appropriately

More Info:

The value of log_statement flag determined the SQL statements that are logged. Valid values are: • none • ddl • mod • all The value ddl logs all data definition statements. The value mod logs all ddl statements, plus data-modifying statements. The statements are logged after a basic parsing is done and statement type is determined, thus this does not logs statements with errors. When using extended query protocol, logging occurs after an Execute message is received and values of the Bind parameters are included. A value of ddl is recommended unless otherwise directed by your organizations logging policy.

Risk Level

Low

Address

Reliability, Security

Compliance Standards

• CIS GCP
• CIS GCP 2.0.0
• Cloudanix Best Practice

Triage and Remediation

Remediation

Remediation

Using Console

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

1. Login to your GCP console and select the project where the PostgreSQL instance is located.
2. In the navigation menu, select SQL under the Storage section.
3. Select the PostgreSQL instance where the misconfiguration exists.
4. Click on the Edit button at the top of the page.
5. Scroll down to the Flags section and look for the log_statement flag.
6. Set the log_statement flag to one of the following values based on your requirements:
   • none: Disables logging of all statements
   • ddl: Logs only data definition language (DDL) statements
   • mod: Logs only statements that modify data (INSERT, UPDATE, DELETE)
   • all: Logs all statements (default)
7. Once you have set the log_statement flag appropriately, click on the Save button at the bottom of the page.
8. Wait for a few minutes for the changes to take effect.

That's it! You have successfully remediated the PostgreSQL Log Statement Flag misconfiguration in GCP using GCP console.

Using CLI

To remediate the PostgreSQL log statement flag misconfiguration in GCP using GCP CLI, follow these 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 that has the PostgreSQL log statement flag misconfiguration.
4. Run the following command to get the current flags of the instance:

   gcloud sql instances describe [INSTANCE_NAME]

   Replace [INSTANCE_NAME] with the name of the instance that you identified in step 3.
5. Look for the databaseFlags field in the output of the previous command. This field contains a list of flags that are currently set for the instance.
6. Check if the log_statement flag is set to all. If it is not set to all, then it is misconfigured.
7. To set the log_statement flag to all, run the following command:

   gcloud sql instances patch [INSTANCE_NAME] --database-flags log_statement=all

   Replace [INSTANCE_NAME] with the name of the instance that you identified in step 3.
8. Verify that the flag has been set correctly by running the gcloud sql instances describe command again and checking the databaseFlags field. The log_statement flag should now be set to all.

By following these steps, you should be able to remediate the PostgreSQL log statement flag misconfiguration in GCP using GCP CLI.

Using Python

To remediate the PostgreSQL Log Statement Flag misconfiguration in GCP using Python, follow these steps:

1. Install the google-cloud-logging library using pip:

pip install google-cloud-logging

2. Create a service account with the necessary permissions to access the GCP project where the PostgreSQL instance is located. Download the JSON key file for the service account.

3. Set the GOOGLE_APPLICATION_CREDENTIALS environment variable to the path of the JSON key file:

export GOOGLE_APPLICATION_CREDENTIALS=/path/to/keyfile.json

4. Use the following Python code to set the PostgreSQL log_statement flag to all:

from google.cloud import logging_v2
from google.oauth2 import service_account

# Set the PostgreSQL instance ID
instance_id = "my-postgres-instance"

# Set the log_statement flag to "all"
log_statement = "all"

# Authenticate using the service account key file
credentials = service_account.Credentials.from_service_account_file(
    "/path/to/keyfile.json"
)

# Create the Logging client
client = logging_v2.LoggingServiceV2Client(credentials=credentials)

# Create the log sink for the PostgreSQL instance
sink_name = f"projects/{client.project}/sinks/postgresql-{instance_id}"
sink = client.get_sink(sink_name)

# Update the sink's filter to include the log_statement flag
new_filter = f'resource.type="gce_instance" AND resource.labels.instance_id="{instance_id}" AND textPayload:"log_statement = {log_statement}"'
sink.filter = new_filter

# Update the sink in GCP
client.update_sink(sink=sink, update_mask={"paths": ["filter"]})

This code will update the log sink for the specified PostgreSQL instance to include the log_statement = all filter. This will ensure that all SQL statements executed on the instance are logged to the Cloud Logging service in GCP.

Using Terraform

resource "google_sql_database_instance" "POSTGRES_INSTANCE" {
  name             = "POSTGRES_INSTANCE_NAME"        # replace with your instance name
  database_version = "POSTGRES_13"                   # replace with your version as needed
  region           = "GCP_REGION"                    # e.g. "us-central1"

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

    # Other settings ...

    database_flags {
      name  = "log_statement"
      value = "ddl"                                  # set to "ddl" per policy
    }

    # If you already have other database_flags, keep them and add/modify only this one.
  }
}

Changing this flag will trigger an in-place update and a brief restart of the Cloud SQL instance, but it does not force resource replacement.

For verification, terraform plan should show a change only to the settings.0.database_flags entry for name = "log_statement" with its value changing from the previous setting to "ddl".