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

I have enabled below FLAGS from GCP console on my Postgres service and now able to generate all type of DB activity/query logs

cloudsql.iam_authentication
on
pgaudit.log
all
log_statement
all

Answer 2

You might also want to consider verifying your user has the roles/logging.privateLogViewer "Private Logs Viewer" role. Per https://cloud.google.com/logging/docs/access-control#considerations :

roles/logging.privateLogViewer (Private Logs Viewer) includes roles/logging.viewer, plus the ability to read Access Transparency logs and Data Access audit logs in the _Required and _Default buckets.

We struggled a long time to figure out why we weren't seeing these logs ourselves, and it ended up being that the "Logs Viewer" role is insufficient to read these data access logs.

Answer 3

Take into consideration that you'd need to enable Data Access Audit Logs, nonetheless the logs are only written to Cloud Logging if the operation is an authenticated user-driven API call.

Although the product is still in Pre-GA Offering terms, I would recommend you to test the pgaudit extension as explained within the relevant section of the public docs. The steps are quite simple, as you should:

1. Enable the relevant flag on the instance by issuing gcloud sql instances patch [INSTANCE_NAME] --database-flags cloudsql.enable_pgaudit=on.
2. Use the command to create the extension within the instance (or particular database) by signing into the instance with the psql client with an user that has the cloudsqlsuperuser role: CREATE EXTENSION pgaudit;
3. Set the values for the pgaudit.log flag (e.g. to turn on all database operations within an instance: gcloud sql instances patch [INSTANCE_NAME] --database-flags cloudsql.enable_pgaudit=on,pgaudit.log=all).

Notice that depending on the load that your database receives enabling these flags might translate in CPU and memory overload so it might be wise to overprovision the tier assigned to the instance as per your current load and that enabling such logs could translate in an increase in Billing as you'd be able to see the logs within the cloudaudit.googleapis.com/data_access Data access audit logs that are charged separately.

Answer 4

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.