Another way to do audit in DML operations in PostgreSQL >= 14

pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Another way to do audit in DML operations in PostgreSQL >= 14
7+ messages / 4 participants
[nested] [flat]

* Another way to do audit in DML operations in PostgreSQL >= 14
@ 2024-02-22 19:14 Lucio Chiessi <lucio.chiessi@trustly.com>
  2024-02-22 23:36 ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 Bruce Momjian <bruce@momjian.us>
  2024-02-23 04:54 ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 S Hoffman <hoffman7043@gmail.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Lucio Chiessi @ 2024-02-22 19:14 UTC (permalink / raw)
  To: PGSql Admin list <pgsql-admin@lists.postgresql.org>

As a Fintech company, we *MUST* audit (by force of law) all DML operations
(select, insert, update, delete) occurring on our PostgreSQL databases.
Today we used PostgreSQL log files to do this and tested pgAudit too.
In these cases (native log files and pgAudit) we having a lot of contention
on backends and high values in the instance CPU (system and wait %).   This
only happens when we on high database TPS rate.
I think that an unique PostgreSQL logger process can't attend all backend
processes when trying to pipe data on it, causing contention and
performance slowdown.
So, I understand that the PostgreSQL logs rule is not to log everything
happening in the database in a high TPS scenario.
I know that we can use triggers to have an audit process, but I need to
audit selects too, and rules only run as instead for selects.

So, I'm looking for another way to this all DML audit and will appreciate
so much your opinion on this.

Thanks for all the advice and have a happy week.

*Lucio Chiessi*

Senior Database Administrator

Trustly, Inc.

M: +55 27 996360276

<https://www.linkedin.com/company/trustly/;
<https://www.facebook.com/trustly;  <https://twitter.com/Trustly;

<https://www.linkedin.com/company/trustly/;
<https://www.facebook.com/trustly;   <https://twitter.com/Trustly;

<https://www.trustly.net/;

-- 
Please read our privacy policy here 
<https://www.trustly.net/about-us/privacy-policy; on how we process your 
personal data in accordance with the General Data Protection Regulation 
(EU) 2016/679 (the “GDPR”) and other applicable data protection legislation.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Another way to do audit in DML operations in PostgreSQL >= 14
  2024-02-22 19:14 Another way to do audit in DML operations in PostgreSQL >= 14 Lucio Chiessi <lucio.chiessi@trustly.com>
@ 2024-02-22 23:36 ` Bruce Momjian <bruce@momjian.us>
  1 sibling, 0 replies; 7+ messages in thread

From: Bruce Momjian @ 2024-02-22 23:36 UTC (permalink / raw)
  To: Lucio Chiessi <lucio.chiessi@trustly.com>; +Cc: PGSql Admin list <pgsql-admin@lists.postgresql.org>

On Thu, Feb 22, 2024 at 04:14:24PM -0300, Lucio Chiessi wrote:
> As a Fintech company, we MUST audit (by force of law) all DML operations
> (select, insert, update, delete) occurring on our PostgreSQL databases.

FYI, DML is data manipulation language (DML), and SELECT is not DML.  I
don't know the answer to your performance question.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Another way to do audit in DML operations in PostgreSQL >= 14
  2024-02-22 19:14 Another way to do audit in DML operations in PostgreSQL >= 14 Lucio Chiessi <lucio.chiessi@trustly.com>
@ 2024-02-23 04:54 ` S Hoffman <hoffman7043@gmail.com>
  2024-02-23 14:29   ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 Bruce Momjian <bruce@momjian.us>
  1 sibling, 1 reply; 7+ messages in thread

From: S Hoffman @ 2024-02-23 04:54 UTC (permalink / raw)
  To: Lucio Chiessi <lucio.chiessi@trustly.com>; +Cc: PGSql Admin list <pgsql-admin@lists.postgresql.org>


