Thread: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately

Hi,

You can use pgaudit and pgauditlogtofile extension (https://github.com/fmbiete/pgauditlogtofile) together to write audit logs in a separate file. 

yours,

Kenan Çiftçi

On Tue, Apr 15, 2025 at 1:44 PM vijay patil <vijay.postgres@gmail.com> wrote:

Hi All,

We are exploring auditing solutions for our PostgreSQL database and are considering using pgaudit for this purpose. However, we have a few questions:

1. What is the best tool for auditing PostgreSQL databases?

   • We are specifically looking for a solution that offers detailed auditing capabilities and is compatible with our setup.

2. Can we store the audit information separately from PostgreSQL logs if we decide to use pgaudit?

   • We would prefer to keep the audit logs in a separate file or location for easier management and analysis.

We appreciate any help or suggestions!

Thanks

Vijay

On 4/15/25 12:14, KENAN ÇİFTÇİ wrote:

Hi,

You can use pgaudit and pgauditlogtofile extension (https://github.com/fmbiete/pgauditlogtofile) together to write audit logs in a separate file.

One issue we have with pgaudit is that it prints AUDIT records even if the xaction gets rollbacked, how do you alleviate that ?

yours,

Kenan Çiftçi

On Tue, Apr 15, 2025 at 1:44 PM vijay patil <vijay.postgres@gmail.com> wrote:

Hi All,

We are exploring auditing solutions for our PostgreSQL database and are considering using pgaudit for this purpose. However, we have a few questions:

1. What is the best tool for auditing PostgreSQL databases?

   • We are specifically looking for a solution that offers detailed auditing capabilities and is compatible with our setup.

2. Can we store the audit information separately from PostgreSQL logs if we decide to use pgaudit?

   • We would prefer to keep the audit logs in a separate file or location for easier management and analysis.

We appreciate any help or suggestions!

Thanks

Vijay

On 16/4/25 15:36, Ron Johnson wrote:

pgaudit is statement-level, not transaction-level; that's its nature.  This is the same as log_statement.

ok, but log_statement prints ROLLBACKs/COMMITs, but pgaudit not.

On Wed, Apr 16, 2025 at 5:10 AM Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com> wrote:

On 4/15/25 12:14, KENAN ÇİFTÇİ wrote:

Hi,

You can use pgaudit and pgauditlogtofile extension (https://github.com/fmbiete/pgauditlogtofile) together to write audit logs in a separate file.

One issue we have with pgaudit is that it prints AUDIT records even if the xaction gets rollbacked, how do you alleviate that ?

yours,

Kenan Çiftçi

On Tue, Apr 15, 2025 at 1:44 PM vijay patil <vijay.postgres@gmail.com> wrote:

Hi All,

We are exploring auditing solutions for our PostgreSQL database and are considering using pgaudit for this purpose. However, we have a few questions:

1. What is the best tool for auditing PostgreSQL databases?

   • We are specifically looking for a solution that offers detailed auditing capabilities and is compatible with our setup.

2. Can we store the audit information separately from PostgreSQL logs if we decide to use pgaudit?

   • We would prefer to keep the audit logs in a separate file or location for easier management and analysis.

We appreciate any help or suggestions!

Thanks

Vijay

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!

On 16/4/25 21:43, Ron Johnson wrote:

You'll have to bring that up with the PgAudit maintainer.  Note, though, that the purpose of PgAudit is not "recreate the database from audit logs"; it's "what Auditors care about".  In my experience, auditors do not care about COMMIT and ROLLBACK statements.

In my experience auditors care a lot about a statement that happened versus a statement that didn't happen.

On Wed, Apr 16, 2025 at 1:35 PM Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote:

On 16/4/25 15:36, Ron Johnson wrote:

pgaudit is statement-level, not transaction-level; that's its nature.  This is the same as log_statement.

ok, but log_statement prints ROLLBACKs/COMMITs, but pgaudit not.

On Wed, Apr 16, 2025 at 5:10 AM Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com> wrote:

On 4/15/25 12:14, KENAN ÇİFTÇİ wrote:

Hi,

You can use pgaudit and pgauditlogtofile extension (https://github.com/fmbiete/pgauditlogtofile) together to write audit logs in a separate file.

One issue we have with pgaudit is that it prints AUDIT records even if the xaction gets rollbacked, how do you alleviate that ?

yours,

Kenan Çiftçi

On Tue, Apr 15, 2025 at 1:44 PM vijay patil <vijay.postgres@gmail.com> wrote:

Hi All,

We are exploring auditing solutions for our PostgreSQL database and are considering using pgaudit for this purpose. However, we have a few questions:

1. What is the best tool for auditing PostgreSQL databases?

   • We are specifically looking for a solution that offers detailed auditing capabilities and is compatible with our setup.

2. Can we store the audit information separately from PostgreSQL logs if we decide to use pgaudit?

   • We would prefer to keep the audit logs in a separate file or location for easier management and analysis.

We appreciate any help or suggestions!

Thanks

Vijay

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!

On Wed, Apr 16, 2025 at 02:43:59PM -0400, Ron Johnson wrote:
> You'll have to bring that up with the PgAudit maintainer.  Note, though,
> that the purpose of PgAudit is not "recreate the database from audit logs";
> it's "what Auditors care about".  In my experience, auditors do not care
> about COMMIT and ROLLBACK statements.

I would care about what happened.  To know what happened I'd have to see
either only audit logs for committed transactions, or also see the
COMMIT and ROLLBACK statements.