Обсуждение: Is there anyway for non-superuser to log all sql statements at the session level?

> 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
superuserto 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
migrationsscripts 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
bepossible in postgres for a non-superuser. We recently migrated off Oracle and we were able to do this as a
non-superuserwith 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?

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.

Best regards,

depesz

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