Monitoring PostgreSQL database with Wazuh

| by Benjamin Nworah | Wazuh 4.7.3
Post icon

PostgreSQL is an open source, highly stable database management system that uses several features to securely store and scale data workloads. PostgreSQL is supported by major operating systems such as Linux, macOS, Microsoft Windows, FreeBSD, OpenBSD, and Solaris. It is primarily used by users, organizations, and businesses to store data for mobile applications, websites, analytics applications, and others.

Wazuh is a free and open source enterprise security platform that offers protection against security threats in the cloud, on-premises, virtualized, and containerized environments.

PostgreSQL can be configured to store database activities such as creating, updating, dropping a database, and others in a log file. Wazuh monitors this log file to detect database activities.

This blog post demonstrates how Wazuh detects activities performed on a PostgreSQL database.

Infrastructure

To demonstrate the monitoring of a PostgreSQL database with Wazuh, we use the following setup:

  • A pre-built, ready-to-use Wazuh OVA 4.7.3: Follow this guide to download the virtual machine (VM). This VM hosts the Wazuh central components (Wazuh server, Wazuh indexer, and Wazuh dashboard).
  • An Ubuntu 22.04 endpoint: A Wazuh agent 4.7.3 installed and enrolled to the Wazuh server. We install a PostgreSQL 16.2 database on this endpoint.

Configuration

In this section, we set up a PostgreSQL database on an Ubuntu endpoint. We configure the Wazuh agent to collect and forward the activities performed on the PostgreSQL database to the Wazuh server for analysis. Finally, we create decoders and rules on the Wazuh server to detect these database activities.

Ubuntu endpoint

Perform the following steps to install and configure the PostgreSQL database.

1. Run the following commands to set the PostgreSQL apt repository:

$ sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ sudo wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

2. Update all packages installed on the Ubuntu endpoint:

$ sudo apt-get update

3. Install PostgreSQL database:

$ sudo apt-get -y install postgresql

4. Run the command below to locate the PostgreSQL configuration file:

$ sudo -u postgres psql -c 'SHOW config_file;'
               config_file
-----------------------------------------
 /etc/postgresql/16/main/postgresql.conf
(1 row)

You should see an output similar to the one above. Take note of the PostgreSQL configuration file /etc/postgresql/16/main/postgresql.conf, because you need it to configure logging on your PostgreSQL database.

5. Open the PostgreSQL configuration file /etc/postgresql/16/main/postgresql.conf. Locate and uncomment the log_statement option, and change its value from none to mod. This setting enables the logging of data definition language (DDL) and data manipulation language (DML) statements such as CREATE, DROP, INSERT, UPDATE, and others.

log_statement =  'mod'

6.  Restart the PostgreSQL service for the changes to take effect:

$ sudo systemctl restart postgresql

7. Run the following command to locate the log file that stores the PostgreSQL logs:

$ sudo pg_lsclusters

You should have a similar output.

Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

From the above output, the file /var/log/postgresql/postgresql-16-main.log stores the PostgreSQL logs.

8. Edit the Wazuh agent /var/ossec/etc/ossec.conf file and add the following configuration within the <ossec_config> block. This configuration allows the Wazuh agent to monitor the file /var/log/postgresql/postgresql-16-main.log and forward its content to the Wazuh server.

<!-- Configure the Wazuh agent to collect and forward the PostgreSQL logs to the Wazuh server for analysis -->
<localfile>
  <log_format>syslog</log_format>
  <location>/var/log/postgresql/postgresql-16-main.log</location>
</localfile>

9. Restart the Wazuh agent service for the changes to take effect:

$ sudo systemctl restart wazuh-agent

Wazuh server

Perform the following steps to add decoders and rules to detect activities performed on a PostgreSQL database.

1. Create the decoder file /var/ossec/etc/decoders/postgresql_decoders.xml on the Wazuh server:

# touch /var/ossec/etc/decoders/postgresql_decoders.xml

2. Edit the decoder file /var/ossec/etc/decoders/postgresql_decoders.xml and include the following decoders:

<decoder name="postgresql">
  <prematch type="pcre2">(?i)statement:</prematch>
