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:
Navigate to the Cloud SQL Instances page.
Select the PostgreSQL instance to open its Instance Overview page.
Locate the Configuration card.
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'
The output should display on for the flag.
Remediation Steps:
Using Google Cloud Console:
Go to the Cloud SQL Instances page.
Select the PostgreSQL instance where the flag needs to be updated.
Click Edit.
Scroll down to the Flags section and click Add a Database Flag.
Choose log_disconnections from the dropdown menu and set its value to on.
Click Save.
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
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:
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: