GCP Cloud SQL Query Logging for Postgres

Question

I've been struggling for a few weeks now to try and get a Cloud SQL Postgres instance to do any type of query logging and I'm just not getting anywhere with it. I realise that this should be a super simple exercise, but I just don't have any more of an explanation to give than it just doesn't seem to work.

I've tried setting the following flags:

log_min_duration_statement with values of 0, 100, 500 and 1000.

log_statement with values of 'ddl', 'mod' and 'all'.

log_statement_stats set to 'on'.

I've also tried a bunch of other "likely suspects" listed here: https://cloud.google.com/sql/docs/postgres/flags to no avail.

I've even tried enabling the pgaudit logs, which didn't seem to do anything.

Given that, there's always a chance this data is being logged, but I'm just not finding it, so in stackdriver, my filter is:

resource.type="cloudsql_database"
resource.labels.database_id="<<project-name>>:<<instance-name>>"

Which shows me any errors that occur on the DB, like querying a table that doesn't exist, or attempting to compare two different data types, but no queries of any kind.

I've even gone as far as scanning all of our logs with:

textPayload:"select"

Which also doesn't turn up anything.

Any advice, tips or tricks would be greatly appreciated!

EDIT: Additional information.

The instance that I'm testing with is described in this image:

I'm able to create the pgaudit extension on all of my schemas without any errors. The psql client simply echoes back 'CREATE EXTENSION'.

I'm connecting to the instance using private IP, from a GKE environment, and my applications run both DDL & DML statements.

This is an image of the 'Audit Logs' I enabled for cloud SQL within the Access section of the cloud console:

This is a brand new instance that I'm using for load testing, so each morning I create a new instance using a terraform script, here is the relevant resource:

resource "google_sql_database_instance" "loadtesting_postgres_master" {
   database_version = "POSTGRES_12"
   name             = "loadtesting-test-instance-12"
   project          = "${var.projectId}"
   region           = "${var.region}"

   depends_on = ["google_service_networking_connection.private_vpc_connection"]
   deletion_protection = false

   settings {

activation_policy = "ALWAYS"
availability_type = "ZONAL"

backup_configuration {
  binary_log_enabled             = "false"
  enabled                        = "true"
  location                       = "eu"
  point_in_time_recovery_enabled = "true"
  start_time                     = "03:00"
}

disk_autoresize        = "false"
disk_size              = "1000"
disk_type              = "PD_SSD"

ip_configuration {
  ipv4_enabled    = "true"
  private_network = "projects/my-project-name/global/networks/loadtesting-vpc"
  require_ssl     = "false"
}

location_preference {
  zone = "${var.region}"
}

maintenance_window {
  day  = "7"
  hour = "0"
}

pricing_plan     = "PER_USE"
tier             = "db-custom-8-15360"
  }
}

After the instance comes up, I manually add the two flags you've recommended, one at a time.

Manually create the pg audit extension on each of my schemas, and start running my tests.

Still nothing getting logged on stackdriver.

Answer 1

So it looks like you need to enable the pgaudit flags. (Currently in beta)

To summarize the link:

INSTANCE_NAME=sql-playground
gcloud sql instances patch $INSTANCE_NAME --database-flags cloudsql.enable_pgaudit=on`
gcloud sql connect $INSTANCE_NAME

Once inside the DB, run this:

> CREATE EXTENSION pgaudit;

Then back in gcloud land:

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

It takes a restart or two and a few moments in between, but I did get it to log the statements:

Of course, be careful in production env, and with PII data, etc, etc.