--Apple-Mail-7D7CBCD5-3F09-431D-B76F-84E7E08D828D
Content-Type: text/html;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">Hi Lucio Chiessi,<div><br></div><div>Dealin=
g with large volumes of data can be a challenging task. If you struggle with=
 this issue, one possible solution is to consider using external logging ser=
vices. Specifically, asynchronous logging services or external logging servi=
ces that are designed to handle streaming data may be effective options to e=
xplore. Some popular external logging services in this regard include Elasti=
csearch and Spunk, known for their ability to handle large volumes of data. I=
f you have any questions or want to explore other options, please let me kno=
w.&nbsp;</div><div><br></div><div>Kind regards,</div><div>Sondra</div><div><=
br></div><div><br id=3D"lineBreakAtBeginningOfSignature"><div dir=3D"ltr">Se=
nt from my iPhone</div><div dir=3D"ltr"><br><blockquote type=3D"cite">On Feb=
 22, 2024, at 11:14=E2=80=AFAM, Lucio Chiessi &lt;lucio.chiessi@trustly.com&=
gt; wrote:<br><br></blockquote></div><blockquote type=3D"cite"><div dir=3D"l=
tr">=EF=BB=BF<div dir=3D"ltr"><div>As a Fintech company, we <b>MUST</b> audi=
t (by force of law) all DML operations (select, insert, update, delete) occu=
rring on our PostgreSQL databases.</div><div>Today we used PostgreSQL log fi=
les to do this and tested pgAudit too.</div><div>In these cases (native log f=
iles and pgAudit) we having a lot of contention on backends and high values i=
n the instance CPU (system and wait %).&nbsp; &nbsp;This only happens when w=
e on high&nbsp;database TPS rate.</div><div>I think that an unique PostgreSQ=
L logger process can't&nbsp;attend all backend processes when trying to pipe=
 data on it, causing&nbsp;contention and performance slowdown.&nbsp;&nbsp;</=
div><div>So, I understand that the PostgreSQL logs rule is not to log everyt=
hing happening in the&nbsp;database in&nbsp;a&nbsp;high TPS scenario.</div><=
div>I know that we can use triggers to have an audit process, but I need to a=
udit selects too, and rules only run as <font face=3D"monospace">instead</fo=
nt> for <font face=3D"monospace">selects</font>.</div><div><br></div><div>So=
, I'm looking for another way to this all DML audit and will appreciate so m=
uch your opinion on this.</div><div><br></div><div>Thanks for all the advice=
 and have a happy week.&nbsp;</div><br clear=3D"all"><div><div dir=3D"ltr" c=
lass=3D"gmail_signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"=
><p class=3D"MsoNormal" style=3D"color:rgb(34,34,34)"><b><span style=3D"font=
-family:Arial,sans-serif;color:black">Lucio Chiessi</span></b><span style=3D=
"font-family:Arial,sans-serif;color:black"></span></p><p class=3D"MsoNormal"=
><font color=3D"#000000" face=3D"Arial, sans-serif">Senior Database Administ=
rator</font></p><p class=3D"MsoNormal" style=3D"color:rgb(34,34,34)"><span s=
tyle=3D"font-family:Arial,sans-serif;color:black">Trustly, Inc.</span></p><p=
 class=3D"MsoNormal" style=3D"color:rgb(34,34,34)"><span style=3D"font-famil=
