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 the following. Note that we’re using TCP only. No UDP.
$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 514
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 “514” 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 $InputTCPServerStreamDriverPermittedPeer *.example.com $InputTCPServerStreamDriverAuthMode x509/name $InputTCPServerRun 514 ### END SECURE
As i understand it, the line ending (above) with “*.example.com” means only systems who’s signed certificate has that name in it. The doco is here.
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. # Note that we're using TCP only. No UDP. *.* @@logger.example.com:514 # The name of this host (the client) $LocalHostName client1.example.com $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 x509/name ### 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;
