Detections
Analytics

6.2.6 Ensure that the 'log_lock_waits' database flag for Cloud SQL PostgreSQL instance is set to 'on'

Warning! Audit Deprecated

This audit has been deprecated and will be removed in a future update.

View Next Audit Version

Information

Enabling the log_lock_waits flag for a PostgreSQL instance creates a log for any session waits that take longer than the alloted deadlock_timeout time to acquire a lock.

Rationale:

The deadlock timeout defines the time to wait on a lock before checking for any conditions. Frequent run overs on deadlock timeout can be an indication of an underlying issue. Logging such waits on locks by enabling the log_lock_waits flag can be used to identify poor performance due to locking delays or if a specially-crafted SQL is attempting to starve resources through holding locks for excessive amounts of time. This recommendation is applicable to PostgreSQL database instances.

Solution

From Console:

Go to the Cloud SQL Instances page in the Google Cloud Console by visiting https://console.cloud.google.com/sql/instances.

Select the PostgreSQL instance where the database flag needs to be enabled.

Click Edit.

Scroll down to the Flags section.

To set a flag that has not been set on the instance before, click Add item, choose the flag log_lock_waits from the drop-down menu and set the value as on.

Click Save.

Confirm the changes under Flags on the Overview page.

From Command Line:

List all Cloud SQL database instances using the following command:

gcloud sql instances list

Configure the log_lock_waits database flag for every Cloud SQL PosgreSQL database instance using the below command:

gcloud sql instances patch INSTANCE_NAME --database-flags log_lock_waits=on

Note: This command will overwrite all database flags previously set. To keep those and add new ones, include the values for all flags to be set on the instance; any flag not specifically included is set to its default value. For flags that do not take a value, specify the flag name followed by an equals sign ('=').

Default Value:

By default log_lock_waits is off.

See Also

https://workbench.cisecurity.org/files/3316

Item Details

References: CSCv7|6.3

Plugin: GCP

Control ID: 842252c04c56efc07c354f536c5734071108a4bc6f0b86e82a07234688f39ead