PostgreSQL Log Min Messages Flag Should Be Disabled

More Info:

Ensure that the log_min_messages database flag for Cloud SQL PostgreSQL instance is set appropriately.

Risk Level

Low

Address

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_messages flag misconfiguration in GCP, you can follow the below steps using the GCP console:

1. Open the Cloud SQL instances page in the GCP console.
2. Select the instance that you want to remediate.
3. Click on the "Edit" button at the top of the page.
4. Scroll down to the "Flags" section and click on "Add item".
5. In the "Flag name" field, enter "log_min_messages".
6. In the "Flag value" field, enter "WARNING".
7. Click on the "Save" button at the bottom of the page to save the changes.

This will set the log_min_messages flag to "WARNING" which will ensure that only warning messages and above are logged in the PostgreSQL logs.

Using CLI

To remediate the PostgreSQL Log Min Messages Flag Should Be Disabled misconfiguration in GCP using GCP CLI, follow these steps:

1. Open the Cloud Shell in your GCP Console.

2. Connect to your GCP instance that has PostgreSQL installed using the following command:

gcloud compute ssh [INSTANCE_NAME]

Replace [INSTANCE_NAME] with the name of your instance.

3. Run the following command to open the PostgreSQL configuration file:

sudo nano /etc/postgresql/[POSTGRESQL_VERSION]/main/postgresql.conf

Replace [POSTGRESQL_VERSION] with the version of PostgreSQL installed on your instance.

4. Locate the line that starts with log_min_messages in the configuration file.

5. Change the value of log_min_messages to WARNING or higher. This will disable the flag and ensure that only warning messages or higher are logged.

6. Save the changes to the configuration file by pressing CTRL + X, then Y, then ENTER.

7. Restart the PostgreSQL service using the following command:

sudo service postgresql restart

8. Verify that the PostgreSQL Log Min Messages flag has been disabled by running the following command:

sudo su - postgres -c "psql -c 'SHOW log_min_messages;'"

This should return the new value of log_min_messages.

9. Exit the SSH session by running the following command:

exit

Your PostgreSQL Log Min Messages flag is now disabled and your instance is secured.

Using Python

To remediate the PostgreSQL Log Min Messages Flag misconfiguration in GCP using Python, you can follow the below steps:

1. First, you need to connect to the GCP project where the PostgreSQL instance is running. You can use the google-cloud-sdk and google-auth Python packages to authenticate and connect to the GCP project.

from google.oauth2 import service_account
from google.cloud import sql_v1beta4

# Authenticate using a service account key file
credentials = service_account.Credentials.from_service_account_file(
    '/path/to/service_account_key.json')

# Connect to the GCP project and get the SQL admin client
project_id = 'your-project-id'
location = 'us-central1'
client = sql_v1beta4.SqlAdminClient(credentials=credentials)
instance_name = 'your-instance-name'
instance_path = f"projects/{project_id}/locations/{location}/instances/{instance_name}"

2. Once you have connected to the GCP project and fetched the SQL admin client, you can get the current PostgreSQL instance configuration using the get method of the instances resource.

# Get the current instance configuration
instance = client.instances().get(instance=instance_path).execute()

3. Check if the log_min_messages flag is enabled or not. If it is enabled, you need to update the instance configuration to disable it.

# Check if the log_min_messages flag is enabled
if 'postgresqlConf' in instance and 'log_min_messages' in instance['postgresqlConf']:
    if instance['postgresqlConf']['log_min_messages'] != 'ERROR':
        # Disable the log_min_messages flag
        instance['postgresqlConf']['log_min_messages'] = 'ERROR'
        # Update the instance configuration
        operation = client.instances().patch(
            instance=instance_path,
            body={'settings': instance['settings']}
        ).execute()
        # Wait for the operation to complete
        client.operations().wait(operation=operation['name']).execute()
        print(f"Successfully disabled log_min_messages flag for instance {instance_name}")
    else:
        print(f"log_min_messages flag is already disabled for instance {instance_name}")
else:
    print(f"log_min_messages flag is not set for instance {instance_name}")

4. After updating the instance configuration, you can verify if the log_min_messages flag is disabled or not by fetching the instance configuration again and checking the value of the flag.

# Fetch the instance configuration again
instance = client.instances().get(instance=instance_path).execute()
# Check if the log_min_messages flag is disabled
if 'postgresqlConf' in instance and 'log_min_messages' in instance['postgresqlConf']:
    if instance['postgresqlConf']['log_min_messages'] == 'ERROR':
        print(f"log_min_messages flag is now disabled for instance {instance_name}")
    else:
        print(f"Failed to disable log_min_messages flag for instance {instance_name}")
else:
    print(f"log_min_messages flag is not set for instance {instance_name}")

By following the above steps, you can remediate the PostgreSQL Log Min Messages Flag misconfiguration in GCP using Python.

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"

    # Ensure log_min_messages is set appropriately (ERROR or stricter)
    database_flags {
      name  = "log_min_messages"
      value = "ERROR"                             # or "FATAL"/"PANIC" if your policy requires stricter
    }

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

Changing log_min_messages updates the instance in place and does not force replacement, though it may briefly restart the PostgreSQL engine.

To verify, terraform plan should show an in-place update of google_sql_database_instance.POSTGRES_INSTANCE with settings.0.database_flags either being added or changing the log_min_messages value to "ERROR".