> ## Documentation Index
> Fetch the complete documentation index at: https://cloudanix.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL Log Lock Waits Flag Should Be Disabled

### 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

<Tabs>
  <Tab title="Remediation">
    ### Remediation

    <AccordionGroup>
      <Accordion title="Using Console" defaultOpen="true">
        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.

        #
      </Accordion>

      <Accordion title="Using CLI">
        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.
      </Accordion>

      <Accordion title="Using Python">
        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](https://cloud.google.com/docs/authentication/getting-started) 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:

        ```python theme={null}
        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.

        3. 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:

        ```python theme={null}
        # 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.

        4. Finally, you can check that the flag has been disabled by querying the instance settings again:

        ```python theme={null}
        # 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.
      </Accordion>

      <Accordion title="Using Terraform">
        ```hcl theme={null}
        resource "google_sql_database_instance" "POSTGRES_INSTANCE" {
          name             = "POSTGRES_INSTANCE_NAME"      # substitute: your instance name
          database_version = "POSTGRES_14"                # substitute: your PG version
          region           = "GCP_REGION"                 # substitute: e.g. us-central1

          settings {
            tier = "DB_TIER"                              # substitute: e.g. db-custom-2-7680

            # Other existing settings go here...

            database_flags {
              name  = "log_lock_waits"
              value = "on"
            }
          }
        }
        ```

        Changing this flag will trigger a restart of the Cloud SQL instance but does not force Terraform to replace it.

        Verification: `terraform plan` should show an in-place update to `settings.0.database_flags` on the `google_sql_database_instance` with `log_lock_waits` changing from its previous value to `"on"`.
      </Accordion>
    </AccordionGroup>
  </Tab>
</Tabs>