Enable 'cloudsql.enable_pgaudit' and 'pgaudit.log' Flags for PostgreSQL Database Instances

01 Sign in to the Google Cloud Management Console.

02 Select the GCP project that you want to examine from the console top navigation bar.

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

04 Click inside the Filter box, select Type and PostgreSQL <version>, then press Enter to list only the PostgreSQL database instances provisioned for the selected GCP project.

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

06 In the navigation panel, select Overview to access the configuration information available for the selected PostgreSQL instance.

07 In the Configuration section, under Database flags, check the configuration value set for the cloudsql.enable_pgaudit and the pgaudit.log database flags. If cloudsql.enable_pgaudit is set to off and the pgaudit.log isn't set to all, the pgAudit extension auditing is disabled for the selected Google Cloud PostgreSQL database instance, therefore the instance configuration is not compliant and the Audit process ends here. If cloudsql.enable_pgaudit is set to on and the pgaudit.log is set to all, continue the Audit process with the next step.

08 To determine if the pgAudit extension is installed on the database instance, connect to the selected PostgreSQL database server using a SQL client of your choice.

09 Open the PostgreSQL shell by typing psql via the command line and run the following command:

SELECT * FROM pg_extension;

10 Repeat step no. 5 – 9 for each PostgreSQL database instance available within the selected GCP project.

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

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

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

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

PROJECT_ID
cc-web-project-112233
cc-mobile-project-123123

03 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)"

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

NAME:
cc-web-postgres-instance
cc-dev-postgres-instance

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

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

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

"off"

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

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

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

"read"

09 To determine if the pgAudit extension is installed, connect to the selected PostgreSQL database server using a SQL client of your choice.

10 Open the PostgreSQL shell by typing psql via the command line and run the following command:

SELECT * FROM pg_extension;

11 Repeat steps no. 5 – 10 for each PostgreSQL database instance created for the selected GCP project.

12 Repeat steps no. 3 – 11 for each project created within your Google Cloud account.

01 Sign in to the Google Cloud Management Console.

02 Select the GCP project that you want to examine from the console top navigation bar.

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

04 Click inside the Filter box, select Type and PostgreSQL <version>, then press Enter to list only the PostgreSQL database instances provisioned for the selected GCP project.

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

06 In the navigation panel, select Overview to access the configuration details available for the selected instance.

07 Choose Edit from the console top menu to modify the instance configuration.

08 In the Customize your instance section, choose Flags to expand the panel with the database flags configured for the selected instance, and perform the following actions:

1. Find the cloudsql.enable_pgaudit flag and enable it by selecting On from the Value dropdown list. If cloudsql.enable_pgaudit is not listed in Flags section, choose ADD FLAG, then search the flag and enable it.
2. Choose ADD FLAG, find the pgaudit.log flag and configure it by selecting all from the Value dropdown list. This will enable audit logging for all the databases available on the selected PostgreSQL instance.
3. Choose DONE to close the panel.

09 Choose SAVE to apply the configuration changes. In the Changes require restart confirmation box, choose SAVE AND RESTART.

10 To install the pgAudit extension, connect to your PostgreSQL database server using a SQL client of your choice.

11 Open the PostgreSQL shell by typing psql via the command line and run the following command to install the pgAudit extension on your database server:

CREATE EXTENSION pgaudit;

12 Repeat steps no. 5 – 11 to enable the specified flag for other PostgreSQL database instances available within the selected GCP project.

13 Repeat steps no. 2 – 12 for each project deployed in your Google Cloud account.

01 Run sql instances patch command (Windows/macOS/Linux) using the name of the PostgreSQL database instance that you want to reconfigure as the identifier parameter, to turn on the "cloudsql.enable_pgaudit" database flag and enable audit logging for all the databases available on the selected PostgreSQL instance:

gcloud sql instances patch cc-web-postgres-instance
  --database-flags cloudsql.enable_pgaudit=on,pgaudit.log=all

02 Type Y to confirm the database configuration change:

The following message will be used for the patch API method.

{"name": "cc-web-postgres-instance", "project": "cc-web-project-112233", "settings": {"databaseFlags": [{"name": "cloudsql.enable_pgaudit", "value": "on"}]}}

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-web-postgres-instance].

04 To install the pgAudit extension, connect to your PostgreSQL database server using a SQL client of your choice.

05 Open the PostgreSQL shell by typing psql via the command line and run the following command to install the pgAudit extension on the selected database server:

CREATE EXTENSION pgaudit;

06 Repeat steps no. 1 – 5 to enable the specified flag for other PostgreSQL database instances provisioned for the selected GCP project.

07 Repeat steps no. 1 – 6 for each project created within your Google Cloud account.