Postgres Pro
Downloads
Home   >   mailing lists

Thread: Who altered the database?

Who altered the database?

From
Durumdara
Date:
Dear Members!

We have a server.
Because of high usage we can't log DDL-s. There are too many temporary tables created by users, so it would slow down the server.
A database owner changed.

What can we do to get info about the change (who did it, from what IP, when it happened) in the future?

As I see we can't define precisely what we want to log... All or nothing.

Do you know any tricks to do it?

For example: DB level trigger, or other thing?

Thank you for the answer!

Best regards

dd












Re: Who altered the database?

From
Ron
Date:
On 4/9/21 6:14 AM, Durumdara wrote:
> Dear Members!
>
> We have a server.
> Because of high usage we can't log DDL-s. There are too many temporary 
> tables created by users, so it would slow down the server.
> A database owner changed.
>
> What can we do to get info about the change (who did it, from what IP, 
> when it happened) in the future?
>
> As I see we can't define precisely what we want to log... All or nothing.
>
> Do you know any tricks to do it?
>
> For example: DB level trigger, or other thing?

pg_audit?

-- 
Angular momentum makes the world go 'round.

Re: Who altered the database?

From
Ganesh Korde
Date:


On Fri, 9 Apr 2021, 4:44 pm Durumdara, <durumdara@gmail.com> wrote:
Dear Members!

We have a server.
Because of high usage we can't log DDL-s. There are too many temporary tables created by users, so it would slow down the server.
A database owner changed.

What can we do to get info about the change (who did it, from what IP, when it happened) in the future?

As I see we can't define precisely what we want to log... All or nothing.

Do you know any tricks to do it?

For example: DB level trigger, or other thing?

Thank you for the answer!

Best regards

dd



You can use different options in log_line_prefix parameter in postgresql.conf
You can also log connection and disconnection, each has separate parameter in configuration file.

Regards,
Ganesh Korde.

Re: Who altered the database?

From
Vincent Veyron
Date:
On Fri, 9 Apr 2021 13:14:17 +0200
Durumdara <durumdara@gmail.com> wrote:
>
> What can we do to get info about the change (who did it, from what IP, when
> it happened) in the future?
>
> As I see we can't define precisely what we want to log... All or nothing.
>

Create a web interface to apply the changes and have the web server log the info in its own logs?

Apache has this directive :

https://httpd.apache.org/docs/2.4/mod/mod_log_config.html#logformat

--
                    Bien à vous, Vincent Veyron

https://marica.fr/
Logiciel de suivi des contentieux juridiques, des sinistres d'assurance et des contrats