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
LowAddress
Reliability, SecurityCompliance Standards
CISGCP, CBPTriage and Remediation
Remediation
Using Console
Using Console
To remediate the PostgreSQL Log Statement Flag misconfiguration in GCP using GCP console, please follow the steps below:
- Login to your GCP console and select the project where the PostgreSQL instance is located.
- In the navigation menu, select SQL under the Storage section.
- Select the PostgreSQL instance where the misconfiguration exists.
- Click on the Edit button at the top of the page.
- Scroll down to the Flags section and look for the log_statement flag.
- 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)
- Once you have set the log_statement flag appropriately, click on the Save button at the bottom of the page.
- Wait for a few minutes for the changes to take effect.
Using CLI
Using CLI
To remediate the PostgreSQL log statement flag misconfiguration in GCP using GCP CLI, follow these steps:
- Open the Cloud Shell in your GCP console.
- Run the following command to list all the instances in your project:
- Identify the instance that has the PostgreSQL log statement flag misconfiguration.
- Run the following command to get the current flags of the instance:
Replace [INSTANCE_NAME] with the name of the instance that you identified in step 3.
- Look for the
databaseFlagsfield in the output of the previous command. This field contains a list of flags that are currently set for the instance. - Check if the
log_statementflag is set toall. If it is not set toall, then it is misconfigured. - To set the
log_statementflag toall, run the following command:Replace [INSTANCE_NAME] with the name of the instance that you identified in step 3. - Verify that the flag has been set correctly by running the
gcloud sql instances describecommand again and checking thedatabaseFlagsfield. Thelog_statementflag should now be set toall.
Using Python
Using Python
To remediate the PostgreSQL Log Statement Flag misconfiguration in GCP using Python, follow these steps:This code will update the log sink for the specified PostgreSQL instance to include the
- Install the
google-cloud-logginglibrary using pip:
- 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.
-
Set the
GOOGLE_APPLICATION_CREDENTIALSenvironment variable to the path of the JSON key file:
- Use the following Python code to set the PostgreSQL log_statement flag to
all:
log_statement = all filter. This will ensure that all SQL statements executed on the instance are logged to the Cloud Logging service in GCP.