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 1u58Wo-00CoOu-2s for pgsql-general@arkaria.postgresql.org; Wed, 16 Apr 2025 19:34:34 +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 1u58Wl-003wid-Er for pgsql-general@arkaria.postgresql.org; Wed, 16 Apr 2025 19:34:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u58Wk-003wiC-WC for pgsql-general@lists.postgresql.org; Wed, 16 Apr 2025 19:34:31 +0000 Received: from mail-oo1-xc32.google.com ([2607:f8b0:4864:20::c32]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u58Wi-000RfB-16 for pgsql-general@postgresql.org; Wed, 16 Apr 2025 19:34:31 +0000 Received: by mail-oo1-xc32.google.com with SMTP id 006d021491bc7-60245c7309bso1885802eaf.3 for ; Wed, 16 Apr 2025 12:34:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744832066; x=1745436866; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=7yc+K2j3so6zoJaVy+P4RazhQhZoFrhXtaroBUpgG3o=; b=lBNDznCiX7Zl/4H0cMQJTULXoJL63eA8ZgxiGwWl5gJJJ+MUI38fyjTd+0wjsT2jQt /cTnIvyVVWN5EQrpQy1+xv5bO06hBZU6kDKETWoDFbI3sAQ51V0h3XIll9i3ERabGZ54 /FwYKgrXcg9Soh+LewZr4TtjH95VRMkFCuz+R59wUYnBLO5RbXsdFFqND370IxbXRorr kmp7n6kyZ1Nm+Ffcrvcki8GZ5twf91qTAclhnFPWJy01tiDndRupoqbVq0JmOqE2wG2V zWtr8xEjU5sEg+du3Ss129uI35R66TTtRCqCcXomqBlcf0Ybp8QillXhVSuNbriQKUx+ 7MnQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744832066; x=1745436866; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=7yc+K2j3so6zoJaVy+P4RazhQhZoFrhXtaroBUpgG3o=; b=DIXGMz/pBf2a1Zvr48rSPoKM6wtTCfe/it0J+d8pFxAGqTWLBNBTsNxQHrTWBbRsRE hNZ3R1QoPdcYL0/xMGYRSJA11thiUdKEDXUFrzmm5zX9G+fGKoymBS6FK5HEwuzx9zfV 0zTXYiDjxlUs7wAYayC4w6L26Kj3waa2f97aX3hbkpfx9WDUP3pDpBwtbvkUjk1kWjdU L0+SXz7SOMoyBj6jgRQBn6e+3OVHUPYFr7pXwhYjuoBeBBdbIoEjgkLPMaSLjH2pevSo CogEKM4LsCAqaPfE9XZVWTYtewToMMMltOWqw3IaFlFnxdEXSuGy21HNVr+o5iThQR/T HV7Q== X-Gm-Message-State: AOJu0YyFtXw+o8kQCvtH2zZSLwFRyRbrHhDojygXWKfAb7VlZITQO4QG 2PZctEXiYy4xgoUlfLHbEgiTqfyWBR6FyOupVW/t6i5Wg26W9Lcm7E33h4BJ0HmZOTvax6VfxTR woZdCkjt2FMbfZaSl3ESj/8KKygJfYnGT X-Gm-Gg: ASbGnctG/wk4wfSI6Pkwv5hVsAzt0X0nt6Riw4XtUMWoUgeMmaloh1F6glL5c/xmECG Z5TQ2BLjIN+3mNv1cxn8cLhB5HHSw2ueUcP8AI3c5xo56TGzP4/czjCnYDxSjbIpG1MB0eL+ctq CukrdoO8SAcCby5VStGpJJQMs= X-Google-Smtp-Source: AGHT+IHObJeXN7NDY8I7F5qlrLUk3yYE7GQPZq0y7yNs92OjSjAFPy76wGgTXWPg0hO0VN30QJFu6sEOolgXmfHsjGw= X-Received: by 2002:a4a:e847:0:b0:603:f1b5:ca0e with SMTP id 006d021491bc7-604a92a8d4dmr1667205eaf.3.1744832066347; Wed, 16 Apr 2025 12:34:26 -0700 (PDT) MIME-Version: 1.0 References: <54b3d612-363e-4c05-8a08-a7563c7d52f1@cloud.gatewaynet.com> <419015c6-3962-40bf-bc95-ee4930d15e75@cloud.gatewaynet.com> In-Reply-To: <419015c6-3962-40bf-bc95-ee4930d15e75@cloud.gatewaynet.com> From: Ron Johnson Date: Wed, 16 Apr 2025 15:34:15 -0400 X-Gm-Features: ATxdqUHTN9Qb9GXTYZRYqS4Gvz0Vf1OwiVRj6kXUfEHsYe1SWRNShD65kOkdhg8 Message-ID: Subject: Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately To: pgsql-general Content-Type: multipart/alternative; boundary="00000000000081e95b0632ea5f8e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000081e95b0632ea5f8e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Apr 16, 2025 at 3:15=E2=80=AFPM 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=E2=80=AFPM 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=E2=80=AFAM Achilleas Mantzios - cloud < >> a.mantzios@cloud.gatewaynet.com> wrote: >> >>> On 4/15/25 12:14, KENAN =C3=87=C4=B0FT=C3=87=C4=B0 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 =C3=87ift=C3=A7i >>> >>> On Tue, Apr 15, 2025 at 1:44=E2=80=AFPM vijay patil >>> wrote: >>> >>>> Hi All, >>>> >>>> We are exploring auditing solutions for our PostgreSQL database and ar= e >>>> 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! > > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000081e95b0632ea5f8e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Apr 16, 2025 at 3:15=E2=80=AFPM A= chilleas Mantzios <a.= mantzios@cloud.gatewaynet.com> wrote:
<= u> =20 =20 =20

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

=20

You'll have to bring that up with the PgAudit maintainer.= =C2=A0 Note, though, that the purpose of PgAudit is not "recreate t= he database from audit logs"; it's "what Auditors care= about".=C2=A0 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.
<= br>
The SELECT statement happened.=C2=A0 The DELETE statement suc= cessfully completed.=C2=A0 That it was rolled back is great, but the DELETE= statement still successfully completed.

If you do= n't like that ROLLBACK and COMMIT are not captured by PgAudit, open a R= equest For Feature issue:=C2=A0https://github.com/pgaudit/pgaudit/issues.=C2=A0 Or write a p= atch then submit a pull request.
=C2=A0

On Wed, Apr 16, 2025 at 1:35=E2=80=AFPM 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.=C2=A0 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=E2=80=AFAM Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.co= m> wrote:

On 4/15/25 12:14, KENAN =C3=87=C4=B0FT=C3=87=C4= =B0 wrote:

Hi,

You can use pgaudit and pgauditlogtofile extension (https://github.com= /fmbiete/pgauditlogtofile) together=C2=A0to write audit logs in a separate=C2=A0file.
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 =C3=87ift=C3=A7i

On Tue, Apr 15, 2025 at 1:44=E2=80=AFPM vijay patil &= lt;vijay.post= gres@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> lobs= ter!
--00000000000081e95b0632ea5f8e--