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

# Sql log statement flag remediation

### Triage and Remediation

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

    <AccordionGroup>
      <Accordion title="Using Console" defaultOpen="true">
        To remediate the PostgreSQL Log Statement Flag misconfiguration in GCP using GCP console, please follow the steps below:

        1. Login to your GCP console and select the project where the PostgreSQL instance is located.
        2. In the navigation menu, select SQL under the Storage section.
        3. Select the PostgreSQL instance where the misconfiguration exists.
        4. Click on the Edit button at the top of the page.
        5. Scroll down to the Flags section and look for the log\_statement flag.
        6. Set the log\_statement flag to one of the following values based on your requirements:
           * none: Disables logging of all statements
           * ddl: Logs only data definition language (DDL) statements
           * mod: Logs only statements that modify data (INSERT, UPDATE, DELETE)
           * all: Logs all statements (default)
        7. Once you have set the log\_statement flag appropriately, click on the Save button at the bottom of the page.
        8. Wait for a few minutes for the changes to take effect.

        That's it! You have successfully remediated the PostgreSQL Log Statement Flag misconfiguration in GCP using GCP console.

        #
      </Accordion>

      <Accordion title="Using CLI">
        To remediate the PostgreSQL log statement flag misconfiguration in GCP using GCP CLI, follow these steps:

        1. Open the Cloud Shell in your GCP console.
        2. Run the following command to list all the instances in your project:
           ```
           gcloud sql instances list
           ```
        3. Identify the instance that has the PostgreSQL log statement flag misconfiguration.
        4. Run the following command to get the current flags of the instance:
           ```
           gcloud sql instances describe [INSTANCE_NAME]
           ```
           Replace \[INSTANCE\_NAME] with the name of the instance that you identified in step 3.
        5. Look for the `databaseFlags` field in the output of the previous command. This field contains a list of flags that are currently set for the instance.
        6. Check if the `log_statement` flag is set to `all`. If it is not set to `all`, then it is misconfigured.
        7. To set the `log_statement` flag to `all`, run the following command:
           ```
           gcloud sql instances patch [INSTANCE_NAME] --database-flags log_statement=all
           ```
           Replace \[INSTANCE\_NAME] with the name of the instance that you identified in step 3.
        8. Verify that the flag has been set correctly by running the `gcloud sql instances describe` command again and checking the `databaseFlags` field. The `log_statement` flag should now be set to `all`.

        By following these steps, you should be able to remediate the PostgreSQL log statement flag misconfiguration in GCP using GCP CLI.
      </Accordion>

      <Accordion title="Using Python">
        To remediate the PostgreSQL Log Statement Flag misconfiguration in GCP using Python, follow these steps:

        1. Install the `google-cloud-logging` library using pip:

        ```
        pip install google-cloud-logging
        ```

        2. Create a service account with the necessary permissions to access the GCP project where the PostgreSQL instance is located. Download the JSON key file for the service account.

        3. Set the `GOOGLE_APPLICATION_CREDENTIALS` environment variable to the path of the JSON key file:

        ```
        export GOOGLE_APPLICATION_CREDENTIALS=/path/to/keyfile.json
        ```

        4. Use the following Python code to set the PostgreSQL log\_statement flag to `all`:

        ```python theme={null}
        from google.cloud import logging_v2
        from google.oauth2 import service_account

        # Set the PostgreSQL instance ID
        instance_id = "my-postgres-instance"

        # Set the log_statement flag to "all"
        log_statement = "all"

        # Authenticate using the service account key file
        credentials = service_account.Credentials.from_service_account_file(
            "/path/to/keyfile.json"
        )

        # Create the Logging client
        client = logging_v2.LoggingServiceV2Client(credentials=credentials)

        # Create the log sink for the PostgreSQL instance
        sink_name = f"projects/{client.project}/sinks/postgresql-{instance_id}"
        sink = client.get_sink(sink_name)

        # Update the sink's filter to include the log_statement flag
        new_filter = f'resource.type="gce_instance" AND resource.labels.instance_id="{instance_id}" AND textPayload:"log_statement = {log_statement}"'
        sink.filter = new_filter

        # Update the sink in GCP
        client.update_sink(sink=sink, update_mask={"paths": ["filter"]})
        ```

        This code will update the log sink for the specified PostgreSQL instance to include the `log_statement = all` filter. This will ensure that all SQL statements executed on the instance are logged to the Cloud Logging service in GCP.
      </Accordion>

      <Accordion title="Using Terraform">
        ```hcl theme={null}
        resource "google_sql_database_instance" "POSTGRES_INSTANCE" {
          name             = "POSTGRES_INSTANCE_NAME"        # replace with your instance name
          database_version = "POSTGRES_13"                   # replace with your version as needed
          region           = "GCP_REGION"                    # e.g. "us-central1"

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

            # Other settings ...

            database_flags {
              name  = "log_statement"
              value = "ddl"                                  # set to "ddl" per policy
            }

            # If you already have other database_flags, keep them and add/modify only this one.
          }
        }
        ```

        Changing this flag will trigger an in-place update and a brief restart of the Cloud SQL instance, but it does not force resource replacement.

        For verification, `terraform plan` should show a change only to the `settings.0.database_flags` entry for `name = "log_statement"` with its `value` changing from the previous setting to `"ddl"`.
      </Accordion>
    </AccordionGroup>
  </Tab>
</Tabs>