Thread
-
Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
vijay patil <vijay.postgres@gmail.com> — 2025-04-15T10:44:03Z
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 -
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
KENAN ÇİFTÇİ <kciftci89@gmail.com> — 2025-04-15T11:14:03Z
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 >
-
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
vijay patil <vijay.postgres@gmail.com> — 2025-04-16T05:15:24Z
Hi Kenan, Thanks for the quick response. Thanks Vijay On Tue, Apr 15, 2025 at 4:44 PM KENAN ÇİFTÇİ <kciftci89@gmail.com> wrote: > 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 >> >
-
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2025-04-16T09:10:22Z
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 >
-
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
Ron <ronljohnsonjr@gmail.com> — 2025-04-16T12:36:06Z
pgaudit is statement-level, not transaction-level; that's its nature. This is the same as log_statement. 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!
-
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2025-04-16T17:35:26Z
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!
-
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
Ron <ronljohnsonjr@gmail.com> — 2025-04-16T18:43:59Z
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. 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!
-
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2025-04-16T19:15:36Z
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!
-
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
Ron <ronljohnsonjr@gmail.com> — 2025-04-16T19:34:15Z
On Wed, Apr 16, 2025 at 3:15 PM Achilleas Mantzios < a.mantzios@cloud.gatewaynet.com> wrote: > 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. > The SELECT statement happened. The DELETE statement successfully completed. That it was rolled back is great, but the DELETE statement still successfully completed. If you don't like that ROLLBACK and COMMIT are not captured by PgAudit, open a Request For Feature issue: https://github.com/pgaudit/pgaudit/issues. Or write a patch then submit a pull request. > > 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! > > -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
-
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
Nico Williams <nico@cryptonector.com> — 2025-04-16T19:42:54Z
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. Nico --
-
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
Ron <ronljohnsonjr@gmail.com> — 2025-04-16T19:53:53Z
On Wed, Apr 16, 2025 at 3:42 PM Nico Williams <nico@cryptonector.com> wrote: > 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. > Decode the logical replication stream? -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
-
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
Nico Williams <nico@cryptonector.com> — 2025-04-16T19:59:37Z
On Wed, Apr 16, 2025 at 03:53:53PM -0400, Ron Johnson wrote: > On Wed, Apr 16, 2025 at 3:42 PM Nico Williams <nico@cryptonector.com> wrote: > > 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. > > Decode the logical replication stream? Yes, or use Debezium or the like which... do just that.