Postgres log visualization on wazuh dashboard

[Juan Ferdinan]

Hallo Everyone

I've managed to do postgresql monitoring with wazuh and the logs have been successfully retrieved by wazuh. Now I want to display the log on the wazuh dashboard, I have followed the method in the following link https://github.com/wazuh/wazuh/tree/master/ruleset how to set the decoder and rules for postgresql, but the log has not been successful in the dashboard, I've tried filter by decoder name (postgresql_log) but nothing. Please help how to solve this.

[Lucio Donda]

Hi Juan,
First of all thanks for using wazuh!
Have you checked our rule and decoder test tool?
I'm guessing that you see those logs on the manager folder but are you also seeing alerts generated by those logs?
Once you filtered out any sensitive information you could share the logs and alerts (if so) and their output when you use that test tool I shared earlier, and we could give you a hand debugging them.
Have a great day!

[Juan Ferdinan]

Hi Lucio

here's the log that I checked using the rule and decoder test tool

/var/ossec/logs/archives/2022/Sep# /var/ossec/bin/wazuh-logtest
Starting wazuh-logtest v4.3.7
Type one log per line

{"timestamp":"2022-09-07T11:16:41.549+0700","agent":{"id":"015","name":"hostname XXX","ip":"10.10.XX.XX"},"manager":{"name":"wazuh"},"id":"1662524201.17090869","full_log":"Sep  7 11:16:40 XXX postgres[1828]: [10-1] 2022-09-07 11:16:40.237 WIB [1828] postgres@DB_Name LOG:  statement: select * from edc_transaction limit 10 ;","predecoder":{"program_name":"postgres","timestamp":"Sep  7 11:16:40","hostname":"XXX"},"decoder":{},"location":"/var/log/syslog"}

**Phase 1: Completed pre-decoding.
    full event: '{"timestamp":"2022-09-07T11:16:41.549+0700","agent":{"id":"015","name":"hostname XXX","ip":"10.10.XX.XX""},"manager":{"name":"wazuh"},"id":"1662524201.17090869","full_log":"Sep  7 11:16:40 XXX postgres[1828]: [10-1] 2022-09-07 11:16:40.237 WIB [1828] postgres@DB_Name LOG:  statement: select * from edc_transaction limit 10 ;","predecoder":{"program_name":"postgres","timestamp":"Sep  7 11:16:40","hostname":"XXX"},"decoder":{},"location":"/var/log/syslog"}'

**Phase 2: Completed decoding.
    name: 'json'
    agent.id: '015'
    agent.ip: '10.10.75.23'
    agent.name: 'hostname XXX'
    full_log: 'Sep  7 11:16:40 XXX postgres[1828]: [10-1] 2022-09-07 11:16:40.237 WIB [1828] postgres@DB_Name LOG:  statement: select * from edc_transaction limit 10 ;'
    id: '1662524201.17090869'
    location: '/var/log/syslog'
    manager.name: 'wazuh'
    predecoder.hostname: 'XXX'
    predecoder.program_name: 'postgres'
    predecoder.timestamp: 'Sep  7 11:16:40'
    timestamp: '2022-09-07T11:16:41.549+0700'

[Lucio Donda]

Hi Juan, 
As you may have seen in the logtest, there's no Rule triggered by those logs so if no alert is generated then it won't be seen in the dashboard. 
Here's a little troubleshooting guide when after the alert is generated then it should be shown in the dashboard.

This is how it should look when a rule gets triggered  (More info here) :

    Type one log per line

    Oct 15 21:07:00 linux-agent sshd[29205]: Invalid user blimey from 18.18.18.18 port 48928

    **Phase 1: Completed pre-decoding.
            full event: 'Oct 15 21:07:00 linux-agent sshd[29205]: Invalid user blimey from 18.18.18.18 port 48928'
            timestamp: 'Oct 15 21:07:00'
            hostname: 'linux-agent'
            program_name: 'sshd'

    **Phase 2: Completed decoding.
            name: 'sshd'
            parent: 'sshd'
            srcip: '18.18.18.18'
            srcport: '48928'
            srcuser: 'blimey'

    **Phase 3: Completed filtering (rules).
            id: '5710'
            level: '5'
            description: 'sshd: Attempt to login using a non-existent user'
            groups: '['syslog', 'sshd', 'invalid_login', 'authentication_failed']'
            firedtimes: '1'
            gdpr: '['IV_35.7.d', 'IV_32.2']'
            gpg13: '['7.1']'
            hipaa: '['164.312.b']'
            mail: 'False'
            mitre.id: '['T1110']'
            mitre.tactic: '['Credential Access']'
            mitre.technique: '['Brute Force']'
            nist_800_53: '['AU.14', 'AC.7', 'AU.6']'
            pci_dss: '['10.2.4', '10.2.5', '10.6.1']'
            tsc: '['CC6.1', 'CC6.8', 'CC7.2', 'CC7.3']'
    **Alert to be generated.

The rules for PostgreSQL are defined here, but as with almost everything, in wazuh you could modify them at your own need.

Does that answer you question, where are you searching your logs in /var/ossec/logs/ossec.log or in the dashboard (in the browser Wazuh/management/Logs) ?

[Juan Ferdinan]

Hi Lucio

I'm sorry I just responded to your answer, I hope you still want to help me. I tried the example log given at https://github.com/wazuh/wazuh/blob/4.3/ruleset/decoders/0225-postgresql_decoders.xml and it looks like the rule that I made is running well, here are the results

# /var/ossec/bin/wazuh-logtest
Starting wazuh-logtest v4.3.8

Type one log per line

[2007-08-31 18:37:09.454 ADT] 192.168.2.99: LOG:  connection authorized: user=ossec_user database=ossecdb

**Phase 1: Completed pre-decoding.
    full event: '[2007-08-31 18:37:09.454 ADT] 192.168.2.99: LOG:  connection authorized: user=ossec_user database=ossecdb'

**Phase 2: Completed decoding.
    name: 'postgresql_log'
    status: 'LOG'

**Phase 3: Completed filtering (rules).

    id: '50511'
    level: '3'
    description: 'PostgreSQL: Database authentication success.'
    groups: '['postgresql_log', 'authentication_success']'
    firedtimes: '1'
    gdpr: '['IV_32.2']'
    gpg13: '['7.1', '7.2']'
    hipaa: '['164.312.b']'
    mail: 'False'
    mitre.id: '['T1078']'
    mitre.tactic: '['Defense Evasion', 'Persistence', 'Privilege Escalation', 'Initial Access']'
    mitre.technique: '['Valid Accounts']'
    nist_800_53: '['AU.14', 'AC.7']'
    pci_dss: '['10.2.5']'
    tsc: '['CC6.8', 'CC7.2', 'CC7.3']'

**Alert to be generated.

maybe there is something wrong with the sample log generated by my postgresql?

[Lucio Donda]

That's ok, let me retake this issue,
Can you share with us some logs from postgreSql (remember always to look and delete or modify any sensitive information)?
comparing these logs with the one you shared, are noticing any difference? PostgreSQL has various types of logs, csv and json format and also depending on the version itself.
Can you tell me additional information about that? 

[Lucio Donda]

Juan,
Sorry for the previous mail, I've reread the entire thread again and find that your original event could be:

Sep 7 11:16:40 XXX postgres[1828]: [10-1] 2022-09-07 11:16:40.237 WIB [1828] postgres@DB_Name LOG:  statement: select * from edc_transaction limit 10 ;

Am I right?
Comparing this to the one we used as an example the only difference is

[10-1] 2022-09-07 11:16:40.237 WIB [1828] postgres@DB_Name

assuming that LOG: statement ... -> are related to Invalid user blimey from 18.18.18.18 port 48928 as a string message.
Can you add or modify this difference to your decoder? what happens if you try logtest with the line I paste first?
Let me know how that goes or share with us your decoder.
Have a great day!

[Juan Ferdinan]

Hi Lucio

yes you're right

Sorry, I don't understand how to modify the decoder. The contents of the decoder that I am currently making is based on the following link https://github.com/wazuh/wazuh/blob/master/ruleset/decoders/0225-postgresql_decoders.xml. Is it possible you can help me with this? By the way I tried to run the event log that you gave in logtest wazuh and here are the results.

# /var/ossec/bin/wazuh-logtest
Starting wazuh-logtest v4.3.8
Type one log per line

Sep 7 11:16:40 XXX postgres[1828]: [10-1] 2022-09-07 11:16:40.237 WIB [1828] postgres@DB_Name LOG:  statement: select * from edc_transaction limit 10 ;

**Phase 1: Completed pre-decoding.
    full event: 'Sep 7 11:16:40 XXX postgres[1828]: [10-1] 2022-09-07 11:16:40.237 WIB [1828] postgres@DB_Name LOG:  statement: select * from edc_transaction limit 10 ;'

**Phase 2: Completed decoding.
    No decoder matched.

[10-1] 2022-09-07 11:16:40.237 WIB [1828] postgres@DB_Name

**Phase 1: Completed pre-decoding.
    full event: '[10-1] 2022-09-07 11:16:40.237 WIB [1828] postgres@DB_Name'

**Phase 2: Completed decoding.
    No decoder matched.

[Lucio Donda]

Juan,
Let me wrap up all this, have you created a new decoder or only a new rule? If you have (any of them), please share them with us so we can see which part of that decoder or rule does not match.
Are you familiar with regular expressions? if not then based on that decoder I'll help you understanding what is trying to match and how we could modify it to make it work. 

[Juan Ferdinan]

Hi Lucio

I've created a new rule and decoder based on the source from this link https://github.com/wazuh/wazuh/blob/master/ruleset/decoders/0225-postgresql_decoders.xml https://github.com/wazuh/wazuh/blob/master/ruleset/rules/0300-postgresql_rules.xml, I didn't make any changes to the contents of the rule and decoder that I got, maybe you can see it at the link. For regular expressions, I am not familiar with it and have no knowledge at all.

Thanks & Regards

Juan

[Lucio Donda]

Juan, sorry for the late response.

First of all, do recheck this documentation tutorial IMHO it's very simple to follow.
In order for you to see any event on the dashboard you need to match that event with a decoder and a rule, that will trigger an alert that will be received by the wazuh-indexer and you will notice on the dashboard.
You already have the log event that you need, after that, you will have to change or set a decoder that matches that event, that's not happening.
here I have used a regex to match that event

/^\w{3} \d+ \d+:\d+:\d+ \w* postgres\[\d*\]: \[\d*\-\d*] \d\d\d\d-\d\d-\d\d \d+:\d+:\d+.\d+ \S+ \[\d*\] \S+ \S+: (.*);/gm

using regex101.com
and in the XML:

<decoder name="postgresql_log_custom">
  <regex type="pcre2" offset="after_prematch">^\w{3} \d+ \d+:\d+:\d+ \w* postgres\[\d*\]: \[\d*\-\d*] \d\d\d\d-\d\d-\d\d \d+:\d+:\d+.\d+ \S+ \[\d*\] \S+ \S+: (.*);</regex>
  <order>log_message</order>
</decoder>

The last part between parenthesis is the one that will be saved and that's another thing you have to decide, what part of the event has important information for your alert.

Once you have defined that, the rule based on that decoder must be created.

With all that you may use the log_test named on the other mails, if that turns out ok then that alert will be seen on the dashboard.
Do check all the above with various example logs till you reach a complete decoder. 

Hope this helps!