Re: Is there anyway for non-superuser to log all sql statements at the session level?

[Hotmail <crajac66@xxxxxxxxxxx>]

Thanks for the suggestion. We currently use pgaudit at the database level for all ddL. Unfortunately, you must be a superuser to change pgaudit  settings at the session level. Our desire is to capture the ddl/dml for a specific session.  Capturing the dml at the database level with pgaudit would generate too much logging with our application user. Our migrations scripts are all checked into a git repo. 

We would like to get specific timestamp/timing info for each migration ddl/dml statement as it is executed. May not be possible in postgres for a non-superuser. We recently migrated off Oracle and we were able to do this as a non-superuser with session tracing but we are not aware similar capabilities in postgres.

Craig

On Sep 9, 2021, 8:50 PM -0600, Dan Smith <j.daniel.smith1@xxxxxxxxx>, wrote:

pgAudit would do the trick (https://www.pgaudit.org/).  If memory serves, you can specify a user to audit as well as the type of operations.

That said, if you are using migration scripts, it may be easier to just keep the scripts in version control with a sequential naming convention.  Generally, these tools save state and know what has been applied.

Best regards,

Dan Smith

On Thu, Sep 9, 2021, 16:31 Hotmail <crajac66@xxxxxxxxxxx> wrote:

Hi All,

We use liquibase to run migrations against our postgres 11 database. We run the migrations as an application user that does not have superuser permissions. Is there any workaround we could use that would allow us to capture the migration sql that is run by our application user? We want to capture all of the ddl as well as the dml run by the migration user. Since setting log_statement=all  at the session level isn’t possible unless the user is a superuser is there any other way to accomplish this task? 

Thanks,

Craig Jackson