pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to Log DBA actions
7+ messages / 5 participants
[nested] [flat]
* How to Log DBA actions
@ 2021-12-20 06:34 Daulat <daulat.dba@gmail.com>
2021-12-20 06:43 ` Re: How to Log DBA actions Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Daulat @ 2021-12-20 06:34 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi Team,
I want to log DBA actions in Postgres. Can you please suggest how I can
achieve it.
Will pg_audit and log_min_duration_statements help on it?
Thanks,
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to Log DBA actions
2021-12-20 06:34 How to Log DBA actions Daulat <daulat.dba@gmail.com>
@ 2021-12-20 06:43 ` Holger Jakobs <holger@jakobs.com>
2021-12-20 06:54 ` Re: How to Log DBA actions Daulat <daulat.dba@gmail.com>
2021-12-21 21:25 ` Re: How to Log DBA actions Nikolay Samokhvalov <samokhvalov@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Holger Jakobs @ 2021-12-20 06:43 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org, Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
There is a setting log_statements which can be set to none (the default), ddl, mod or all
This might help.
Am 20. Dezember 2021 07:34:13 MEZ schrieb Daulat <daulat.dba@gmail.com>:
>Hi Team,
>
>I want to log DBA actions in Postgres. Can you please suggest how I can
>achieve it.
>Will pg_audit and log_min_duration_statements help on it?
>
>Thanks,
--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to Log DBA actions
2021-12-20 06:34 How to Log DBA actions Daulat <daulat.dba@gmail.com>
2021-12-20 06:43 ` Re: How to Log DBA actions Holger Jakobs <holger@jakobs.com>
@ 2021-12-20 06:54 ` Daulat <daulat.dba@gmail.com>
2021-12-21 20:53 ` Re: How to Log DBA actions Michael Banck <michael.banck@credativ.de>
1 sibling, 1 reply; 7+ messages in thread
From: Daulat @ 2021-12-20 06:54 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Thanks Holger !
It means, there is no use of pgaudit if we already have log_statements
enabled?
On Mon, Dec 20, 2021 at 12:13 PM Holger Jakobs <holger@jakobs.com> wrote:
> There is a setting log_statements which can be set to none (the default),
> ddl, mod or all
>
> This might help.
>
> Am 20. Dezember 2021 07:34:13 MEZ schrieb Daulat <daulat.dba@gmail.com>:
>>
>> Hi Team,
>>
>> I want to log DBA actions in Postgres. Can you please suggest how I can
>> achieve it.
>> Will pg_audit and log_min_duration_statements help on it?
>>
>> Thanks,
>>
>>
>> --
> Holger Jakobs, Bergisch Gladbach
> +49 178 9759012
> - sent from mobile, therefore short -
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to Log DBA actions
2021-12-20 06:34 How to Log DBA actions Daulat <daulat.dba@gmail.com>
2021-12-20 06:43 ` Re: How to Log DBA actions Holger Jakobs <holger@jakobs.com>
2021-12-20 06:54 ` Re: How to Log DBA actions Daulat <daulat.dba@gmail.com>
@ 2021-12-21 20:53 ` Michael Banck <michael.banck@credativ.de>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Banck @ 2021-12-21 20:53 UTC (permalink / raw)
To: Daulat <daulat.dba@gmail.com>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Dec 20, 2021 at 12:24:14PM +0530, Daulat wrote:
> It means, there is no use of pgaudit if we already have log_statements
> enabled?
It depends. log_statments, logs *all* statements of the particular type,
so if you want to log whether the DBA do any data changes at all and set
it to "mod", that means, any other data changes (from the application)
will get logged as well.
Maybe you can live with just log_statement = ddl, or you can set
role-specific log_statement = mod,ddl for the DBA roles (this might not
work on a managed Postgres service).
If not, pgaudit allows to log much more fine-grained and targetted audit
events.
Michael
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to Log DBA actions
2021-12-20 06:34 How to Log DBA actions Daulat <daulat.dba@gmail.com>
2021-12-20 06:43 ` Re: How to Log DBA actions Holger Jakobs <holger@jakobs.com>
@ 2021-12-21 21:25 ` Nikolay Samokhvalov <samokhvalov@gmail.com>
2021-12-21 21:31 ` Re: How to Log DBA actions Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Nikolay Samokhvalov @ 2021-12-21 21:25 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
On Sun, Dec 19, 2021 at 10:43 PM Holger Jakobs <holger@jakobs.com> wrote:
> There is a setting log_statements which can be set to none (the default),
> ddl, mod or all
>
..and, unfortunately, any user will be able to set it back via SET. This
extension is aimed to solve the problem:
https://gitlab.com/ongresinc/extensions/noset.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to Log DBA actions
2021-12-20 06:34 How to Log DBA actions Daulat <daulat.dba@gmail.com>
2021-12-20 06:43 ` Re: How to Log DBA actions Holger Jakobs <holger@jakobs.com>
2021-12-21 21:25 ` Re: How to Log DBA actions Nikolay Samokhvalov <samokhvalov@gmail.com>
@ 2021-12-21 21:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-21 21:49 ` Re: How to Log DBA actions Nikolay Samokhvalov <samokhvalov@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2021-12-21 21:31 UTC (permalink / raw)
To: Nikolay Samokhvalov <samokhvalov@gmail.com>; +Cc: Holger Jakobs <holger@jakobs.com>; Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
Nikolay Samokhvalov <samokhvalov@gmail.com> writes:
> On Sun, Dec 19, 2021 at 10:43 PM Holger Jakobs <holger@jakobs.com> wrote:
>> There is a setting log_statements which can be set to none (the default),
>> ddl, mod or all
> ..and, unfortunately, any user will be able to set it back via SET.
Really?
regression=# create user joe;
CREATE ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> set log_statement = none;
ERROR: permission denied to set parameter "log_statement"
It is true that superusers can change that variable. However, if
you are trying to force monitoring of an uncooperative superuser,
you are in for a hard slog.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to Log DBA actions
2021-12-20 06:34 How to Log DBA actions Daulat <daulat.dba@gmail.com>
2021-12-20 06:43 ` Re: How to Log DBA actions Holger Jakobs <holger@jakobs.com>
2021-12-21 21:25 ` Re: How to Log DBA actions Nikolay Samokhvalov <samokhvalov@gmail.com>
2021-12-21 21:31 ` Re: How to Log DBA actions Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-12-21 21:49 ` Nikolay Samokhvalov <samokhvalov@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Nikolay Samokhvalov @ 2021-12-21 21:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Holger Jakobs <holger@jakobs.com>; Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, Dec 21, 2021 at 1:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Nikolay Samokhvalov <samokhvalov@gmail.com> writes:
> > On Sun, Dec 19, 2021 at 10:43 PM Holger Jakobs <holger@jakobs.com>
> wrote:
> >> There is a setting log_statements which can be set to none (the
> default),
> >> ddl, mod or all
>
> > ..and, unfortunately, any user will be able to set it back via SET.
>
> Really?
>
You're right, I was wrong.
postgres=# select context from pg_settings where name = 'log_statement';
context
-----------
superuser
(1 row)
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-12-21 21:49 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-20 06:34 How to Log DBA actions Daulat <daulat.dba@gmail.com>
2021-12-20 06:43 ` Holger Jakobs <holger@jakobs.com>
2021-12-20 06:54 ` Daulat <daulat.dba@gmail.com>
2021-12-21 20:53 ` Michael Banck <michael.banck@credativ.de>
2021-12-21 21:25 ` Nikolay Samokhvalov <samokhvalov@gmail.com>
2021-12-21 21:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-21 21:49 ` Nikolay Samokhvalov <samokhvalov@gmail.com>
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