Обсуждение: Another way to do audit in DML operations in PostgreSQL >= 14

Поиск
Список
Период
Сортировка

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

От
Lucio Chiessi
Дата:
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

  

    


Please read our privacy policy here 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.

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

От
Bruce Momjian
Дата:
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.

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

От
Bruce Momjian
Дата:
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.

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

От
S Hoffman
Дата:
A remote syslog is a good option, too. The method depends on the specific needs, resources, and infrastructure. Remote
Syslogcan be easier to set up. External logging solutions scale better, offer more analytical features, and can
allocatemore resources to log management. 

Try testing these approaches in a staging environment to assess their impact on performance and to ensure they meet
complianceneeds. 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.

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

От
Ron Johnson
Дата:
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.

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

От
Lucio Chiessi
Дата:
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

  

    



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 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.