From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Hotmail <crajac66@hotmail.com>
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Cc: Dan Smith <j.daniel.smith1@gmail.com>
Subject: Re: Is there anyway for non-superuser to log all sql statements at the session level?
Date: Thu, 9 Sep 2021 21:31:20 -0600
Message-ID: <B2D5A861-5FF0-4CB0-90B9-A6422EB0D470@elevated-dev.com> (raw)
In-Reply-To: <SJ0PR06MB76462E1183CDCEFAA6FC69C6D7D69@SJ0PR06MB7646.namprd06.prod.outlook.com>
References: <16e9ff79-fe32-4d0d-be57-7c823744a793@Spark>
<SJ0PR06MB76469C5B646B4551B6292DBFD7D59@SJ0PR06MB7646.namprd06.prod.outlook.com>
<CAK50Jrx3azdVqVKVs+j3kqZQnfTU0pf9ve20J5H-E__pYSivJw@mail.gmail.com>
<SJ0PR06MB76462E1183CDCEFAA6FC69C6D7D69@SJ0PR06MB7646.namprd06.prod.outlook.com>
> On Sep 9, 2021, at 9:18 PM, Hotmail <crajac66@hotmail.com> wrote:
>
> 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.
Can't you use a single-purpose user to run migration scripts, and pgaudit to log everything that user does?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, crajac66@hotmail.com, pgsql-admin@lists.postgresql.org, j.daniel.smith1@gmail.com
Subject: Re: Is there anyway for non-superuser to log all sql statements at the session level?
In-Reply-To: <B2D5A861-5FF0-4CB0-90B9-A6422EB0D470@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox