From: Tom Lane <tgl@sss.pgh.pa.us>
To: depesz@depesz.com
cc: Hotmail <crajac66@hotmail.com>, pgsql-admin@lists.postgresql.org
Subject: Re: Is there anyway for non-superuser to log all sql statements at
 the session level?
In-reply-to: <20210910084255.GB11352@depesz.com>
References: <16e9ff79-fe32-4d0d-be57-7c823744a793@Spark>
 <SJ0PR06MB76469C5B646B4551B6292DBFD7D59@SJ0PR06MB7646.namprd06.prod.outlook.com>
 <20210910084255.GB11352@depesz.com>
Comments: In-reply-to hubert depesz lubaczewski <depesz@depesz.com>
	message dated "Fri, 10 Sep 2021 10:42:55 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <3933155.1631270181.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Fri, 10 Sep 2021 06:36:21 -0400
Message-ID: <3933156.1631270181@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3933156.1631270181%40sss.pgh.pa.us>
Precedence: bulk

hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Thu, Sep 09, 2021 at 05:30:55PM -0600, Hotmail wrote:
>> 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?

> Sure. Make a security-definer function that changes the log_statement,
> and grant privileges to call it to whoever you need.

Also you can do

alter user foo set log_statement = 'all';

to apply the setting at future logins of user foo.  (While a non-superuser
can't do that for themselves, a superuser can do it for them.)

			regards, tom lane