y:Arial,sans-serif;color:black">M: +55 27 996360276</span></p><p class=3D"Ms=
oNormal" style=3D"color:rgb(34,34,34)"><span style=3D"font-family:Arial,sans=
-serif;color:black"><a href=3D"https://www.linkedin.com/company/trustly/"; ta=
rget=3D"_blank"><img src=3D"https://ci3.googleusercontent.com/mail-sig/AIorK=
4zAAE1IxyiCWzQH7LlbQvWzfPJSaezaeGcuUb-o7qLExY6u4Axla7Vg4M_47so109-UJzmqNN4" d=
ata-unique-identifier=3D""></a>&nbsp;<a href=3D"https://www.facebook.com/tru=
stly" target=3D"_blank"><img src=3D"https://ci3.googleusercontent.com/mail-s=
ig/AIorK4woeblfRGYD_MkZYgAuhnmcGJAL4GQj8B8QakHtJQ-nv4myHxBkBwmGZsMMcd3EBwpHC=
FEr4gQ" data-unique-identifier=3D""></a>&nbsp;<a href=3D"https://twitter.com=
/Trustly" target=3D"_blank"><img src=3D"https://ci3.googleusercontent.com/ma=
il-sig/AIorK4yXMfV603SEnNTg-F3grGUtcmkk3sDyQKooDLBgTtARiC5_ZgV7zV8aiHfPIloh8=
EXtooI0x-I" data-unique-identifier=3D""></a><br></span></p><p class=3D"MsoNo=
rmal" style=3D"color:rgb(34,34,34)"><a href=3D"https://www.linkedin.com/comp=
any/trustly/" style=3D"color:rgb(17,85,204)" target=3D"_blank"><span style=3D=
"font-family:Arial,sans-serif;color:black"><img border=3D"0" width=3D"13" he=
ight=3D"13" src=3D"https://mail.google.com/mail/u/0/?ui=3D2&amp;ik=3D42095eb=
97c&amp;attid=3D0.2&amp;permmsgid=3Dmsg-f%3A1686071028223994457&amp;th=3D176=
620aabf5e6a59&amp;view=3Dfimg&amp;sz=3Ds0-l75-ft&amp;attbid=3DANGjdJ831NJNFV=
3dlJGwvO0DLaa5HS945e1uJRwzE80C2njvV0E3-3Ub2-bC4YruPygt0CgkGv8LqQeYjUuQ4yOZpr=
J-XB3kUwDACfwxEPqEq04a89Ud0qnlqoHLaXk&amp;disp=3Demb&amp;realattid=3D567a03a=
bbf259cca_0.2" style=3D"width:0.1319in;height:0.1319in" data-unique-identifi=
er=3D""></span></a><span style=3D"font-family:Arial,sans-serif;color:black">=
&nbsp;&nbsp;</span><a href=3D"https://www.facebook.com/trustly"; style=3D"col=
or:rgb(17,85,204)" target=3D"_blank"><span style=3D"font-family:Arial,sans-s=
erif;color:black"><img border=3D"0" width=3D"13" height=3D"13" src=3D"https:=
//mail.google.com/mail/u/0/?ui=3D2&amp;ik=3D42095eb97c&amp;attid=3D0.3&amp;p=
ermmsgid=3Dmsg-f%3A1686071028223994457&amp;th=3D176620aabf5e6a59&amp;view=3D=
fimg&amp;sz=3Ds0-l75-ft&amp;attbid=3DANGjdJ8xs4gf2euYOzZ5hKUPrHKTVTR_KqY4HxB=
oNxBbxHc4A2Ub-BW3w73N5Id3H9HGxk6nbPcTv8wlkaP8zHz43ky_NIATrae8l8DCyScEe-YNYRg=
iGx7YtSlJQsA&amp;disp=3Demb&amp;realattid=3D567a03abbf259cca_0.3" style=3D"w=
idth:0.1319in;height:0.1319in" data-unique-identifier=3D""></span></a><span s=
tyle=3D"font-family:Arial,sans-serif;color:black">&nbsp;&nbsp;</span><a href=
=3D"https://twitter.com/Trustly"; style=3D"color:rgb(17,85,204)" target=3D"_b=
lank"><span style=3D"font-family:Arial,sans-serif;color:black"><img border=3D=
"0" width=3D"13" height=3D"13" src=3D"https://mail.google.com/mail/u/0/?ui=3D=
2&amp;ik=3D42095eb97c&amp;attid=3D0.4&amp;permmsgid=3Dmsg-f%3A16860710282239=
94457&amp;th=3D176620aabf5e6a59&amp;view=3Dfimg&amp;sz=3Ds0-l75-ft&amp;attbi=
d=3DANGjdJ8O4xFm9b2d1oUr4ZuXkRoi3ZijR3_JV5Jze72hnMn9nzEngZMlfzEuVecy9PGS2UQg=
kjYyA7nMYWFQfBIViM_xMwg3kUlbSslhmQulckXH2XAegXUQjaLt1Us&amp;disp=3Demb&amp;r=
ealattid=3D567a03abbf259cca_0.4" style=3D"width:0.1319in;height:0.1319in" da=
ta-unique-identifier=3D""></span></a><span style=3D"font-family:Arial,sans-s=
erif;color:black"></span></p><p class=3D"MsoNormal" style=3D"color:rgb(34,34=
,34)"><span style=3D"font-family:Arial,sans-serif;color:black"><a href=3D"ht=
tps://www.trustly.net/" target=3D"_blank"><img width=3D"96" height=3D"20" sr=
c=3D"https://ci3.googleusercontent.com/mail-sig/AIorK4zqkSGmwos-7333cPVw-bXb=
sjnhpHvcpewFFK1mj6AcrGcf6a4q_rYJMjnKS0rJmDNBiZiSpTk" data-unique-identifier=3D=
""></a></span></p></div></div></div></div>

