iCompaas Support

Profile Applicability:

• Level 2

Description:

The log_statement database flag in PostgreSQL controls the logging of SQL statements based on their type. The valid values for this flag are:

• none: No statements are logged.

• ddl: Logs only data definition language (DDL) statements (e.g., CREATE, ALTER, DROP).

• mod: Logs all DDL statements plus data-modifying statements (e.g., INSERT, UPDATE, DELETE).

• all: Logs all types of SQL statements, including those with errors.

The recommended value is 'ddl', unless specified otherwise by your organization's logging policy.

Rationale:

Auditing and logging SQL statements are crucial for forensic analysis and troubleshooting. Setting the log_statement flag to an appropriate level ensures that relevant activity is logged without overwhelming the logs with unnecessary data. Too many logged statements can make it difficult to find useful information, while too few can result in missing critical data. Aligning this setting with your security and logging policies will help streamline audit processes and enhance visibility.

Impact:

• Enabling logging increases storage requirements over time.

• Improper log management may cause storage costs to rise.

• Setting custom flags via command line might overwrite existing flags, potentially leading to loss of custom settings and requiring instance restarts. Changes should be made during off-peak hours to mitigate these issues.

Default Value:

By default, the log_statement flag is set to 'none' (no statements are logged).

Audit Steps:

Using Google Cloud Console:

1. Navigate to the Cloud SQL Instances page.

2. Select the PostgreSQL instance to view its Instance Overview page.

      .

3. Go to the Configuration card.

4. Ensure the value of the log_statement flag is set as expected (typically to ddl or stricter).

Using Google Cloud CLI:

List all Cloud SQL PostgreSQL instances:

gcloud sql instances list --format=json

Verify the log_statement flag value for each instance:

gcloud sql instances describe [INSTANCE_NAME] --format=json | jq '.settings.databaseFlags[] | select(.name=="log_statement")|.value'

Remediation Steps:

Using Google Cloud Console:

1. Go to the Cloud SQL Instances page.

2. Select the PostgreSQL instance that needs to be updated.

3. Click Edit.

4. Scroll to the Flags section.

5. If the log_statement flag is not set, click Add a Database Flag, select log_statement from the dropdown menu, and set its value to the desired level (ddl, mod, or all).

6. Click Save to apply the changes.

7. Confirm the changes under Flags on the Overview page.

Using Google Cloud CLI:

Use the following command to configure the log_statement flag for a PostgreSQL instance:

gcloud sql instances patch [INSTANCE_NAME] --database-flags log_statement=[ddl|mod|all]

1. Note: This command will overwrite any previously set flags. Ensure to include all the desired flags in the command to avoid resetting any other custom configurations.

Backout Plan:

Step 1: Revert log_statement Configuration

If you need to revert the log_statement flag to its default value, set it to none:

gcloud sql instances patch [INSTANCE_NAME] --database-flags log_statement=non

Step 2: Verify Changes

Ensure the configuration change has been applied successfully:

gcloud sql instances describe [INSTANCE_NAME] --format=json | jq '.settings.databaseFlags[] | select(.name=="log_statement")|.value'

Step 3: Notify Stakeholders

• Inform the relevant stakeholders (e.g., security and operations teams) that the logging settings have been reverted.

• Discuss any potential adjustments needed to align the logging configuration with organizational policies.

References:

1. Google Cloud SQL Database Flags Documentation

2. PostgreSQL Runtime Configuration Logging

Additional Information:

• Configuring the log_statement flag will not require a restart for the Cloud SQL instance.

• Some database flags may affect instance availability or stability, and may impact the Cloud SQL SLA. For more details, check the Operational Guidelines.

CIS Controls: