how to monitor postgresql database

[Madison !]

Hello team

Could help me. I installed wazuh manager and added two servers 1) windows and 2) ubuntu

and i want to add postgresql server.

i did not understand what should i type here in TARGET : cd wazuh-*/src # make deps && make TARGET=server DATABASE=<mysql/pgsql> 

also i should add posgresql rules and decoders in manager configuration? 

[Madison !]

pls provide some information

вторник, 30 марта 2021 г. в 16:44:22 UTC+6, Madison !:

[Juan Ricci]

Hello, 

Thanks for contacting us! Please let me clarify some scenarios here:

1) In case you are installing Wazuh Manager from sources (https://documentation.wazuh.com/current/installation-guide/more-installation-alternatives/wazuh-from-sources/wazuh-server/index.html) and following the steps for configuring Wazuh to output the alerts into a database (https://documentation.wazuh.com/current/user-manual/manager/manual-database-output.html#installation), you need to execute the following command before step 3 from the installation guide:

make deps && make TARGET=server DATABASE=<mysql/pgsql>

literally as it is.

The TARGET=server option specifies that you are installing a Wazuh Manager from the sources (you can check other TARGET options on the following link: https://documentation.wazuh.com/current/development/makefile.html#makefile-reference).

2) In case you want to monitor a PostgreSQL server and get logs from it, you need to install an Agent on the mentioned server first, and connect it to the Manager (https://documentation.wazuh.com/current/user-manual/registering/index.html). As the Wazuh ruleset includes PostgreSQL rules and decoders by default (/var/ossec/ruleset/decoders/0225-postgresql_decoders.xml and /var/ossec/ruleset/rules/0300-postgresql_rules.xml), you will be able to get events from the mentioned server immediately.

I hope this information helps. Please let me know if you have further questions.

Regards.

Juan

[Madison !]

Juan thank you for reply.

In the directory /var/ossec/ruleset i have only these files. There is not decoders. Should i 

conf.d  environment  pg_ctl.conf  pg_hba.conf  pg_ident.conf  postgresql.conf  start.conf

Sorry but i have never tried to do it. it's my first time doing that.

Database configuration¶

i typed this command: # 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

In my test database appeared the database Alerts_DB.

in that database, i should send security logs?

вторник, 30 марта 2021 г. в 21:54:27 UTC+6, juan....@wazuh.com:

[Madison !]

Please could give me some advice

четверг, 1 апреля 2021 г. в 17:01:37 UTC+6, Madison !:

[Juan Ricci]

Hello,  my apologies for the delay here.

Unfortunately, I could not understand if you are trying to monitor a PostgreSQL database, or you want to store the alerts generated on the Wazuh Manager in a PostgreSQL database.

When you monitor a database, the Agent installed on the same host that the database engine sends events to the Wazuh Manager which creates alerts related to these events.

Security alerts created by the Wazuh Manager can also be stored in a database (all generated alerts, not only the ones generated by a PostgreSQL engine).

Could you please clarify which implementation you need to do?

Thank you, best regards.

Juan

[Madison !]

Hello Juan

i am testing wazuh. i have one server which is on Centos 7.8

on the centos server i entered: yum install postgresql-devel

on the wazuh-server i did: # cd wazuh-*/src # make deps && make TARGET=server DATABASE=<mysql/pgsql>

on the centos-server i did this:

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          but i didn't find this file  src/os_dbd/postgresql.schema 

src/os_dbd/postgresql.schema i found this file on wazuh-server 

понедельник, 12 апреля 2021 г. в 20:21:37 UTC+6, juan....@wazuh.com:

[Juan Ricci]

Hello, I hope you are doing well!

If I understood properly, you have one server for Wazuh Manager, and another one for PostgreSQL engine. In such case, you need to copy the file you found on your Wazuh server to the PostgreSQL server and execute the following command setting the proper postresql.schema file path. For example:

psql -U PostgreSQLadmin -d Alerts_DB -f path/where/the/file/is/stored/postgresql.schema

Then add the proper settings for sending alerts to the PostgreSQL server on the /var/ossec/etc/ossec.conf configuration file on the Wazuh server:

<database_output> 

  <hostname>192.168.1.122</hostname> 

  <username>PostgreSQLadmin</username> 

  <password>secret1234</password> 

  <database>Alerts_DB</database> 

  <type>postgresql</type> 

</database_output>

There you must set the PostgreSQL server IP address, and the username and password you set up on PostgreSQL.

Finally, restart your Wazuh Manager by executing: 

systemctl restart wazuh-manager

In case it could be useful, this is the related documentation link: https://documentation.wazuh.com/current/user-manual/manager/manual-database-output.html

Please, let me know if this information helps out. 

Looking forward to your reply. Best regards.

Juan

[Madison !]

[root@localhost home]# sudo -u postgres createuser -P PostgreSQLadmin

Enter password for new role:

Enter it again:

createuser: creation of new role failed: ERROR:  role "PostgreSQLadmin" already exists

[root@localhost home]# sudo -u postgres createdb -O PostgreSQLadmin Alerts_DB

createdb: database creation failed: ERROR:  database "Alerts_DB" already exists

[root@localhost home]# psql -U PostgreSQLadmin -d Alerts_DB -f /home/postgresql.schema

psql: FATAL:  Peer authentication failed for user "PostgreSQLadmin"

[root@localhost home]#

i've the error Peer authentication failed

вторник, 13 апреля 2021 г. в 22:26:47 UTC+6, juan....@wazuh.com:

[Juan Ricci]

Hello,

Please, try changing the authentication method in the pg_hba.conf file (you can execute find / -iname pg_hba.conf for finding it). Change this line:

local all  PostgreSQLadmin  peer

to

local all  PostgreSQLadmin  trust

and restart the service:

systemctl restart postgresql

As PostgreSQL server and Wazuh Manager were installed on different machines, pgsql could be rejecting connections from the Wazuh Manager host.

For more information kindly check: 

http://www.postgresql.org/docs/9.1/static/auth-methods.html

https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge

Please let me know if you need further help.

Regards.

Juan

[Madison !]

Hello could you help me where i should add this.

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

below that config? <!-- Database synchronization settings -->

    <synchronization>

      <enabled>yes</enabled>

      <interval>5m</interval>

      <max_interval>1h</max_interval>

      <max_eps>10</max_eps>

    </synchronization>

пятница, 16 апреля 2021 г. в 03:09:47 UTC+6, juan....@wazuh.com:

[Madison !]

Could you help me pls 

Hello could you help me where i should add this.

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

below that config? <!-- Database synchronization settings -->

    <synchronization>

      <enabled>yes</enabled>

      <interval>5m</interval>

      <max_interval>1h</max_interval>

      <max_eps>10</max_eps>

    </synchronization>

воскресенье, 30 мая 2021 г. в 16:07:13 UTC+6, Madison !: