Profile Applicability:
- Level 1
Description:
The cloudsql.enable_pgaudit database flag enables database auditing in PostgreSQL using the open-source pgAudit extension. This flag facilitates centralized logging, allowing detailed session and object logging for compliance with regulatory and organizational standards. Enabling this flag ensures the audit logs are sent to Google Logs Explorer for centralized access.
Rationale:
By enabling the cloudsql.enable_pgaudit flag, PostgreSQL audit logs are collected in a centralized location, simplifying compliance with government, financial, and ISO standards. It provides robust monitoring of security events, allowing organizations to mitigate risks and ensure visibility into database activity. This is particularly useful for tracking security events and managing data access logs.
Impact:
Storage Overhead: Enabling pg Audit may lead to increased data storage requirements.
Instance Restart: Applying the flag requires a restart of the PostgreSQL instance.
Flag Overwrites: Enabling this flag via CLI overwrites any previously configured flags unless explicitly re-applied.
It is recommended to enable the flag during periods of low usage to minimize disruptions.
Default Value:
By default, the cloudsql.enable_pg audit flag is set to off, and the pgAudit extension is not enabled.
Audit Steps:
Verify the cloudsql.enable_pgaudit Flag:
Using Google Cloud Console:
Navigate to Cloud SQL Instances.
Select the PostgreSQL instance to open the Overview page.
Under Configuration > Flags, verify that the cloudsql.enable_pgaudit flag is set to on.
Using Google Cloud CLI:
Run the following command:
gcloud sql instances describe [INSTANCE_NAME] --format="json" | jq '.settings.databaseFlags[] | select(.name=="cloudsql.enable_pgaudit")|.value'Ensure the returned value is on.
Verify the pgAudit Extension Installation:
Connect to the PostgreSQL instance via a SQL client or psql.
Run the following command:
SELECT * FROM pg_extension;Confirm that pgAudit is listed in the results.
Verify Logs in Logs Explorer:
Navigate to Logs Explorer in the Google Cloud Console.
Run the following query:
resource.type="cloudsql_database"
logName="projects/[PROJECT_ID]/logs/cloudaudit.googleapis.com%2Fdata_access"
protoPayload.request.@type="type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"Confirm that logs are being correctly collected and displayed.
Remediation Steps:
Enable the cloudsql.enable_pgaudit Flag:
Using Google Cloud Console:
Go to Cloud SQL Instances.
Select the PostgreSQL instance and click Edit.
Scroll down to the Flags section.
Click Add Item and set the flag:
Name: cloudsql.enable_pgaudit
Value: on
Click Save and confirm the changes.
Restart the instance to apply the changes.
Using Google Cloud CLI:
Run the following command:
gcloud sql instances patch [INSTANCE_NAME] --database-flags cloudsql.enable_pgaudit=onRestart the instance for the changes to take effect.
Install the pgAudit Extension:
Connect to the PostgreSQL instance via a SQL client or psql.
Run the following command as a superuser:
CREATE EXTENSION pgaudit;Customize Audit Logging (Optional):
To enable logging for all databases:
From Console: Set pgaudit.log=all in the Flags section.
gcloud sql instances patch [INSTANCE_NAME] --database-flags cloudsql.enable_pgaudit=on,pgaudit.log=all
Backout Plan:
Step 1: Disable pgAudit If Necessary
gcloud sql instances patch <INSTANCE_NAME> --database-flags cloudsql.enable_pgaudit=off
Restart the instance to apply changes:
gcloud sql instances restart <INSTANCE_NAME>
Step 2: Notify Security Teams
Inform security teams before disabling pgAudit.
Step 3: Restore from Backup (If Necessary)
If pgAudit causes failures, restore the PostgreSQL instance from a backup.
References:
Additional Information:
Restart Required: Configuring the cloudsql.enable_pgaudit flag requires restarting the PostgreSQL instance.
Centralized Logging: Logs collected via the pgAudit extension can be centrally managed in Google Logs Explorer for enhanced visibility and forensic analysis.
CIS Controls: