PostgreSQL Monitoring Issue

[Yousef Tarek]

Hello All,
I installed wazuh v4.1 all in one deployment successfully on my CentOs7 machine using the below documentation:
https://documentation.wazuh.com/current/installation-guide/open-distro/all-in-one-deployment/unattended-installation.html

And I am trying to monitor my PostgreSQL database logs using the below rule file located in /var/ossec/ruleset/rules/0300-postgresql_rules.xmlSo, I added the below to the agent.conf file located in the database group shared directory (/var/ossec/etc/shared/Postgresql_Group/agent.conf):<agent_config>
<!-- Shared agent configuration here -->
<localfile>
    <log_format>postgresql_log</log_format>
    <location>/var/lib/pgsql/data/log/*.log</location>
  </localfile>
</agent_config>

and my database logs are looking like this (attached)

and my problem is, these logs are never showing up on wazuh or kibana

and when I tried to change the <log_format> to Syslog instead of postgresql_log, I was able to see the logs on wazuh and kibana but in syslog format matching the 2501 rule.id (Syslog: User authentication failure.) but here is the thing, I want my logs to match the below rule instead (50512 rule.id - PostgreSQL: Database authentication failure)so, please assist me if there is any misconfiguration or syntax error.

Thanks in advance.
Best Regards

[elw...@wazuh.com]

Hello Yousseif,

Can you please share me some database logs lines omitting sensitive information to help you with triggering the requested rule/alert ?

Regards,
Wali

[Yousef Tarek]

Hello Elwali,

Let's start from the beginning when I try to connect to the DB with a wrong password, here is what I can found on my log file for one authentication failed attempt

2021-04-07 16:48:20.991 EET [8329] FATAL:  password authentication failed for user "testing_user"
2021-04-07 16:48:20.991 EET [8329] DETAIL:  Password does not match for user "testing_user".
        Connection matched pg_hba.conf line 90: "host   testing_db      testing_user           0.0.0.0/0                md5"

There are two types of logs here, one line log and two lines log each log statement starting with the date as you can see.
So, I tried to paste my two types of logs into /ossec/etc/wazuh-logtest to see what is going to happen, and here is what I've got:

-one-line log (attachement1)

-two lines log (attachement2)

Thanks.

[Javier Bejar]

Hello Yousseif,

The problem is with postgre decoder, it is made for a previous version, so it's not being correctly decoded and postgre rules are not being evaluated.

To solve this please add this decoder to your custom decoders, as explained here:

<decoder name="postgresql_log">
<prematch>^\d\d\d\d-\d\d-\d\d \S+ \w+ </prematch>
<regex offset="after_prematch">^\S+ (\w+): </regex>
<order>status</order>
</decoder>

Have a nice day.

[Yousef Tarek]

Thanks a lot, Javier for your support and sorry for the late reply, I added a custom decoder in /var/ossec/etc/decoders/local_decoders.xml and now logtest binary is able to identify the log criteria (attachment1) but I can not see the logs pushed to alerts.log file nor the wazuh portal even though I specified the log files to be monitored in agent.conf file using <localfile> tag (attachment2) so please advise if there are any missing steps or configurations I should make.

Thanks in Advance.

Best Regards,

Yousseif Tarik