pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIs there anyway for non-superuser to log all sql statements at the session level?
6+ messages / 5 participants
[nested] [flat]
* Is there anyway for non-superuser to log all sql statements at the session level?
@ 2021-09-09 23:30 Hotmail <crajac66@hotmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Hotmail @ 2021-09-09 23:30 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there anyway for non-superuser to log all sql statements at the session level?
@ 2021-09-10 02:49 Dan Smith <j.daniel.smith1@gmail.com>
parent: Hotmail <crajac66@hotmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Dan Smith @ 2021-09-10 02:49 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
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@hotmail.com> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there anyway for non-superuser to log all sql statements at the session level?
@ 2021-09-10 03:18 Hotmail <crajac66@hotmail.com>
parent: Dan Smith <j.daniel.smith1@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Hotmail @ 2021-09-10 03:18 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>; Dan Smith <j.daniel.smith1@gmail.com>
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@gmail.com>, 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@hotmail.com> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there anyway for non-superuser to log all sql statements at the session level?
@ 2021-09-10 03:31 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Hotmail <crajac66@hotmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Scott Ribe @ 2021-09-10 03:31 UTC (permalink / raw)
To: Hotmail <crajac66@hotmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>; Dan Smith <j.daniel.smith1@gmail.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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there anyway for non-superuser to log all sql statements at the session level?
@ 2021-09-10 08:42 hubert depesz lubaczewski <depesz@depesz.com>
parent: Hotmail <crajac66@hotmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: hubert depesz lubaczewski @ 2021-09-10 08:42 UTC (permalink / raw)
To: Hotmail <crajac66@hotmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is there anyway for non-superuser to log all sql statements at the session level?
@ 2021-09-10 10:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2021-09-10 10:36 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: Hotmail <crajac66@hotmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-09-10 10:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-09 23:30 Is there anyway for non-superuser to log all sql statements at the session level? Hotmail <crajac66@hotmail.com>
2021-09-10 02:49 ` Dan Smith <j.daniel.smith1@gmail.com>
2021-09-10 03:18 ` Hotmail <crajac66@hotmail.com>
2021-09-10 03:31 ` Scott Ribe <scott_ribe@elevated-dev.com>
2021-09-10 08:42 ` hubert depesz lubaczewski <depesz@depesz.com>
2021-09-10 10:36 ` Tom Lane <tgl@sss.pgh.pa.us>
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