Monitor postgresql

[Madison !]

I installed the wazuh manager all in one, then installed ubuntu. 

the wazuh agent is installed on the PostgreSQL server.

I did these steps https://documentation.wazuh.com/current/user-manual/manager/manual-database-output.html#manual-database-output  but i can't see logs from the PostgreSQL server. 

Where I can see them? 

maybe i have to do this https://documentation.wazuh.com/current/user-manual/ruleset/custom.html

[Alfonso Ruiz-Bravo]

Hello Madiartumarbayev,

The steps in the guide seem to be correct. Have you installed Wazuh Manager from Sources as specified?

As previously mentioned, the database output can be enabled when compiling Wazuh with the database type to use. On the sources installation guide, users must pre-compile the source code before running the install.sh script.

Execute the following command before step 3 from the installation guide:

All the configuration of the guide is done in the Wazuh manager. I say this because you may have configured some section in your Wazuh agent without realizing it, can it be?

Answering your question,  Wazuh has a set of rules and decoders for PostgreSQL, therefore, it should not be necessary for you to create new rules and decoders:

- Rules: https://github.com/wazuh/wazuh-ruleset/blob/master/rules/0300-postgresql_rules.xml

- Decoders: https://github.com/wazuh/wazuh-ruleset/blob/master/decoders/0225-postgresql_decoders.xml

If your configuration is correct and the decoders and rules are working properly you can find the generated PostgreSQL alerts in the /var/ossec/logs/alerts/alerts.json file in your Wazuh manager.

If you cannot see any alert, I suggest you enable the logall_json setting in your manager configuration (ossec.conf): https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/global.html#logall-json. This way the Wazuh manager will write in the file /var/ossec/logs/archives/archives/archives.json all the events to be analyzed, whether they generate an alert or not. Remember to restart the Wazuh manager service for the changes to take effect. Once your manager is logging all events, try generating events in your DB to see if they are added to the archives.json file. 

- If you find PostgreSQL alerts in your alerts.json file everything is working correctly.

- If you do not find PostgreSQL alerts in your alerts.json file but you do find PostgreSQL events in your archives.json file the integration with the DB is working fine, but the decoders and/or rules are not generating alerts with the parsed events. In this case, you would have to create new decoders or rules to suit your needs.

- If you do not find any PostgreSQL events in either alerts.json or archives.json it is likely that the integration with PostgreSQL is not correct.

As a reminder, logall_json logs everything that is going to be analyzed by the Wazuh manager, not just alerts, so your disk usage will increase considerably if you leave it enabled for too long. If you do not want this to happen, remember to disable it after testing. 

I hope I have been helpful.

Best regards,

Alfonso Ruiz-Bravo

[Madison !]

thanks before installing agent on ubuntu server i have to install pgaudit on the postgres?

Using all the tips i will reinstall the wazuh manager.
Does it mean that after all in one installation i have to this command apt-get install python gcc make libc6-dev curl policycoreutils automake autoconf libtool ?

before step 3 means that (When the script asks what kind of installation you want, type manager to install the Wazuh manager:

1- What kind of installation do you want (manager, agent, local, hybrid or help)? manager)?

вторник, 8 июня 2021 г. в 16:48:17 UTC+6, alfonso.r...@wazuh.com:

[Alfonso Ruiz-Bravo]

Hello Madison,

I think we are dealing with different topics, the output alerts into DB part and the monitoring DB part:

- Output alerts into DB

What you would have to do would be the following.

Follow this installation guide to install the Wazuh manager:

https://documentation.wazuh.com/current/installation-guide/more-installation-alternatives/wazuh-from-sources/wazuh-server/index.html#wazuh-server-source-installation

Complete the installation using the guide, with this exception:

Execute the following command before step 3 from the installation guide:

# cd wazuh-*/src
# make deps && make TARGET=server DATABASE=<mysql/pgsql>

Once you have finished installing the Wazuh manager, you can continue with the PostgreSQL guide from this point:

https://documentation.wazuh.com/current/user-manual/manager/manual-database-output.html#database-configuration

When you finish the DB outputs configuration your Wazuh manager will be ready to send your alerts to your DB.

- Monitoring DB

Now, if what you want is to monitor the actions that happen in your DB, what you should do is to have your Wazuh agent collect the logs of that DB. For example, you can use a localfile to collect PostgreSQL logs and send them for analysis.

Localfile: https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/localfile.html

Suppose PostgreSQL writes its logs to this file in your server: /var/log/postgresql/postgresql.log

You can add this section to your agent configuration (ossec.conf):

<localfile>
    <log_format>postgresql_log</log_format>
    <location>/var/logs/postgresql/postgresql.log</location>
</localfile>

This way the Wazuh agent will collect the events that PostgreSQL writes in its log and send them to the manager for analysis. The manager will analyze it with its decoders and rules and generate alerts if necessary.

I hope I have explained myself correctly by separating the two use cases that had confused me. 

Please do not hesitate to contact us for further information. Thank you very much for your patience.

Best regards,

Alfonso Ruiz-Bravo Cloud computing engineer The Open Source Security Platform

--
You received this message because you are subscribed to a topic in the Google Groups "Wazuh mailing list" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/wazuh/_k1lXn7fW8Y/unsubscribe.
To unsubscribe from this group and all its topics, send an email to wazuh+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/1812ec27-3680-41b4-a069-c3dd00005182n%40googlegroups.com.

[Madison !]

Sorry Alfonso

but i can't understand. when you say step 3 you mean that ( When the script asks what kind of installation you want, type manager to install the Wazuh manager:

1- What kind of installation do you want (manager, agent, local, hybrid or help)? manager)

i did these steps:

1) curl -so ~/unattended-installation.sh https://raw.githubusercontent.com/wazuh/wazuh-documentation/4.1/resources/open-distro/unattended-installation/unattended-installation.sh && bash ~/unattended-installation.sh

2)yum install make cmake gcc gcc-c++ python3 python3-policycoreutils automake autoconf libtool

3)# yum install epel-release yum-utils -y # yum-builddep python34 -y

4)curl -Ls https://github.com/wazuh/wazuh/archive/v4.1.5.tar.gz | tar zx5

before running installation script i run https://documentation.wazuh.com/current/user-manual/manager/manual-database-output.html#manual-database-output

5) yum install postgresql-devel

6) cd wazuh-*/src # make deps && make TARGET=server DATABASE=pgsql but after typing this command i see ERROR make: *** [external/cpython.tar.gz] Error 56

вторник, 8 июня 2021 г. в 18:47:22 UTC+6, alfonso.r...@wazuh.com:

[Alfonso Ruiz-Bravo]

Hello Madison,

I think the correct steps to make the Wazuh manager send the alerts to the PostgreSQL DB would be the following:

Installing the Wazuh manager

Prerequisites 

- # yum install postgresql-devel

Installation

1 - Download and extract the latest version:

 # curl -Ls https://github.com/wazuh/wazuh/archive/v4.1.5.tar.gz | tar zx

2 - Run the install.sh script. This will display a wizard to guide you through the installation process using the Wazuh sources. If you have previously compiled for another platform, you must clean the build using the Makefile in src:

# cd wazuh-*

# make -C src clean

# make -C src clean-deps

# cd wazuh-*/src

# make deps && make TARGET=server DATABASE=pgsql

# ./install.sh

3 - When the script asks what kind of installation you want, type manager to install the Wazuh manager:

1- What kind of installation do you want (manager, agent, local, hybrid or help)? manager

4 - The installer asks if you want to start Wazuh at the end of the installation. If you choosed not to, you can start it later with:

For Systemd:

# systemctl restart wazuh-manager

For SysV Init:

# service wazuh-manager restart

5 - Database configuration. For PostgreSQL:

# sudo -u postgres createuser -P PostgreSQLadmin

# sudo -u postgres createdb -O PostgreSQLadmin Alerts_DB

# psql -U PostgreSQLadmin -d Alerts_DB -f src/os_dbd/postgresql.schema

6 - Wazuh configuration . For PostgreSQL:

<database_output>
  <hostname>192.168.1.122</hostname>
  <username>PostgreSQLadmin</username>
  <password>secret1234</password>
  <database>Alerts_DB</database>
  <type>postgresql</type>
</database_output>

7 - Last steps. Restart Wazuh manager:

For Systemd:

# systemctl restart wazuh-manager

For SysV Init:

# service wazuh-manager restart

By following these steps, you should be able to have the Wazuh manager ready to send alerts to your PostgreSQL DB.

I hope it has been helpful. Please do not hesitate to contact us if you have any questions.

Best regards,

Alfonso Ruiz-Bravo Cloud computing engineer The Open Source Security Platform

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/0a15061a-7512-461d-8d77-b62fdcfa137bn%40googlegroups.com.

[Madison !]

Good Morning Alfonso

i did all the steps. I want to see all the alerts from postgresql db in wazuh. How can i send alerts to monitor them in wazuh manager? 

четверг, 10 июня 2021 г. в 13:05:15 UTC+6, alfonso.r...@wazuh.com:

[Alfonso Ruiz-Bravo]

Hello Madison!

I think that this is the best option:

- Monitoring DB