<br>
<span style=3D"font-family:-apple-system,system-ui,&quot;Segoe UI&quot;,Robo=
to,Oxygen,Ubuntu,&quot;Fira Sans&quot;,&quot;Droid Sans&quot;,&quot;Helvetic=
a Neue&quot;,sans-serif;font-size:14px;letter-spacing:-0.07px;white-space:pr=
e-wrap;background-color:rgb(255,255,255);color:rgb(102,102,102)">Please read=
 our privacy policy </span><strong style=3D"color:rgb(0,82,204);font-family:=
-apple-system,system-ui,&quot;Segoe UI&quot;,Roboto,Oxygen,Ubuntu,&quot;Fira=
 Sans&quot;,&quot;Droid Sans&quot;,&quot;Helvetica Neue&quot;,sans-serif;fon=
t-size:14px;letter-spacing:-0.07px;white-space:pre-wrap;background-color:rgb=
(255,255,255)"><span style=3D"color:rgb(102,102,102)"><a href=3D"https://www=
.trustly.net/about-us/privacy-policy" title=3D"https://www.trustly.net/about=
-us/privacy-policy" style=3D"color:rgb(0,82,204);font-family:-apple-system,s=
ystem-ui,&quot;Segoe UI&quot;,Roboto,Oxygen,Ubuntu,&quot;Fira Sans&quot;,&qu=
ot;Droid Sans&quot;,&quot;Helvetica Neue&quot;,sans-serif;font-size:14px;let=
ter-spacing:-0.07px;white-space:pre-wrap;background-color:rgb(255,255,255)" t=
arget=3D"_blank">here</a> </span></strong><span style=3D"font-family:-apple-=
system,system-ui,&quot;Segoe UI&quot;,Roboto,Oxygen,Ubuntu,&quot;Fira Sans&q=
uot;,&quot;Droid Sans&quot;,&quot;Helvetica Neue&quot;,sans-serif;font-size:=
14px;letter-spacing:-0.07px;white-space:pre-wrap;background-color:rgb(255,25=
5,255);color:rgb(102,102,102)">on how we process your personal data in accor=
dance</span><span style=3D"color:rgb(23,43,77);font-family:-apple-system,sys=
tem-ui,&quot;Segoe UI&quot;,Roboto,Oxygen,Ubuntu,&quot;Fira Sans&quot;,&quot=
;Droid Sans&quot;,&quot;Helvetica Neue&quot;,sans-serif;font-size:14px;lette=
r-spacing:-0.07px;white-space:pre-wrap;background-color:rgb(255,255,255)"> <=
/span><span style=3D"font-family:-apple-system,system-ui,&quot;Segoe UI&quot=
;,Roboto,Oxygen,Ubuntu,&quot;Fira Sans&quot;,&quot;Droid Sans&quot;,&quot;He=
lvetica Neue&quot;,sans-serif;font-size:14px;letter-spacing:-0.07px;white-sp=
ace:pre-wrap;background-color:rgb(255,255,255);color:rgb(102,102,102)">with<=
/span><span style=3D"color:rgb(23,43,77);font-family:-apple-system,system-ui=
,&quot;Segoe UI&quot;,Roboto,Oxygen,Ubuntu,&quot;Fira Sans&quot;,&quot;Droid=
 Sans&quot;,&quot;Helvetica Neue&quot;,sans-serif;font-size:14px;letter-spac=
