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

CISGCP, CBP

Triage and 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:
  1. Login to GCP console.
  2. Navigate to the Cloud SQL instances page.
  3. Select the instance for which you want to remediate the misconfiguration.
  4. Click on the “Edit” button at the top of the page.
  5. Scroll down to the “Flags” section.
  6. Click on the “Add item” button.
  7. In the “Name” field, enter “log_min_error_statement”.
  8. In the “Value” field, enter “error”.
  9. 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.

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:
  1. Open the Cloud Shell in your GCP console.
  2. Run the following command to list all the Cloud SQL instances in your project:
    gcloud sql instances list
    
  3. Identify the instance for which you want to remediate the misconfiguration and note down its name.
  4. 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.
  5. 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.
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:
  1. First, you need to authenticate to your GCP project using the Google Cloud SDK by running the command gcloud auth login in your terminal.
  2. Next, you need to install the google-cloud-logging library by running the command pip install google-cloud-logging.
  3. 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.

Additional Reading: