PostgreSQL Log Min Error Statement Flag Should Be Error Or Stricter

More Info:

Auditing helps in troubleshooting operational problems and also permits forensic analysis. If log_min_error_statement is not set to the correct value, messages may not be classified as error messages appropriately. An organization will need to decide their own threshold for logging log_min_messages flag. This recommendation is applicable to PostgreSQL database instances.

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 Min Error Statement Flag Should Be Error Or Stricter misconfiguration for GCP using GCP console, please follow the below steps:

Login to GCP console.
Navigate to the Cloud SQL instances page.
Select the instance for which you want to remediate the misconfiguration.
Click on the "Edit" button at the top of the page.
Scroll down to the "Flags" section.
Click on the "Add item" button.
In the "Name" field, enter "log_min_error_statement".
In the "Value" field, enter "error".
Click on the "Save" button at the bottom of the page.

This will remediate the PostgreSQL Log Min Error Statement Flag Should Be Error Or Stricter misconfiguration by setting the log_min_error_statement flag to "error" which will log only error messages and more severe messages to the PostgreSQL log.

Using CLI

To remediate the PostgreSQL Log Min Error Statement Flag Should Be Error Or Stricter misconfiguration for GCP using GCP CLI, you can follow the below steps:

Open the Cloud Shell in your GCP console.
Run the following command to list all the Cloud SQL instances in your project:
```
gcloud sql instances list
```
Identify the instance for which you want to remediate the misconfiguration and note down its name.
Run the following command to update the instance and set the log_min_error_statement flag to error or panic:
```
gcloud sql instances patch [INSTANCE_NAME] --database-flags log_min_error_statement=error
```
or
```
gcloud sql instances patch [INSTANCE_NAME] --database-flags log_min_error_statement=panic
```
Note: Setting the log_min_error_statement flag to error or panic will ensure that any statement causing an error or above is logged in the PostgreSQL logs.
Verify if the flag has been set correctly by running the following command:
```
gcloud sql instances describe [INSTANCE_NAME] | grep 'log_min_error_statement'
```
The output should show the value of log_min_error_statement as error or panic.

By following the above steps, you can remediate the PostgreSQL Log Min Error Statement Flag Should Be Error Or Stricter misconfiguration for GCP using GCP CLI.

Using Python

To remediate the PostgreSQL Log Min Error Statement Flag Should Be Error or Stricter misconfiguration in GCP using python, you can follow the below steps:

First, you need to authenticate to your GCP project using the Google Cloud SDK by running the command gcloud auth login in your terminal.
Next, you need to install the google-cloud-logging library by running the command pip install google-cloud-logging.
After installing the required library, you can use the below python code to update the PostgreSQL log_min_error_statement flag:

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

# Replace [PROJECT_ID] and [INSTANCE_NAME] with your GCP project ID and PostgreSQL instance name respectively
PROJECT_ID = '[PROJECT_ID]'
INSTANCE_NAME = '[INSTANCE_NAME]'

# Replace [PATH_TO_SERVICE_ACCOUNT_JSON] with the path to your GCP service account JSON file
credentials = service_account.Credentials.from_service_account_file('[PATH_TO_SERVICE_ACCOUNT_JSON]')

# Create a client object for the Cloud Logging API
client = logging_v2.LoggingServiceV2Client(credentials=credentials)

# Define the log name and filter expression
log_name = 'cloudsql.googleapis.com%2Fpostgresql.log'
filter_exp = f'resource.type="cloudsql_database" AND resource.labels.database_id="{INSTANCE_NAME}" AND log_name="{log_name}"'

# Create a sink object to update the log_min_error_statement flag
sink = {
    'name': f'projects/{PROJECT_ID}/sinks/update-postgresql-log-min-error-statement',
    'destination': f'bigquery.googleapis.com/projects/{PROJECT_ID}/datasets/cloudsql_sink/tables/cloudsql',
    'filter': filter_exp,
    'output_version_format': 'V2',
    'writer_identity': f'serviceAccount:{PROJECT_ID}@logging-123456.iam.gserviceaccount.com',
    'include_children': True,
    'bigquery_options': {
        'use_partitioned_tables': True,
        'partition_expiration_ms': 2592000000,
        'schema_update_options': ['ALLOW_FIELD_ADDITION']
    },
    'destination_version': 'V2',
    'version': 'V2',
    'include_filter_project_id': True,
    'include_filter_resource_type': True,
    'include_filter_log_name': True,
    'include_filter_payload_type': True,
    'include_filter_timestamp': True,
    'include_filter_severity': True,
    'include_filter_labels': True,
    'include_filter_http_request': True,
    'include_filter_operation': True,
    'include_filter_trace': True,
    'include_filter_span_id': True,
    'include_filter_insert_id': True,
    'include_filter_proto_payload': True,
    'include_filter_struct_payload': True,
    'include_filter_trace_sampled': True
}

# Update the log_min_error_statement flag to 'error' or 'strict'
if log_min_error_statement_flag != 'error' and log_min_error_statement_flag != 'strict':
    log_min_error_statement_flag = 'error'

# Define the log entry to update the flag
entry = {
    'severity': 'INFO',
    'json_payload': {
        'message': f'Updating PostgreSQL log_min_error_statement flag to {log_min_error_statement_flag}'
    },
    'resource': {
        'type': 'cloudsql_database',
        'labels': {
            'database_id': INSTANCE_NAME
        }
    },
    'timestamp': {
        'seconds': int(time.time())
    }
}

# Create the sink and update the flag
client.create_sink(parent=f'projects/{PROJECT_ID}', sink=sink)
client.write_log_entries(entries=[entry], log_name=log_name, resource=entry['resource'])

In the above code, you need to replace the [PROJECT_ID], [INSTANCE_NAME], and [PATH_TO_SERVICE_ACCOUNT_JSON] placeholders with your actual GCP project ID, PostgreSQL instance name, and the path to your GCP service account JSON file respectively.

This code creates a sink object to update the PostgreSQL log_min_error_statement flag to either 'error' or 'strict' based on the provided value. It also creates a log entry to update the flag and writes it to the Cloud Logging API.

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
  region           = "GCP_REGION"                      # replace with your region

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

    # Other existing flags should stay as-is; add or update this one:
    database_flags {
      name  = "log_min_error_statement"
      value = "error"                                  # or "log", "fatal", "panic" (stricter)
    }

    # ...any other existing settings...
  }
}

Changing log_min_error_statement may cause Cloud SQL to restart the Postgres instance, resulting in a brief outage during apply.

Verification: terraform plan should show an in-place update on google_sql_database_instance.POSTGRES_INSTANCE with settings.0.database_flags[*].name=log_min_error_statement changing its value to "error" (or your chosen stricter level).

Additional Reading:

https://cloud.google.com/sql/docs/postgres/flags

More Info:​

Risk Level​

Address​

Compliance Standards​

Triage and Remediation​

Remediation​

Additional Reading:​