If what you want is to monitor the actions that happen in your DB, what you should do is to have your Wazuh agent (in your DB server) collect the logs of that DB. For example, you can use a localfile to collect PostgreSQL logs and send them for analysis.

Localfile: https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/localfile.html

Suppose PostgreSQL writes its logs to this file in your server: /var/log/postgresql/postgresql.log

You can add this section to your agent configuration (ossec.conf):

<localfile>
    <log_format>postgresql_log</log_format>
    <location>/var/logs/postgresql/postgresql.log</location>
</localfile>

This way the Wazuh agent will collect the events that PostgreSQL writes in its log and send them to the manager for analysis. The manager will analyze it with its decoders and rules and generate alerts if necessary.

Please do not hesitate to contact us for further information. Thank you very much for your patience.

Best regards,

Alfonso Ruiz-Bravo Cloud computing engineer The Open Source Security Platform

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/9bff5f59-339b-427a-aa27-1ba75de3b1afn%40googlegroups.com.

[Madison !]

Hello Alfonso. Thank you for your assistance 

i configured as you tolde me. My logs are generated in /var/log/postgresql/postgresql-12-main.log. Pg audit is installed. 

 I add this

       <localfile> 

<log_format>postgresql_log</log_format>

<location>/var/logs/postgresql/ postgresql-12-main.log 

      </location> </localfile>

In the global configuration the infomariton was updated. Why i still can not see logs from postgresql.? 

пятница, 11 июня 2021 г. в 12:04:37 UTC+6, alfonso.r...@wazuh.com:

[Alfonso Ruiz-Bravo]

Hello Madison,

It seems that PostgreSQL rotates its log files, at least I get that impression seeing that its name is numbered. Therefore I propose the following configuration:

<localfile>
    <log_format>postgresql_log</log_format>
    <location>/var/logs/postgresql/postgresql-*-main.log</location>
</localfile>

This way you will use one wildcard for all PostgreSQL files without having to modify the configuration. Do not forget to restart the agent service for the changes to take effect.

Now, to check if the events collected from PostgreSQL by the Wazuh agent are arriving to the Wazuh manager, I propose the following:

 I suggest you enable the logall_json setting in your Wazuh manager configuration (ossec.conf): https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/global.html#logall-json. This way the Wazuh manager will write in the file /var/ossec/logs/archives/archives/archives.json all the events to be analyzed, whether they generate an alert or not. Remember to restart the Wazuh manager service for the changes to take effect. Once your manager is logging all events, try generating events in your DB to see if they are added to the archives.json file. 

- If you find PostgreSQL events in your archives.json file the integration with the DB is working fine, but the decoders and/or rules are not generating alerts with the parsed events. In this case, you would have to create new decoders or rules to suit your needs.

- If you do not find any PostgreSQL events in archives.json it is likely that the localfile with PostgreSQL is not correct or that the Wazuh agent is not reaching the Wazuh manager (you can easily check this by looking for alerts that have the agent as the source).

With this check we will be able to see if the PostgreSWL events reach the Wazuh manager. Knowing this, we can see how to continue with the problem.

As a reminder, logall_json logs everything that is going to be analyzed by the Wazuh manager, not just alerts, so your disk usage will increase considerably if you leave it enabled for too long. If you do not want this to happen, remember to disable it after testing. 

Best regards,

Alfonso Ruiz-Bravo Cloud computing engineer The Open Source Security Platform

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/ee367bbd-9990-44f8-a39a-60a286505fb3n%40googlegroups.com.

[Madison !]

Probably  i should configure here? # These are relevant when logging to syslog:

#syslog_facility = 'LOCAL0'

#syslog_ident = 'postgres'

#syslog_sequence_numbers = on

#syslog_split_messages = on

# This is only relevant when logging to eventlog (win32):

# (change requires restart)

#event_source = 'PostgreSQL'

?

понедельник, 14 июня 2021 г. в 14:27:52 UTC+6, alfonso.r...@wazuh.com:

[Madison !]

there is log from archives_json.

понедельник, 14 июня 2021 г. в 16:16:54 UTC+6, Madison !:

[Alfonso Ruiz-Bravo]

Hi Maidosn,

Perfect, as we can see, PostgreSQL events are arriving to the Wazuh manager. If you don't see PostgreSQL alerts it can be due to two things:

The first one. The triggered rules have a low alert level and because of their configuration they are not logged:

- https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/alerts.html#log-alert-level

The second. The decoders and rules are not working properly for those logs. If this is the case, you should modify or create new decoders and rules. As I see it, there may be a problem related to the decoders: 

- https://groups.google.com/g/wazuh/c/wZXj7Y8f2b8

