Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Configure "log_statement" Flag for PostgreSQL Database Instances

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: Medium (should be achieved)
Rule ID: CloudSQL-032

Ensure that the "log_statement" database flag configured for your Google Cloud PostgreSQL database instances has the appropriate value (logging level) in accordance with your organization's logging policy. The "log_statement" configuration flag controls which SQL statements are logged. The valid "log_statement" values are: none (disabled), ddl, mod, and all (all statements). ddl captures CREATE, ALTER, and DROP statements. mod captures ddl statements and data-modifying operations like INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. Additionally, PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are logged if their underlying commands are appropriate. For clients employing the extended query protocol, logging is triggered upon receipt of an Execute message, with Bind parameter values included, and any single-quote marks within them are doubled. Before running this conformity rule, select the value (level) used by the "log_statement" flag within your organization, in the Trend Cloud One™ – Conformity account rule settings.

This rule resolution is part of the Conformity Security & Compliance tool for GCP.

Security

PostgreSQL database auditing can help in troubleshooting operational issues and permit administrators to perform forensic analysis. An incorrect value for the "log_statement" flag can lead to excessive logging, making it difficult to find relevant information, or insufficient logging, missing crucial details. Therefore, the database flag value should be set in accordance with your organization's logging protocols.

Note: Some database flag settings can affect instance availability and/or stability, and eventually remove the PostgreSQL instance from the Google Cloud SQL Service Level Agreement (SLA).

Audit

To determine if the "log_statement" flag set for your Cloud PostgreSQL database instances has the appropriate configuration, perform the following operations:

Using GCP Console

01 Sign in to your Trend Cloud One™ – Conformity account, access the Configure "log_statement" Flag for PostgreSQL Database Instances rule, and identity the value configured for the "log_statement" database flag.

02 Sign in to Google Cloud Management Console.

03 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

04 Navigate to Cloud SQL Instances console available at https://console.cloud.google.com/sql/instances>.

05 Click inside the Filter box, select Type and PostgreSQL \ to list only the PostgreSQL database instances provisioned for the selected GCP project.

06 Click on the name (ID) of the PostgreSQL database instance that you want to examine.

07 Select Overview from the navigation panel to access the configuration information available for the selected PostgreSQL instance.

08 In the Configuration section, under Database flags, check the value set for the log_statement flag. If log_statement is not available in the Database flags list or the flag value is different than the one identified at step no. 1, the "log_statement" configuration for the selected Google Cloud PostgreSQL database instance is not compliant. If Database flags is not available in the Configuration section, instead the following status message is displayed: No database flags set, there are no database flags configured for the selected PostgreSQL instance.

09 Repeat step no. 6 – 8 to check the database flag configuration for other PostgreSQL database instances available within the selected project.

10 Repeat steps no. 3 – 9 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Sign in to your Trend Cloud One™ – Conformity account, access the Configure "log_statement" Flag for PostgreSQL Database Instances rule, and identity the value configured for the "log_statement" database flag.

02 Run projects list command (Windows/macOS/Linux) with custom query filters to list the IDs of all the Google Cloud Platform (GCP) projects available in your Google Cloud account: 

gcloud projects list
  --format="table(projectId)"

03 The command output should return the requested GCP project identifiers: 

PROJECT_ID
cc-web-project-112233
cc-gov-project-123123

04 Run sql instances list command (Windows/macOS/Linux) with custom filtering to describe the name of each PostgreSQL database instance provisioned for the selected Google Cloud project: 

gcloud sql instances list
  --project cc-web-project-112233
  --filter='DATABASE_VERSION:POSTGRES*'
  --format="(NAME)"

05 The command output should return the requested database instance name(s): 

NAME
cc-app-postgres-instance
cc-web-postgres-instance

06 Run sql instances describe command (Windows/macOS/Linux) using the name of the PostgreSQL database instance that you want to examine as identifier parameter and custom query filters to describe the "log_statement" flag configuration value set for the selected database instance: 

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

07 The command output should return the requested flag configuration value: 

"mod"

If the sql instances describe command output returns null or the flag value is different than the one approved by your organization and identified at step no. 1, the "log_statement" configuration for the selected Google Cloud PostgreSQL database instance is not compliant.

08 Repeat steps no. 6 and 7 to verify the database flag configuration value for other PostgreSQL database instances created for the selected project.

09 Repeat steps no. 4 – 8 for each project available within your Google Cloud account.

Remediation / Resolution

To configure the "log_statement" flag value (logging level) in accordance with your organization's logging policy, perform the following operations:

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL Instances console available at https://console.cloud.google.com/sql/instances>.

04 Click inside the Filter box, select Type and PostgreSQL \ to list only the PostgreSQL database instances provisioned for the selected GCP project.

05 Click on the name (ID) of the PostgreSQL database instance that you want to configure.

06 Choose EDIT from the console top menu to modify the instance configuration settings.

07 In the Customize your instance section, choose Flags to expand the panel with the database flags configured for the selected PostgreSQL instance.

08 Find the log_statement flag and select the appropriate value, in accordance with your organization's logging policy, from the flag configuration dropdown list. If the flag has not been set on the selected instance before, choose ADD A DATABASE FLAG, select log_statement from the Choose a flag dropdown list, and select the correct value from the Value list. Choose DONE to close the configuration panel.

09 Choose SAVE to apply the configuration changes.

10 Repeat step no. 5 – 9 to configure the required flag for other PostgreSQL database instances available within the selected project.

11 Repeat steps no. 2 – 10 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run sql instances patch command (Windows/macOS/Linux) with the name of the PostgreSQL database instance that you want to configure as the identifier parameter, to set the correct value (i.e. logging level) for the "log_statement" database flag, in accordance with your organization's logging policy. The supported values are none (disabled), ddl, mod, and all (all statements). The following command example, sets the "log_statement" value to all: 

gcloud sql instances patch cc-app-postgres-instance 
  --database-flags log_statement=all

02 Type Y and press Enter to confirm the database configuration change: 

The following message will be used for the patch API method.
{"name": "cc-app-postgres-instance", "project": "cc-web-project-112233", "settings": {"databaseFlags": [{"name": "log_statement", "value": "all"}]}}
WARNING: This patch modifies database flag values, which may require your instance to be restarted. Check the list of supported flags - https://cloud.google.com/sql/docs/postgres/flags - to see if your instance will be restarted when this patch is submitted.

Do you want to continue (Y/n)? Y

03 The output should return the sql instances patch command request status: 

Patching Cloud SQL instance...done.
Updated [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-web-project-112233/instances/cc-app-postgres-instance].

04 Repeat steps no. 1 – 3 to configure the required flag for other PostgreSQL database instances provisioned for the selected project.

05 Repeat steps no. 1 – 4 for each project created within your Google Cloud account.

References

Publication date May 2, 2024

Related CloudSQL rules