Configuring database audit logging on PingFederate with MySQL

Paul Heaney, Technical Director at ProofID discusses how to configure database audit logging on PingFederate with MySQL.

In an enterprise PingFederate deployment, it’s a good idea to configure the system to log to a central repository such as database rather than to text files.

One of the main reasons for this is that enterprise deployments tend to be clustered, with multiple runtime nodes, and each node will log events to its own local text files by default. This can make troubleshooting hard, as you often need to cross-reference log entries against multiple log files on different nodes to make sense of what’s going on across the system. With database logging, all nodes send their logging events to a single, central database, which makes it much easier to track and troubleshoot events across nodes.

Another benefit of database logging is that the PingFederate log files are a valuable source of information about your environment, beyond simply providing insight into how your system is performing. For example, the PingFederate audit log tells you who is logging into which applications and when, and contains important security information such as failed logins. Placing all this data into a central repository means you can easily create reports to gain real insights into the behavior of your users and meet your compliance requirements.

Unfortunately, however, the PingFederate documentation, whilst accurate, assumes quite a lot of knowledge which makes configuration of database logging quite tricky for a first timer. This blog aims to fill in some of the gaps in the documentation and explain how to complete a simple configuration – to configure audit logging to a MySQL database for a single PingFederate server. The intention is to help administrators become comfortable with the process in a test environment.

For this blog, I’m assuming that everything is running on a Linux server (I’m using Ubuntu).

Set up your MySQL database

I’m assuming that you already have MsQL set up and running on your databse server. If not, there’s a great tutorial on setting up MySQL on Ubuntu here.

The first step is to create your PingFederate audit database.

• Log into MySQL as root:

mysql –u root –p

• Create a new database:

CREATE DATABASE PFAUDIT;

• Select this new database:

USE PFAUDIT

• Next you need to run the appropriate database setup script. Ping provides scripts to set up your MySQL database so it is ready to accept log entries. You can find them in the directory below.

/pingfedrate/server/default/conf/log4j/sql-scripts

The easiest way to do this is to open the correct script in a text editor. For the audit log, you need

audit-log-mysql.sql

Copy the script into the buffer, and then paste the script into the mysql command line.

• When the database has been created, you need to create a new non-root user that PingFederate will use to communicate with the database. You also need to grant the necessary privileges to this user:

CREATE USER 'pfaudit'@'localhost' IDENTIFIED BY 'P455w0rd'; GRANT SELECT, INSERT ON PFAUDIT.* TO 'pfaudit'@'localhost';

• You will need to ensure that your PingFederate server can talk to your database server on the appropriate port. By default, MySQL listens on port 3306. An easy way to check that the PingFederate server can communicate with the database is to use telnet from the PingFederate server. The example below shows the command (insert your real host name) and what a successful connection looks like.

telnet database-server.test.com 3306 Trying xx.xx.xx.xx... Connected to database-server.test.com Escape character is '^]'. [ 5.7.16-0ubuntu0.16.04.1$R} y%-a-)e{Y'% >mysql_native_password

Install JDBC driver on PingFederate server

You need to install the JDBC driver to enable the PingFederate server to query and write to the MySQL database. The documentation is a bit vague on what is required here.

For MySQL, the file you need is mysql-connector-java-5.1.42-bin.jar (or the latest version), and it can be downloaded from here.

You need to copy the file to the following directory on your PingFederate server.

/pingfederate/server/default/lib

Then you’ll need to restart your PingFederate sever.

sudo service pingfederate restart

Configure logging on the PingFederate server

PingFederate uses a logging engine called log4j2. The configuration files for this are found in the below directory on your PingFederate server.

/pingfederate/server/default/conf

The first step is to tell your PingFederate server about your database:

• Edit the log4j2.db.properties file.

• Complete the mysql.url, mysql.username and mysql.password fields. An example is shown below for reference:

mysql.url=jdbc:mysql://database-server.test.com:3306/PFAUDIT mysql.username=pfaudit mysql.password=P455w0rd

The mysql url takes the form of jdbc:mysql://{database server hostname or IP address}:{port}/{database name}

In a production environment, it is strongly recommended that the password is obfuscated using obfuscate.sh supplied with PingFederate.

The next step is to configure log4j2 to send audit log events to the MySQL database. The configuration file for log4j2 is log4j2.xml, and it is a long, complex file which seems unwieldy the first time you work with it. Once you have a working configuration however, it becomes a bit clearer how the file works, and the Ping documentation starts to make a bit more sense!

The first thing to do is to enable audit logging to MySQL by uncommenting the relevant part of the config file. You are looking for the following element, which is typically around 70% through the file:

You need to uncomment from this element down as far as (about 30 lines).

The final step is to tell the relevant logging sections of the file to log events to our new MySQL failover appender. You need to find the section of the file below:

<Loggers>

For each of the Loggers listed below, comment out the existing SecurityAudit2File appender reference add a reference to the appender.

An example of an updated Logger configuration is shown below:

Note the appender line in bold – it is referring to the failover appender we configured earlier. It is important that only one appender is active for these loggers.

PingFederate automatically reloads the log4j2.xml file so there is no need to restart PingFederate.

Test logging

You are now ready to test that database logging is working. Log into an application that is configured to federate via PingFederate, and you should generate some audit log events.

To check that the events are hitting your database, you can run the following command from your MySQL console on the database server:

select * from audit_log;

If things are working you should see output like the following:

+—-+———————+——-+———-+———– —–+——+———–+———-+——+————— —————————————————+—— —+————–+————-+————–+

| id | dtime | event | username | ip | app | host | protocol | role | partnerid | status | adapterid  | description | responsetime |

+—-+———————+——-+———-+—————-+——+———

–+———-+——+————————————————————–

—+———+————–+————-+————–+

|  1 | 2017-05-04 08:27:28 | SSO   | 5001  | xx.xx.xx.xx |   | localhost | SAML20   | IdP  | https://www.test.com/saml2/service-provider/spia5zh3yca9colmU0h7 | success | HTMLAdapter1 |     |    53 |

+—-+———————+——-+———-+—————-+——+——–

—+———-+——+———————————————————–

——-+———+————–+————-+————–+