iCompaas Support

Profile Applicability:

• Level 1

Description:

The log_connections database flag, when enabled, ensures that each attempted connection to the PostgreSQL server is logged, including successful client authentication. This setting is essential for monitoring and troubleshooting connection activity. Note that this parameter cannot be altered once a session starts.

Rationale:

By default, PostgreSQL does not log connection attempts. Enabling log_connections creates log entries for every attempted connection and successful authentication. This information is invaluable for identifying unusual connection attempts, diagnosing connectivity issues, and enhancing the overall security of the database instance.

Impact:

• Enabling logging will result in an increase in storage usage over time.

• Mismanaged logs can lead to increased storage costs.

• Applying changes to database flags via the command line may reset any omitted flags to their default values, which can cause unintended disruptions or instance restarts. Therefore, it is recommended to make these changes during periods of low usage.

Default Value:

By default, the log_connections flag is set to off.

Audit Steps:

Using Google Cloud Console:

1. Go to the Cloud SQL Instances page.

2. Select the desired PostgreSQL instance to open its Instance Overview page.

3. Locate the Configuration card.

4. Check the value of the log_connections flag under the Database Flags section. Ensure the value is set to on.

Using Google Cloud CLI:

Use the following command to check the value of the log_connections flag for all PostgreSQL instances:

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

1. The output should display on for the flag.

Remediation Steps:

Using Google Cloud Console:

1. Navigate to the Cloud SQL Instances page.

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

3. Click Edit.

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

5. Select the log_connections flag from the dropdown and set its value to on.

6. Click Save.

7. Confirm the changes under the Database Flags section on the Overview page.

Using Google Cloud CLI:

Set the log_connections flag for each PostgreSQL instance using the following command:

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

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

Backout Plan:

1. Revert the Flag to Off:

   • If needed, revert the log_connections flag to off to stop logging connections.

Using Google Cloud Console:

• Navigate to the Cloud SQL Instances page.

• Select the PostgreSQL instance to edit.

• Click Edit, scroll to the Flags section, and remove or set the log_connections flag to off.

• Save and restart the instance.

Using Google Cloud CLI

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

• Replace [INSTANCE_NAME] with the actual PostgreSQL instance name.

• Restart the instance for the changes to take effect

References:

1. Google Cloud SQL Database Flags Documentation

2. PostgreSQL Documentation on Runtime Configuration Logging

Additional Information:

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

• Certain flag settings may impact instance availability and stability. Refer to Operational Guidelines for more details.

• Mismanaged logs can lead to excessive storage usage; ensure proper log rotation and retention policies are in place.

CIS Controls: