Profile Applicability:

  • Level 1

Description:

The log_disconnections setting in PostgreSQL enables logging at the end of each session, providing details such as the session's duration. This flag works alongside the log_connections flag to offer a complete picture of connection activity.

Rationale:

By default, PostgreSQL does not log details about when sessions end or their duration. Enabling the log_disconnections flag helps generate logs that can assist in troubleshooting, identifying anomalies, and detecting unusual activity over time. This flag is typically used in conjunction with the log_connections flag for comprehensive connection logging.

Impact:

  • Enabling session logging increases storage requirements over time.

  • Improper log management can lead to unnecessary storage costs.

  • Using the command line to set database flags may reset all omitted flags to their default values, potentially causing configuration issues or triggering instance restarts. It is recommended to apply these changes during off-peak hours.

Default Value:

By default, the log_disconnections flag is set to off.

Audit Steps:

Using Google Cloud Console:

  1. Navigate to the Cloud SQL Instances page.

               

  1. Select the PostgreSQL instance to open its Instance Overview page.

     

  1. Locate the Configuration card.

                 

  1. Verify that the log_disconnections flag is set to on under the Database Flags section.

               

Using Google Cloud CLI:

Use the following command to verify the log_disconnections flag for all PostgreSQL instances:

gcloud sql instances list --format=json | jq '.[].settings.databaseFlags[] | select(.name=="log_disconnections")|.value'

  1. The output should display on for the flag.

Remediation Steps:

Using Google Cloud Console:

  1. Go to the Cloud SQL Instances page.

                   

  1. Select the PostgreSQL instance where the flag needs to be updated.

       

  1. Click Edit.

       

  1. Scroll down to the Flags section and click Add a Database Flag.

                       

  1. Choose log_disconnections from the dropdown menu and set its value to on.

               

  1. Click Save.

                       

  1. Verify the changes under the Database Flags section on the Overview page.

                     

Using Google Cloud CLI:

Enable the log_disconnections flag for the PostgreSQL instance using the following command:

gcloud sql instances patch [INSTANCE_NAME] --database-flags log_disconnections=on

  1. Note: This command will overwrite all previously configured flags. To retain existing flags, explicitly include them in the command.

Backout Plan

Step 1: Revert log_disconnections Flag to Off

If you need to revert the log_disconnections flag to its default setting, set it to off:

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

Step 2: Verify the Reverted Configuration

Ensure that the configuration change has been successfully reverted

gcloud sql instances describe [INSTANCE_NAME] --format=json | jq '.settings.databaseFlags[] | select(.name=="log_disconnections")|.value'

Step 3: Notify Stakeholders

  • Inform the relevant teams (e.g., security and operations) that the logging of disconnections has been disabled.

  • Discuss any adjustments that may be necessary to maintain proper audit logging for future monitoring.

References:

  1. Google Cloud SQL Database Flags Documentation

  2. PostgreSQL Documentation on Runtime Configuration Logging

Additional Information:

  • Configuring the log_disconnections flag does not require restarting the Cloud SQL instance.

  • Some database flag settings may affect the stability or availability of the instance and could remove it from the Cloud SQL SLA. Refer to the Operational Guidelines for more details.

  • Ensure proper log rotation and retention policies to avoid excessive storage usage.

CIS Controls:

Control

Description

IG 1

IG 2

IG 3

8.5 Collect Detailed Audit Logs

Enable audit logging for enterprise assets to capture details such as event sources, timestamps, and user data for forensic investigations.

✅

✅


6.3 Enable Detailed Logging

Configure detailed logging to record essential information, including user actions and system events, for better monitoring and troubleshooting.

✅

✅