> ## 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 Statement Flag Should Be Set Appropriately

### More Info:

The value of log\_statement flag determined the SQL statements that are logged. Valid values are: • none • ddl • mod • all The value ddl logs all data definition statements. The value mod logs all ddl statements, plus data-modifying statements. The statements are logged after a basic parsing is done and statement type is determined, thus this does not logs statements with errors. When using extended query protocol, logging occurs after an Execute message is received and values of the Bind parameters are included. A value of 'ddl' is recommended unless otherwise directed by your organization's logging policy.

### Risk Level

Low

### Address

Reliability, Security

### Compliance Standards

CISGCP, CBP

### 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>
    </AccordionGroup>
  </Tab>
</Tabs>