ing:-0.07px;white-space:pre-wrap;background-color:rgb(255,255,255)"> </span>=
<span style=3D"font-family:-apple-system,system-ui,&quot;Segoe UI&quot;,Robo=
to,Oxygen,Ubuntu,&quot;Fira Sans&quot;,&quot;Droid Sans&quot;,&quot;Helvetic=
a Neue&quot;,sans-serif;font-size:14px;letter-spacing:-0.07px;white-space:pr=
e-wrap;background-color:rgb(255,255,255);color:rgb(102,102,102)">the</span><=
span style=3D"color:rgb(23,43,77);font-family:-apple-system,system-ui,&quot;=
Segoe UI&quot;,Roboto,Oxygen,Ubuntu,&quot;Fira Sans&quot;,&quot;Droid Sans&q=
uot;,&quot;Helvetica Neue&quot;,sans-serif;font-size:14px;letter-spacing:-0.=
07px;white-space:pre-wrap;background-color:rgb(255,255,255)"> </span><span s=
tyle=3D"font-family:-apple-system,system-ui,&quot;Segoe UI&quot;,Roboto,Oxyg=
en,Ubuntu,&quot;Fira Sans&quot;,&quot;Droid Sans&quot;,&quot;Helvetica Neue&=
quot;,sans-serif;font-size:14px;letter-spacing:-0.07px;white-space:pre-wrap;=
background-color:rgb(255,255,255);color:rgb(102,102,102)">General Data Prote=
ction Regulation</span><span style=3D"color:rgb(23,43,77);font-family:-apple=
-system,system-ui,&quot;Segoe UI&quot;,Roboto,Oxygen,Ubuntu,&quot;Fira Sans&=
quot;,&quot;Droid Sans&quot;,&quot;Helvetica Neue&quot;,sans-serif;font-size=
:14px;letter-spacing:-0.07px;white-space:pre-wrap;background-color:rgb(255,2=
55,255)"> </span><span style=3D"font-family:-apple-system,system-ui,&quot;Se=
goe UI&quot;,Roboto,Oxygen,Ubuntu,&quot;Fira Sans&quot;,&quot;Droid Sans&quo=
t;,&quot;Helvetica Neue&quot;,sans-serif;font-size:14px;letter-spacing:-0.07=
px;white-space:pre-wrap;background-color:rgb(255,255,255);color:rgb(102,102,=
102)">(EU) 2016/679 (the =E2=80=9CGDPR=E2=80=9D) and other applicable data p=
rotection legislation.</span></div></blockquote></div></body></html>=

--Apple-Mail-7D7CBCD5-3F09-431D-B76F-84E7E08D828D--





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Another way to do audit in DML operations in PostgreSQL >= 14
  2024-02-22 19:14 Another way to do audit in DML operations in PostgreSQL >= 14 Lucio Chiessi <lucio.chiessi@trustly.com>
  2024-02-23 04:54 ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 S Hoffman <hoffman7043@gmail.com>
