Ensure That 'cloudsql.enable_pgaudit' Database Flag for each Cloud Sql Postgresql Instance Is Set to 'on' For Centralized Logging

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.Enabling the pgAudit extension can lead to increased data storage requirements and to ensure durability of pgAudit log records in the event of unexpected storage issues, it is recommended to enable the Enable automatic storage increases setting on the instance. Enabling flags via the command line will also overwrite all existing flags, so you should apply all needed flags in the CLI command. Also flags may require a restart of the server to be implemented or will break existing functionality so update your servers at a time of low usage.

Suggest Edits

Risk Level: Low
Cloud Entity: GCP CloudSql
CloudGuard Rule ID: D9.GCP.LOG.29
Covered by Spectral: Yes
Category: Database

GSL LOGIC

CloudSql where databaseVersion like 'POSTGRES_%' should have (settings.databaseFlags contain [ name like 'cloudsql.enable_pgaudit' and value='on' ]) and (settings.databaseFlags contain [ name like 'pgaudit.log' and value='all'])

REMEDIATION

From Portal
A) Initialize the pgAudit flag:

Go to https://console.cloud.google.com/sql/instances.
Select the instance to open its Overview page.
Click Edit.
Scroll down and expand Flags.
To set a flag that has not been set on the instance before, click Add item.
Enter cloudsql.enable_pgaudit for the flag name and set the flag to on.
Click Done.
Click Save to update the configuration.
Confirm your changes under Flags on the Overview page.

From Command Line

Run the below command by providing 'INSTANCE_NAME' to enable 'cloudsql.enable_pgaudit' flag.

gcloud sql instances patch INSTANCE_NAME --database-flags=cloudsql.enable_pgaudit=on

Note: RESTART is required to get this configuration in effect.

FROM TF
Use resource google_sql_database_instance. Make sure you set the values for arguments as below;
settings.database_flags.name=cloudsql.enable_pgaudit and value= on

resource "google_sql_database_instance" "instance" {
	name             = "postgres-instance"
	...
	settings {
		database_flags {
			name  = "cloudsql.enable_pgaudit"
			value = "on"
		}
		...
	}

B)Creating the extension

Connect to the server running PostgreSQL or through a SQL client of your choice.
If SSHing to the server in the command line open the PostgreSQL shell by typing psql
Run the following command as a superuser.

CREATE EXTENSION pgaudit;

C) Updating the previously created pgaudit.log flag for your Logging Needs.
From Portal
Note: there are multiple options here. This command will enable logging for all databases on a server. Please see the customizing database audit logging reference for more flag options.

Go to https://console.cloud.google.com/sql/instances.
Select the instance to open its Overview page.
Click Edit.
Scroll down and expand Flags.
To set a flag that has not been set on the instance before, click Add item.
Enter pgaudit.log=all for the flag name and set the flag to on.
Click Done.
Click Save to update the configuration.
Confirm your changes under Flags on the Overview page.

From Command Line

Run the command.

gcloud sql instances patch INSTANCE_NAME --database-flags cloudsql.enable_pgaudit=on,pgaudit.log=all

From TF
Use resource google_sql_database_instance. Make sure you set the values for arguments as below;
settings.database_flags.name=cloudsql.enable_pgaudit and value= on,
settings.database_flags.name=pgaudit.log and value= all

resource "google_sql_database_instance" "instance" {
	name             = "postgres-instance"
	...
	settings {
		database_flags {
			name  = "cloudsql.enable_pgaudit"
			value = "on"
		}
		database_flags {
			name  = "pgaudit.log"
			value = "all"
		}
		...
	}

D) Determine if logs are being sent to Logs Explorer.
From Console

From the Google Console home page, open the hamburger menu in the top left.
In the menu that pops open, scroll down to Logs Explorer under Operations.
In the query box, paste the following and search

resource.type="cloudsql_database"
logName="projects/PROJECT_NAME/logs/cloudaudit.googleapis.com%2Fdata_access"
protoPayload.request.@type="type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"

References

GCP CloudSql

Cloud SQL is a fully managed database service that makes it easy to set up, maintain, manage, and administer your relational PostgreSQL, MySQL, and SQL Server databases in the cloud.

Compliance Frameworks

CloudGuard GCP All Rules Ruleset
GCP CIS Controls V 8
GCP CIS Foundations v. 1.3.0
GCP CIS Foundations v. 2.0
GCP CloudGuard Best Practices
GCP MITRE ATT&CK Framework v12.1
GCP NIST 800-53 Rev 5

Updated about 1 year ago