</decoder>
<decoder name="postgresql_child">
  <parent>postgresql</parent>
  <regex type="pcre2">(?i)\d+-\d+-\d+\s+\d+:\d+:\d+.\d+\s+\S+\s+\[\d+\]\s+(\S+\s*\S+)@\S+\s+LOG:\s+statement:\s+create\s+database\s+([^"\s;]+)</regex>
  <order>db_user,database</order>
</decoder>
<decoder name="postgresql_child">
  <parent>postgresql</parent>
  <regex type="pcre2">(?i)\d+-\d+-\d+\s+\d+:\d+:\d+.\d+\s+\S+\s+\[\d+\]\s+(\S+\s*\S+)@\S+\s+LOG:\s+statement:\s+create\s+database\s+"([^;]+)"</regex>
  <order>db_user,database</order>
</decoder>
<decoder name="postgresql_child">
  <parent>postgresql</parent>
  <regex type="pcre2">(?i)\d+-\d+-\d+\s+\d+:\d+:\d+.\d+\s+\S+\s+\[\d+\]\s+(\S+\s*\S*)@\S+\s+LOG:\s+statement:\s+drop\s+database\s+([^"\s;]+)</regex>
  <order>db_user,database</order>
</decoder>
<decoder name="postgresql_child">
  <parent>postgresql</parent>
  <regex type="pcre2">(?i)\d+-\d+-\d+\s+\d+:\d+:\d+.\d+\s+\S+\s+\[\d+\]\s+(\S+\s*\S*)@\S+\s+LOG:\s+statement:\s+drop\s+database\s+"([^;]+)"</regex>
  <order>db_user,database</order>
</decoder>
<decoder name="postgresql_child">
  <parent>postgresql</parent>
  <regex type="pcre2">(?i)\d+-\d+-\d+\s+\d+:\d+:\d+.\d+\s+\S+\s+\[\d+\]\s+(\S+\s*\S*)@\S+\s+LOG:\s+statement:\s+create\s+table\s+([^"\s;]+)</regex>
  <order>db_user,db_table</order>
</decoder>
<decoder name="postgresql_child">
  <parent>postgresql</parent>
  <regex type="pcre2">(?i)\d+-\d+-\d+\s+\d+:\d+:\d+.\d+\s+\S+\s+\[\d+\]\s+(\S+\s*\S*)@\S+\s+LOG:\s+statement:\s+create\s+table\s+"([^;]+)"</regex>
       <order>db_user,db_table</order>
</decoder>
<decoder name="postgresql_child">
  <parent>postgresql</parent>
  <regex type="pcre2">(?i)\d+-\d+-\d+\s+\d+:\d+:\d+.\d+\s+\S+\s+\[\d+\]\s+(\S+\s*\S*)@\S+\s+LOG:\s+statement:\s+drop\s+table\s+([^"\s;]+)</regex>
  <order>db_user,db_table</order>
</decoder>
<decoder name="postgresql_child">
  <parent>postgresql</parent>
  <regex type="pcre2">(?i)\d+-\d+-\d+\s+\d+:\d+:\d+.\d+\s+\S+\s+\[\d+\]\s+(\S+\s*\S*)@\S+\s+LOG:\s+statement:\s+drop\s+table\s+"([^;]+)"</regex>
  <order>db_user,db_table</order>
</decoder>
<decoder name="postgresql_child">
  <parent>postgresql</parent>
  <regex type="pcre2">(?i)\d+-\d+-\d+\s+\d+:\d+:\d+.\d+\s+\S+\s+\[\d+\]\s+(\S+\s*\S*)@\S+\s+LOG:\s+statement:\s+insert\s+into\s+([^"\s;]+)</regex>
       <order>db_user,db_table</order>
</decoder>
<decoder name="postgresql_child">
  <parent>postgresql</parent>
  <regex type="pcre2">(?i)\d+-\d+-\d+\s+\d+:\d+:\d+.\d+\s+\S+\s+\[\d+\]\s+(\S+\s*\S*)@\S+\s+LOG:\s+statement:\s+insert\s+into\s+"([^;]+)"</regex>
  <order>db_user,db_table</order>
</decoder>
<decoder name="postgresql_child">
  <parent>postgresql</parent>
  <regex type="pcre2">(?i)\d+-\d+-\d+\s+\d+:\d+:\d+.\d+\s+\S+\s+\[\d+\]\s+(\S+\s*\S*)@\S+\s+LOG:\s+statement:\s+update\s+([^"\s;]+)</regex>
  <order>db_user,db_table</order>
</decoder>
<decoder name="postgresql_child">
  <parent>postgresql</parent>
  <regex type="pcre2">(?i)\d+-\d+-\d+\s+\d+:\d+:\d+.\d+\s+\S+\s+\[\d+\]\s+(\S+\s*\S*)@\S+\s+LOG:\s+statement:\s+update\s+"([^;]+)"</regex>
  <order>db_user,db_table</order>
</decoder>

3. Create the file /var/ossec/etc/rules/postgresql_rules.xml on the Wazuh server:

# touch /var/ossec/etc/rules/postgresql_rules.xml

4. Edit the file /var/ossec/etc/rules/postgresql_rules.xml and include the following detection rules:

<group name="postgresql,">
<!-- This rule detects a PostgreSQL log. No alert is generated by this rule.-->
  <rule id="100080" level="0">
    <decoded_as>postgresql</decoded_as>
    <description>No alerts.</description>
  </rule>
<!-- This rule detects when a database is created.-->
  <rule id="100081" level="4">
    <if_sid>100080</if_sid>
    <match type="pcre2">(?i)create database</match>
    <description>A database $(database) has been created by the user $(db_user).</description>
  </rule>
<!-- This rule detects when a database is deleted.-->
  <rule id="100082" level="6">
    <if_sid>100080</if_sid>
    <match type="pcre2">(?i)drop database</match>
    <description>A database $(database) has been deleted by the user $(db_user).</description>
    <mitre>
      <id>T1485</id>
    </mitre>
  </rule>
<!-- This rule detects when a table is created.-->
  <rule id="100083" level="4">
    <if_sid>100080</if_sid>
    <match type="pcre2">(?i)create table</match>
    <description>A table $(db_table) has been created by the user $(db_user).</description>
  </rule>
<!-- This rule detects when a database table is deleted.-->
  <rule id="100084" level="6">
    <if_sid>100080</if_sid>
    <match type="pcre2">(?i)drop table</match>
    <description>A table $(db_table) has been deleted by the user $(db_user).</description>
    <mitre>
      <id>T1485</id>
    </mitre>
  </rule>
<!-- This rule detects when new values are inserted into a table.-->
  <rule id="100085" level="4">
    <if_sid>100080</if_sid>
    <match type="pcre2">(?i)insert into</match>
    <description>A table $(db_table) has been inserted with new values by the user $(db_user).</description>
    <mitre>
      <id>T1565.001</id>
    </mitre>
  </rule>
<!-- This rule detects when the value in a table is updated.-->
  <rule id="100086" level="4">
    <if_sid>100080</if_sid>
    <match type="pcre2">(?i)update</match>
    <description>A table $(db_table) has been updated by the user $(db_user).</description>
    <mitre>
      <id>T1565.001</id>
    </mitre>
  </rule>

</group>

Where:

  • Rule ID 100080 is triggered when Wazuh detects a PostgreSQL log. This rule does not generate any alert on the Wazuh dashboard.
  • Rule ID 100081 is triggered when a database is created.
  • Rule ID 100082 is triggered when a database is deleted.
  • Rule ID 100083 is triggered when a table is created.
  • Rule ID 100084 is triggered when a table is deleted.
  • Rule ID 100085 is triggered when new values are inserted into a table.
  • Rule ID 100086 is triggered when any value in a table is updated.

5. Restart the Wazuh manager for your changes to take effect:

# systemctl restart wazuh-manager

Testing the configuration 

Our use cases involve the following activities performed on the PostgreSQL database:

Ubuntu endpoint

Creating a database and a table

Perform the following steps to create a database and a table.

1. Run the following command to log in as the default postgres user:

$ sudo -i -u postgres

2. Run the following command to access the terminal of the PostgreSQL database:

$ psql

3. Create the database test_db:

postgres=# CREATE DATABASE test_db;

You should see a similar output if the database is created successfully.

CREATE DATABASE

4. Run the following command to connect to the database test_db:

postgres=# \c test_db

You should have a similar output if you have connected successfully to the database test_db.

You are now connected to database "test_db" as user "postgres".

5. Create a table test_table with Name, Role, and Department fields:

test_db=# CREATE TABLE test_table (Name varchar(30), Role varchar(30), Department varchar(40));

You should see a similar output if the table is created successfully.

CREATE TABLE

Inserting new values into a table

1. While still connected to the database test_db, run the following command to insert values into the table test_table. The command inserts the values test_user, test_engineer, and test_department into the fields Name, Role, and Department respectively.

test_db=# INSERT INTO test_table ( Name, Role, Department) VALUES ('test_user', 'test_engineer','test_department' );

You should see a similar output.

INSERT 0 1

Updating values in a table

1. While still connected to the database test_db, run the following command to change the value of the Department field in the table test_table from test_department to test_department10:

test_db=# UPDATE test_table SET Department = 'test_department10' WHERE Name = 'test_user';

You should see a similar output if your table is updated successfully.

UPDATE 1

Deleting a table and a database

1. While still connected to the database test_db, run the following command to delete the database table test_table:

test_db=# DROP TABLE test_table;

You should see a similar output if your table is deleted successfully.

DROP TABLE

2. Run the following command to connect to the default database postgres:

test_db=# \c postgres

3. Delete the database test_db:

postgres=# DROP DATABASE test_db;

You should see a similar output if your database is deleted successfully.

DROP DATABASE

Visualizing alerts on the Wazuh dashboard

Perform the following steps to view the alerts on the Wazuh dashboard.

1. Navigate to Modules > Security Events > Events.

2. Click + Add filter. Then filter for rule.id in the Field field.

3. Filter for is one of in the Operator field.

4. Filter for 100081, 100082, 100083, 100084, 100085, and 100086 in the Values field.

5. Click Save.

PostgreSQL

Conclusion

In this blog post, we successfully demonstrated how Wazuh monitors the activities performed on a PostgreSQL database. This integration helps organizations leverage Wazuh to provide visibility into their PostgreSQL database.

Wazuh is a free and open source enterprise security solution for uncovering security threats, incident response, and meeting regulatory requirements. Wazuh has an ever-growing community where users are supported. To learn more about Wazuh, please check out our documentation and blog posts.

References