@ 2024-02-23 14:29   ` Bruce Momjian <bruce@momjian.us>
  2024-02-23 19:35     ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 S Hoffman <hoffman7043@gmail.com>
  2024-02-26 00:02     ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 Ron Johnson <ronljohnsonjr@gmail.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Bruce Momjian @ 2024-02-23 14:29 UTC (permalink / raw)
  To: S Hoffman <hoffman7043@gmail.com>; +Cc: Lucio Chiessi <lucio.chiessi@trustly.com>; PGSql Admin list <pgsql-admin@lists.postgresql.org>

On Thu, Feb 22, 2024 at 08:54:52PM -0800, S Hoffman wrote:
> Hi Lucio Chiessi,
> 
> Dealing with large volumes of data can be a challenging task. If you struggle
> with this issue, one possible solution is to consider using external logging
> services. Specifically, asynchronous logging services or external logging
> services that are designed to handle streaming data may be effective options to
> explore. Some popular external logging services in this regard include
> Elasticsearch and Spunk, known for their ability to handle large volumes of
> data. If you have any questions or want to explore other options, please let me
> know. 

Would remote syslog be an option too?

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Another way to do audit in DML operations in PostgreSQL >= 14
  2024-02-22 19:14 Another way to do audit in DML operations in PostgreSQL >= 14 Lucio Chiessi <lucio.chiessi@trustly.com>
  2024-02-23 04:54 ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 S Hoffman <hoffman7043@gmail.com>
  2024-02-23 14:29   ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 Bruce Momjian <bruce@momjian.us>
@ 2024-02-23 19:35     ` S Hoffman <hoffman7043@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: S Hoffman @ 2024-02-23 19:35 UTC (permalink / raw)
  To: Bruce Momjian <bruce@momjian.us>; +Cc: Lucio Chiessi <lucio.chiessi@trustly.com>; PGSql Admin list <pgsql-admin@lists.postgresql.org>

A remote syslog is a good option, too. The method depends on the specific needs, resources, and infrastructure. Remote Syslog can be easier to set up. External logging solutions scale better, offer more analytical features, and can allocate more resources to log management.

Try testing these approaches in a staging environment to assess their impact on performance and to ensure they meet compliance needs. That will be the best approach to determine the most beneficial solution.

Kind regards,
Sondra

Sent from my iPhone

> On Feb 23, 2024, at 6:29 AM, Bruce Momjian <bruce@momjian.us> wrote:
> 
> On Thu, Feb 22, 2024 at 08:54:52PM -0800, S Hoffman wrote:
>> Hi Lucio Chiessi,
>> 
>> Dealing with large volumes of data can be a challenging task. If you struggle
>> with this issue, one possible solution is to consider using external logging
>> services. Specifically, asynchronous logging services or external logging
>> services that are designed to handle streaming data may be effective options to
>> explore. Some popular external logging services in this regard include
>> Elasticsearch and Spunk, known for their ability to handle large volumes of
>> data. If you have any questions or want to explore other options, please let me
>> know.
> 
> Would remote syslog be an option too?
> 
> --
>  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
>  EDB                                      https://enterprisedb.com
> 
>  Only you can decide what is important to you.





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Another way to do audit in DML operations in PostgreSQL >= 14
  2024-02-22 19:14 Another way to do audit in DML operations in PostgreSQL >= 14 Lucio Chiessi <lucio.chiessi@trustly.com>
  2024-02-23 04:54 ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 S Hoffman <hoffman7043@gmail.com>
  2024-02-23 14:29   ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 Bruce Momjian <bruce@momjian.us>
