All Linux HowTo's Security HowTo's

Secure Log Remotely to Syslog & Store The Logs in MySQL | Redhat Linux & Centos

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:

  • 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.
  • Consider using TLS for remote logging (documented here) as logs are a peripheral target for hackers.
  • In my examples below, make sure to change IP addresses and FQDN’s to match your environment.
  • The certificates (and related keys) are created on the server side and copied to the client.

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

Configure the server

Install the required software. “MariaDB-server” is the SQL database server that will be storing our logs, “rsyslog-mysql” is the module for rsyslog to access Maria and “rsyslog-gnutls” is the rsyslog module that will allow us to communicate securely over TLS.

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 version in the following command. Mine is version 7.4.7. The “mysql-createDB.sql” file is an SQL script that MySQL will run to setup a fresh database ready for rsyslog:

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. This is done in the MariaDB monitor. To start the monitor run:

sudo mysql

Then type the following MySQL commands:

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:

$ModLoad ommysql.so
$ModLoad imudp
$UDPServerRun 514
$WorkDirectory /var/lib/rsyslog
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat
$IncludeConfig /etc/rsyslog.d/*.conf
$OmitLocalLogging on
$IMJournalStateFile imjournal.state
*.info; :ommysql:localhost,rsyslogDB,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

$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
$InputTCPServerStreamDriverPermittedPeer * 
$InputTCPServerStreamDriverMode 1
$InputTCPServerStreamDriverAuthMode anon
$InputTCPServerRun 515

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;

Now we need to start work on the rsyslog server components.

Uncomment the following lines from the “/etc/rsyslog.conf” file. Note that we’ll be logging over port “515” using TLS. We won’t be sending logs in the clear.

# 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

It is vital that you don’t share your keys with anyone, this would allow them to imitate the given host and send fictitious log reports.

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

Still on the server: Now we need to generate a key pair for our client that is trusted and signed by the CA. We do this on the same machine (the server) that we created the ca-cert.pem and ca-key.pem (in our example the rsyslog/mariadb server) by running:

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

On the client.

Create the directory structure:

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

We need to copy the certificate and related files from the server to the client. The files you need to copy are some of those that you created above. They are listed here:

  • ca-cert.pem
  • client-cert.pem
  • client-key.pem

Make sure the permissions are set correctly on the files:

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

Add the following to the end of the “/etc/rsyslog.d/remotelogger.conf” file. Note that the “logger.example.com” server is the rsyslog remote server address:

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

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

Restart rsyslog on the client:

systemctl restart rsyslog

For more in-depth documentation about rsyslogs TLS module, visit the Rsyslog website:
http://www.rsyslog.com/doc/v8-stable/tutorials/tls_cert_summary.html

Troubleshooting

If something isn’t quite working right you can try a few things:

1. Check that your syntax in rsyslog.conf is valid or that you are using a parameter correctly by running:

rsyslogd -N1

2. Test that logs are getting through by using the “logger” command:

logger -s -t TEST testing

3. Rsyslog dumps its own error logs locally in:

/var/log/messages
or on other systems
/var/log/syslog

4. On the MariaDB server check that the database is not corrupted somehow by:

sudo mysql
USE Syslog;
CHECK TABLE SystemEvents;

Similar Posts:

Leave a Reply

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