Regards,

Alfonso Ruiz-Bravo Cloud computing engineer The Open Source Security Platform

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/f8ba78e0-4dde-4472-8475-2176b11d3af2n%40googlegroups.com.

[Madison !]

Alfoso can i ask a question? 

For what we created a database Alerts_DB and username PostgreSQLadmin? in the pg audit config i should specify Alerts_DB to collect events from the other databases? 

The first one. The triggered rules have a low alert level and because of their configuration they are not logged --- where is stored  alerts.log and/or alerts.json.

понедельник, 14 июня 2021 г. в 16:32:20 UTC+6, alfonso.r...@wazuh.com:

[Madison !]

The second. The decoders and rules are not working properly for those logs. If this is the case, you should modify or create new decoders and rules. As I see it, there may be a problem related to the decoders: 

- https://groups.google.com/g/wazuh/c/wZXj7Y8f2b8

I should add this      to local_decoder.xml local_rules.xml ??

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

понедельник, 14 июня 2021 г. в 16:56:54 UTC+6, Madison !:

[Alfonso Ruiz-Bravo]

Hello Madison!

For what we created a database Alerts_DB and username PostgreSQLadmin? in the pg audit config i should specify Alerts_DB to collect events from the other databases?

These steps are necessary to configure the database server and integrate it with the Wazuh manager. The purpose of this, as we have seen previously, is that the alerts generated by the Wazuh manager are sent to its DB named Alerts_DB.

The first one. The triggered rules have a low alert level and because of their configuration they are not logged --- where is stored  alerts.log and/or alerts.json.

Great, in this case, if you want to log alerts for these events you can either lower the minimum level of alerts to be recorded or overwrite these alerts by increasing their level.

Alert level setting

- https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/alerts.html#log-alert-level

Overwrite rules:

- https://documentation.wazuh.com/current/learning-wazuh/replace-stock-rule.html

Regards

Alfonso Ruiz-Bravo Cloud computing engineer The Open Source Security Platform

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/1594eb3d-82e8-47ce-a8c4-593b7747c117n%40googlegroups.com.

[Alfonso Ruiz-Bravo]

I should add this to local_decoder.xml local_rules.xml ??

If you have experienced this error, yes. Do it by following these steps:

https://documentation.wazuh.com/current/user-manual/ruleset/custom.html#changing-an-existing-decoder

Regards,

Alfonso Ruiz-Bravo Cloud computing engineer The Open Source Security Platform

[Madison !]

already have this decoder inside /var/ossec/ruleset/decoders/ 0225-postgresql_decoders.xml

[root-wazuh archives]# vi /var/ossec/ruleset/decoders/0225-postgresql_decoders.xml

<!--

  -  PostgreSQL decoders

  -  Author: Daniel Cid.

  -  Updated by Wazuh, Inc.

  -  Copyright (C) 2015-2020, Wazuh Inc.

  -  Copyright (C) 2009 Trend Micro Inc.

  -  This program is a free software; you can redistribute it and/or modify it under the terms of GPLv2.

-->

<!--

  - Examples:

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

  - [2007-08-31 18:37:15.525 ADT] 192.168.2.99: ERROR:  relation "alert2" does not exist

  -->

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

~

~

понедельник, 14 июня 2021 г. в 17:21:21 UTC+6, alfonso.r...@wazuh.com:

[Madison !]

Alfonso could we make a remote session with you by zoom? i will show you ?

понедельник, 14 июня 2021 г. в 17:31:26 UTC+6, Madison !:

[Alfonso Ruiz-Bravo]

Hi Madison,

It is not possible for me to set up a meeting right now, sorry.

You have this decoder:

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

 

This decoder seems to be failing as we have seen in another community thread. To make it work you must replace it with this one: 

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

The difference lies in the prematch:

Wrong:  <prematch>^[\d\d\d\d-\d\d-\d\d \S+ \w+] </prematch>

Ok:  <prematch>^\d\d\d\d-\d\d-\d\d \S+ \w+ </prematch>

To correctly change a decoder, you must follow these steps:

https://documentation.wazuh.com/current/user-manual/ruleset/custom.html#changing-an-existing-decoder

Regards,

Alfonso Ruiz-Bravo Cloud computing engineer The Open Source Security Platform

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/1fbe4f81-0778-4b84-9d42-f722ee881a44n%40googlegroups.com.

[Ibtihel Dhifallah]

Hello Alfoso!

i have this log format in my log file for postgresql : example : 2022-03-22 16:07:51.623 +03 [3831] user@postgres FATAL:  role "user" does not exist

the decoder postgresql_log does'nt match my log , could you help me please ?

Regards