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.
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.
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
On October 12, 2020 Google Cloud SQL for PostgreSQL added support for pgAudit. Please check these docs for more information.