Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u58Ed-00Ck0E-1W for pgsql-general@arkaria.postgresql.org; Wed, 16 Apr 2025 19:15:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u58Eb-003chD-3Y for pgsql-general@arkaria.postgresql.org; Wed, 16 Apr 2025 19:15:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u58Ea-003cSo-Fo for pgsql-general@lists.postgresql.org; Wed, 16 Apr 2025 19:15:45 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1u58EX-000Q35-2V for pgsql-general@lists.postgresql.org; Wed, 16 Apr 2025 19:15:44 +0000 Content-Type: multipart/alternative; boundary="------------6ImxjrF04WcHnmc7VzabYSRy" Message-ID: <419015c6-3962-40bf-bc95-ee4930d15e75@cloud.gatewaynet.com> Date: Wed, 16 Apr 2025 22:15:36 +0300 MIME-Version: 1.0 Subject: Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately To: pgsql-general@lists.postgresql.org References: <54b3d612-363e-4c05-8a08-a7563c7d52f1@cloud.gatewaynet.com> Content-Language: en-US From: Achilleas Mantzios In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------6ImxjrF04WcHnmc7VzabYSRy Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > 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 >> 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 >>> 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 , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! > > > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! --------------6ImxjrF04WcHnmc7VzabYSRy Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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!
--------------6ImxjrF04WcHnmc7VzabYSRy--