Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.13\))
Subject: Re: Is there anyway for non-superuser to log all sql statements at
 the session level?
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <SJ0PR06MB76462E1183CDCEFAA6FC69C6D7D69@SJ0PR06MB7646.namprd06.prod.outlook.com>
Date: Thu, 9 Sep 2021 21:31:20 -0600
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>,
 Dan Smith <j.daniel.smith1@gmail.com>
Content-Transfer-Encoding: quoted-printable
Message-Id: <B2D5A861-5FF0-4CB0-90B9-A6422EB0D470@elevated-dev.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>
To: Hotmail <crajac66@hotmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/B2D5A861-5FF0-4CB0-90B9-A6422EB0D470%40elevated-dev.com>
Precedence: bulk


> On Sep 9, 2021, at 9:18 PM, Hotmail <crajac66@hotmail.com> wrote:
>=20
> 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.=20
>=20
> 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?