Review Cloud SQL PostgreSQL pgAudit configuration

Description

Cloud SQL PostgreSQL pgAudit records selected SQL operations. Enabling cloudsql.enable_pgaudit alone does not complete the configuration: create the extension and select audit classes with pgaudit.log. This is separate from auditing instance administration.

Potential impact

Missing SQL-operation records can hinder investigation of data access or changes.
Excessive auditing can increase sensitive SQL content, disk usage and logging costs.

Remediation

Plan for the restart and set cloudsql.enable_pgaudit to on. Create the pgAudit extension through a SQL client and select required operation classes in pgaudit.log.
Enable project Data Access audit logs and verify receipt in Cloud Logging. Manage retention, access permissions and disk usage.

Examples

These excerpts compare part of the instance settings. Use a supported engine version and supply omitted required settings. Changing this flag does not require the engine-version change shown here.

Before

hcl

resource "google_sql_database_instance" "example" {
  name             = "postgres-instance-with-flag"
  database_version = "POSTGRES_14"
  region           = "us-central1"

  settings {
    database_flags {
      name  = "cloudsql.enable_pgaudit"
      value = "off"
    }
  }
}

After

hcl

resource "google_sql_database_instance" "example" {
  name             = "postgres-instance-with-flag"
  database_version = "POSTGRES_15"
  region           = "us-central1"

  settings {
    database_flags {
      name  = "cloudsql.enable_pgaudit"
      value = "on"
    }
  }
}

Explanation:

Before: The pgAudit enable flag is off. This does not disable every other server log.
After: The prerequisite flag is enabled. Configure the extension, audit scope and log receipt separately.