Profile Applicability:

  • Level 1

Description:

The log_min_duration_statement flag specifies the minimum execution time, in milliseconds, for SQL statements to be logged. A value of -1 disables the logging of SQL statements. It is recommended to set this flag to -1 to prevent SQL statements from being logged.

Rationale:

Logging SQL statements can expose sensitive information, such as user data or confidential queries, in the logs. To minimize the risk of sensitive information being inadvertently logged, it is advised to disable this flag by setting it to -1. This ensures no SQL statement durations are logged.

Impact:

  • Disabling log_min_duration_statement prevents the logging of SQL statement execution times.

  • Enabling this flag may result in sensitive data being logged, posing a security risk.

  • Modifying this flag via the command line will reset any unspecified flags to their default values, potentially causing configuration issues or requiring instance restarts. Changes should be applied during low-usage periods to avoid disruption.

Default Value:

The default value for log_min_duration_statement is -1, which disables SQL statement logging.

Audit Steps:

Using Google Cloud Console:

  1. Visit the Cloud SQL Instances page.

                         

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

           

  1. Navigate to the Configuration card.

                 

  1. Under Database flags, verify that the log_min_duration_statement flag is set to -1.

                 

Using Google Cloud CLI:

Execute the following command for each PostgreSQL instance to verify the flag's value:

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

Remediation Steps:

Using Google Cloud Console:

  1. Navigate to the Cloud SQL Instances page.

                   

  1. Select the PostgreSQL instance requiring configuration.

                 

  1. Click Edit

       

  1. Scroll to the Flags section.

                 

  1. If the log_min_duration_statement flag is not already set, click Add item, select the flag from the dropdown menu, and set its value to -1.

         

  1. Click Save.

                     

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

         

Using Google Cloud CLI:

List all Cloud SQL database instances:

gcloud sql instances list

Configure the log_min_duration_statement flag for the required PostgreSQL instances:

gcloud sql instances patch [INSTANCE_NAME] --database-flags log_min_duration_statement=-1
  1. Note: This command overwrites all previously set flags. Include all required flags in the command to preserve them.

Backout Plan:

Step 1: Re-enable Query Logging If Needed

gcloud sql instances patch <INSTANCE_NAME> --database-flags "log_min_duration_statement=1000"

Step 2: Notify Security Teams

  • Inform security and database teams before re-enabling SQL logging.

Step 3: Restore from Backup (If Necessary)

  • If disabling SQL statement logging causes application failures, restore the PostgreSQL instance from a backup.

Step 4: Validate Rollback Success

  • Run the audit steps again to confirm that the intended log_min_duration_statement setting is applied.

References:

  1. Google Cloud SQL PostgreSQL Flags Documentation

  2. PostgreSQL Runtime Configuration Logging Documentation

Additional Information:

  • Modifying the log_min_duration_statement flag does not require restarting the Cloud SQL instance.

  • Certain database flag settings may impact instance stability and availability, potentially affecting the Cloud SQL SLA. Review Operational Guidelines before making changes.

CIS Controls:

Control

Description

IG 1

IG 2

IG 3

8.5 Collect Detailed Audit Logs

Configure detailed audit logging for enterprise assets containing sensitive data. Include event source, timestamp, and user information.


6.3 Enable Detailed Logging

Enable system logging to capture detailed event information, including user, timestamp, and source/destination addresses.