@ 2024-02-26 00:02     ` Ron Johnson <ronljohnsonjr@gmail.com>
  2024-02-27 14:41       ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 Lucio Chiessi <lucio.chiessi@trustly.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Ron Johnson @ 2024-02-26 00:02 UTC (permalink / raw)
  To: Pgsql-admin <pgsql-admin@lists.postgresql.org>

On Fri, Feb 23, 2024 at 9:29 AM Bruce Momjian <bruce@momjian.us> wrote:

> On Thu, Feb 22, 2024 at 08:54:52PM -0800, S Hoffman wrote:
> > Hi Lucio Chiessi,
> >
> > Dealing with large volumes of data can be a challenging task. If you
> struggle
> > with this issue, one possible solution is to consider using external
> logging
> > services. Specifically, asynchronous logging services or external logging
> > services that are designed to handle streaming data may be effective
> options to
> > explore. Some popular external logging services in this regard include
> > Elasticsearch and Spunk, known for their ability to handle large volumes
> of
> > data. If you have any questions or want to explore other options, please
> let me
> > know.
>
> Would remote syslog be an option too?
>

rsyslog feeding Qradar is great, but a DBA still needs his own logs.  If
there's a way to spit only pgaudit logs to rsyslog, and leave everything
else go to stderr, I'd be a very happy camper.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Another way to do audit in DML operations in PostgreSQL >= 14
  2024-02-22 19:14 Another way to do audit in DML operations in PostgreSQL >= 14 Lucio Chiessi <lucio.chiessi@trustly.com>
  2024-02-23 04:54 ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 S Hoffman <hoffman7043@gmail.com>
  2024-02-23 14:29   ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 Bruce Momjian <bruce@momjian.us>
  2024-02-26 00:02     ` Re: Another way to do audit in DML operations in PostgreSQL >= 14 Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-02-27 14:41       ` Lucio Chiessi <lucio.chiessi@trustly.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Lucio Chiessi @ 2024-02-27 14:41 UTC (permalink / raw)
  To: PGSql Admin list <pgsql-admin@lists.postgresql.org>

Thanks to all!!

Yes Bruce.  You are right about the selects.  My mistake.
We need to audit the DQL, DML and DDL executed into the databases.
I considered using Syslog, but our PostgreSQL instances use AWS RDS and
Aurora Services only.  I think that is not possible in this instances type.
And I know that the PostgreSQL log files wasn't to be used for this and
looking for another way to have this audit done without problems.
Cheers!!

*Lucio Chiessi*

Senior Database Administrator

Trustly, Inc.

M: +55 27 996360276

<https://www.linkedin.com/company/trustly/;
<https://www.facebook.com/trustly;  <https://twitter.com/Trustly;

<https://www.linkedin.com/company/trustly/;
<https://www.facebook.com/trustly;   <https://twitter.com/Trustly;

<https://www.trustly.net/;


On Sun, Feb 25, 2024 at 9:03 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

> On Fri, Feb 23, 2024 at 9:29 AM Bruce Momjian <bruce@momjian.us> wrote:
>
>> On Thu, Feb 22, 2024 at 08:54:52PM -0800, S Hoffman wrote:
>> > Hi Lucio Chiessi,
>> >
>> > Dealing with large volumes of data can be a challenging task. If you
>> struggle
>> > with this issue, one possible solution is to consider using external
>> logging
>> > services. Specifically, asynchronous logging services or external
>> logging
>> > services that are designed to handle streaming data may be effective
>> options to
>> > explore. Some popular external logging services in this regard include
>> > Elasticsearch and Spunk, known for their ability to handle large
>> volumes of
>> > data. If you have any questions or want to explore other options,
>> please let me
>> > know.
>>
>> Would remote syslog be an option too?
>>
>
> rsyslog feeding Qradar is great, but a DBA still needs his own logs.  If
> there's a way to spit only pgaudit logs to rsyslog, and leave everything
> else go to stderr, I'd be a very happy camper.
>

-- 
Please read our privacy policy here 
<https://www.trustly.net/about-us/privacy-policy; on how we process your 
personal data in accordance with the General Data Protection Regulation 
(EU) 2016/679 (the “GDPR”) and other applicable data protection legislation.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

end of thread, other threads:[~2024-02-27 14:41 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-22 19:14 Another way to do audit in DML operations in PostgreSQL >= 14 Lucio Chiessi <lucio.chiessi@trustly.com>
2024-02-22 23:36 ` Bruce Momjian <bruce@momjian.us>
2024-02-23 04:54 ` S Hoffman <hoffman7043@gmail.com>
2024-02-23 14:29   ` Bruce Momjian <bruce@momjian.us>
2024-02-23 19:35     ` S Hoffman <hoffman7043@gmail.com>
2024-02-26 00:02     ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-27 14:41       ` Lucio Chiessi <lucio.chiessi@trustly.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox