All Linux HowTo's Security HowTo's

Rsyslog and MySQL on CentOS7 and Redhat 7

This article explains how to create an Rsyslog server that logs to MySQL (MariaDB). We’ve used CentOS 7 for this article.

A few tips to save you time:
1. If you’re logging from a remote node to this server, make sure you have proper host names because that’s what ends up in the logs.
2. Consider using TLS for remote logging (documented here) as logs are a peripheral target for hackers.
3. In my examples below, make sure to change IP addresses and FQDN’s to match your environment.

You may need to ensure IPTables access if you intend to remotely access this logging server.

Configure the server

Install the required software.

yum install rsyslog-mysql mariadb-server rsyslog-gnutls

Start and enable MariaDB:

systemctl restart mariadb
systemctl enable mariadb

Build the DB. You may have to change the verion in the following command. Mine is version 7.4.7:

mysql < /usr/share/doc/rsyslog-7.4.7/mysql-createDB.sql

Give the sysloguser the right permissions. It only needs the INSERT permission but i've added SELECT for testing.

GRANT INSERT ON Syslog.* to 'sysloguser'@'localhost' identified by 'iuHUzzhUIhHjkKJLHuU';
GRANT SELECT ON Syslog.* to 'sysloguser'@'localhost' identified by 'iuHUzzhUIhHjkKJLHuU';
flush privileges;

Edit the file:

/etc/rsyslog.conf

My file looks like this:

$LocalHostName www.example.com
$ModLoad ommysql.so
$WorkDirectory /var/lib/rsyslog
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat
$IncludeConfig /etc/rsyslog.d/*.conf
$OmitLocalLogging on
$IMJournalStateFile imjournal.state
*.info;mail.none;authpriv.none;cron.none :ommysql:localhost,Syslog,sysloguser,iuHUzzhUIhHjkKJLHuU
authpriv.* /var/log/secure
mail.* -/var/log/maillog
cron.* /var/log/cron
*.emerg :omusrmsg:*
uucp,news.crit /var/log/spooler
local7.* /var/log/boot.log

In the above, the two changed or added lines are:

$LocalHostName www.example.com
$ModLoad ommysql.so
*.info;mail.none;authpriv.none;cron.none :ommysql:localhost,Syslog,sysloguser,iuHUzzhUIhHjkKJLHuU

Restart Rsyslogd:

systemctl restart rsyslog

All done. You can log into the DB and do a select statement to see additions to the DB.

mysql --user sysloguser -p Syslog
SELECT * FROM SystemEvents;

Secure remote logging service

On the server

Uncomment the following lines from the "/etc/rsyslog.conf" file:

# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514

# Provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514

Run the following commands to create the certificates:

yum install rsyslog-gnutls
mkdir /etc/rsyslog.ssl ; cd /etc/rsyslog.ssl
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Add the following to the "/etc/rsyslog.d/remoteserver.conf" file:

### START SECURE
$DefaultNetstreamDriver gtls
$DefaultNetstreamDriverCAFile /etc/rsyslog.ssl/ca-cert.pem
$DefaultNetstreamDriverCertFile /etc/rsyslog.ssl/server-cert.pem
$DefaultNetstreamDriverKeyFile /etc/rsyslog.ssl/server-key.pem

$ModLoad imtcp

$InputTCPServerStreamDriverMode 1
$InputTCPServerStreamDriverAuthMode anon
$InputTCPServerRun 5822
### END SECURE

Restart the rsyslog server:

systemctl restart rsyslog

Client side secure logging

yum install rsyslog-gnutls
mkdir /etc/rsyslog.ssl ; cd /etc/rsyslog.ssl

Copy the server's "/etc/rsyslog.ssl/ca-cert.pem" file onto the client in file "/etc/rsyslog.ssl/ca-cert.pem". Make sure the permissions are set correctly on that file:

chmod 600 /etc/rsyslog.ssl/ca-cert.pem

Add the following to the end of the "/etc/rsyslog.d/remotelogger.conf" file:

### START SECURE
# The address below can be an IP address or FQDN.
*.* @@logger.example.com:514

$DefaultNetstreamDriverCAFile /etc/rsyslog.ssl/ca-cert.pem
$DefaultNetstreamDriver gtls
$ActionSendStreamDriverMode 1
$ActionSendStreamDriverAuthMode anon
### END SECURE

Restart the rsyslog server:

systemctl restart rsyslog

Share This:

Leave a Reply

Your email address will not be published. Required fields are marked *