​
More Info:

As numerous other recommendations in this section consist of turning on flags for logging purposes, your organization will need a way to manage these logs. You may have a solution already in place. If you do not, consider installing and enabling the open source pgaudit extension within PostgreSQL and enabling its corresponding flag of cloudsql.enable_pgaudit. This flag and installing the extension enables database auditing in PostgreSQL through the open-source pgAudit extension. This extension provides detailed session and object logging to comply with government, financial, & ISO standards and provides auditing capabilities to mitigate threats by monitoring security events on the instance. Enabling the flag and settings later in this recommendation will send these logs to Google Logs Explorer so that you can access them in a central location. to This recommendation is applicable only to PostgreSQL database instances.

​
Risk Level

Low

​
Address

Reliability, Security

​
Compliance Standards

CISGCP, CBP

​
Triage and Remediation

​
Remediation

Using Console

To remediate the PostgreSQL Enable PGAudit Database Flag Should Be On misconfiguration for GCP using GCP console, follow the below steps:
  1. Open the Google Cloud Console and select the project in which your PostgreSQL instance is running.
  2. Navigate to the Cloud SQL Instances page and click on the name of the PostgreSQL instance you want to remediate.
  3. In the instance details page, select the Configuration tab.
  4. In the Configuration tab, click on Edit Configuration.
  5. In the Edit Configuration page, scroll down to the Flags section.
  6. In the Flags section, click on Add item.
  7. In the Add item dialog box, enter the flag name as pgaudit.enabled and set the value to on.
  8. Click on Save to save the configuration changes.
  9. Once the configuration changes are saved, restart the PostgreSQL instance for the changes to take effect.
After following the above steps, the PostgreSQL Enable PGAudit Database Flag Should Be On misconfiguration will be remediated for your GCP PostgreSQL instance.

​

To remediate the PostgreSQL Enable PGAudit Database Flag Should Be On misconfiguration in GCP using GCP CLI, follow these steps:
  1. Open the Cloud Shell in the GCP console.
  2. Connect to your PostgreSQL instance using the following command: 
gcloud sql connect INSTANCE_NAME --user=USER_NAME
Replace INSTANCE_NAME with the name of your PostgreSQL instance and USER_NAME with the username for the database.
  1. Once connected to the PostgreSQL instance, run the following command to enable the PGAudit flag:
ALTER DATABASE DATABASE_NAME SET pgaudit.log='all';
Replace DATABASE_NAME with the name of the database for which you want to enable the PGAudit flag.
  1. Verify that the PGAudit flag is enabled by running the following command:
SHOW pgaudit.log;
This should return “all” indicating that all audit events are being logged.
  1. Exit the PostgreSQL instance by running the following command:
\q
By following these steps, you can remediate the PostgreSQL Enable PGAudit Database Flag Should Be On misconfiguration in GCP using GCP CLI.
To remediate the PostgreSQL Enable PGAudit Database Flag Should Be On misconfiguration on GCP using Python, you can follow these steps:
  1. First, ensure that you have the necessary permissions to make changes to the PostgreSQL instance.
  2. Next, use the google-cloud-sql library to connect to the PostgreSQL instance. You can install this library using pip: 
pip install google-cloud-sql
  1. Once you have connected to the instance, you can use the patch method of the DatabaseInstance object to update the settings.settingsVersion field with the latest version. This will ensure that you are making changes to the latest version of the settings.
from google.cloud import sql

client = sql.Client()

instance_name = 'your-instance-name'
instance = client.instance(instance_name)

settings_version = instance.settings.settings_version

# Update the settings version to the latest version
instance.patch({'settings': {'settingsVersion': settings_version}})
  1. Next, you can use the patch method again to update the settings.databaseFlags field with the pgaudit.log flag set to on. This will enable PGAudit database flag.
# Update the database flags to enable PGAudit
instance.patch({'settings': {'databaseFlags': {'pgaudit.log': 'on'}}})
  1. Finally, you can verify that the changes have been made by querying the settings field of the DatabaseInstance object:
# Get the updated settings
updated_settings = instance.settings

# Print the database flags to verify that PGAudit is enabled
print(updated_settings.database_flags)
By following these steps, you should be able to remediate the PostgreSQL Enable PGAudit Database Flag Should Be On misconfiguration on GCP using Python.

​
Additional Reading: