​
More Info:

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

​
Risk Level

Medium

​
Address

Security

​
Compliance Standards

SOC2

​
Triage and Remediation

​
Remediation

Using Console

To remediate the PostgreSQL Log Lock Waits Flag Should Be Disabled misconfiguration in GCP using GCP console, please follow these steps:
  1. Open the GCP Console and navigate to the Cloud SQL instances page.
  2. Select the instance that you want to remediate.
  3. Click on the Edit button at the top of the page.
  4. Scroll down to the Flags section and click on the Add database flag button.
  5. In the Name field, enter log_lock_waits and in the Value field, enter off.
  6. Click on the Save button at the bottom of the page to save the changes.
  7. Wait for a few minutes for the changes to take effect.
Once the changes have been applied, the PostgreSQL Log Lock Waits Flag will be disabled and the misconfiguration will be remediated.

​

To remediate the PostgreSQL Log Lock Waits Flag misconfiguration in GCP using GCP CLI, follow these steps:
  1. Open the Cloud Shell in your GCP console.
  2. Connect to your instance using the following command: 
    gcloud sql connect [INSTANCE_NAME] --user=[USER_NAME]
    Replace [INSTANCE_NAME] with the name of your Cloud SQL instance and [USER_NAME] with the name of the user you want to connect as.
  3. Enter the user’s password when prompted.
  4. Run the following command to disable the log_lock_waits flag: 
    ALTER SYSTEM SET log_lock_waits = off;
  5. Restart your instance to apply the changes: 
    gcloud sql instances restart [INSTANCE_NAME]
    Replace [INSTANCE_NAME] with the name of your Cloud SQL instance.
  6. Verify that the flag has been disabled by running the following command: 
    SHOW log_lock_waits;
    If the output is off, then the flag has been successfully disabled.
Congratulations! You have successfully remediated the PostgreSQL Log Lock Waits Flag misconfiguration in GCP using GCP CLI.
To remediate the “PostgreSQL Log Lock Waits Flag Should Be Disabled” misconfiguration in GCP using Python, follow these steps:
  1. First, you need to authenticate and set up the GCP client library for Python. You can follow the instructions provided in the official documentation to do this.
  2. Next, you need to create a connection to your PostgreSQL instance using the google-cloud-sql library. You can use the following code to do this: 
from google.cloud import sql
from google.oauth2 import service_account

# Set up the credentials
credentials = service_account.Credentials.from_service_account_file(
    'path/to/credentials.json')

# Create a client object
client = sql.Client(project='project-id', credentials=credentials)

# Get a reference to your instance
instance = client.instance('instance-id')
Make sure to replace path/to/credentials.json, project-id, and instance-id with the appropriate values for your setup.
  1. Once you have a reference to your instance, you can disable the log_lock_waits flag by updating the instance settings. You can use the following code to do this:
# Get the current settings
settings = instance.database_flags().get()

# Disable the log_lock_waits flag
settings['log_lock_waits'] = False

# Update the settings
operation = instance.update_database_flags(settings)
result = operation.result()
This code gets the current database settings, disables the log_lock_waits flag, and updates the settings. The update_database_flags method returns an operation object, which you can use to check the status of the update.
  1. Finally, you can check that the flag has been disabled by querying the instance settings again:
# Get the updated settings
settings = instance.database_flags().get()

# Print the log_lock_waits flag value
print(settings['log_lock_waits'])
This code gets the updated database settings and prints the value of the log_lock_waits flag. It should be False if the remediation was successful.That’s it! You have successfully remediated the “PostgreSQL Log Lock Waits Flag Should Be Disabled” misconfiguration in GCP using Python.