How to log SQL queries on a Google Cloud SQL PostgreSQL 11 instance?

Question

I have to log all DDL and DML queries executed on a Google Cloud SQL PostgreSQL instance.

I checked a lot of websites, but there is no clear information available. I tried using the pgAudit extension, but that is not supported by Cloud SQL.

Can someone please suggest the extension to be used or any other way of logging SQL queries? Also, if the user logins can be logged, then that will be helpful, too.

Answer 1

Short Answer - Database Flags

The solution provided in the other answer can be used, if PostgreSQL is locally installed or if we have access to the server container. In Google Cloud, however, this file cannot be directly accessed from the instance.

I found that this can be achieved on Google Cloud SQL instance by setting the various parameters given in this link - PostgreSQL configuration parameters as database flags.

Note: All of the parameters are not supported, hence verify in the official documentation by Google given below.

Google Cloud Database Flags for PostgreSQL

Answer 2

Add in postgresql.conf:

log_statement=mod

https://www.postgresql.org/docs/12/runtime-config-logging.html says

logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type.

To log connections and disconnections, add in postgresql.conf:

log_connections=on
log_disconnections=on