After enabling pgaudit, queries are not being properly logged in the logging system.

Google Cloud Database
1

Hello,
I am currently operating a CloudSQL (PostgreSQL) environment in the GCP environment and have a question regarding an issue with the configuration of pgaudit.

  • Region: us-central1 (Iowa)
    DB Version: PostgreSQL 15.4
    Spec: 8 vCPU, 32 GB, No Data Cache, 250GB Storage, Single Zone Availability (Enterprise Edition)

For the purpose of auditing user queries, I have activated pgaudit by referring to the following documentation:

https://cloud.google.com/sql/docs/postgres/pg-audit?hl=en#customizing-database-audit-logging

  1. Activate database flags
  • $ gcloud sql instances patch [INSTANCE_NAME] --database-flags cloudsql.enable_pgaudit=on,pgaudit.log=all (or read,write)
  1. Execute the following statement in all databases to activate the pgaudit extension program for each user database
  • CREATE EXTENSION pgaudit;

After activating pgaudit through these steps, I logged in with a user account and executed a select query, but the action was not properly recorded in the logging.

I would like to inquire if there are any additional contents to check or any guides available.

1 Like
2

Hi @zzizim ,

Welcome to Google Cloud Community!

I’ve managed to replicate your concern by following the documentation that you provided. When another user is accessing the SQL instance, it would only show the database user but not the service account itself.

You may consider using IAM in a database which lets you give access to specific Google Cloud resources and prevent unwanted access to other resources. Check out this documentation on IAM authentication on Cloud SQL for PostgreSQL for additional information.

Hope this helps.