OpenVPN DB Log is a project to log OpenVPN connect and disconnect events to a database.

This project logs OpenVPN connect / disconnect events to a database. The code is in Perl, so adding support for new backend databases is fairly simple.

The OpenVPN DB Log project is licensed under the GPLv3 license:

• https://opensource.org/licenses/GPL-3.0

To use this project meaningfully, you will need:

• OpenVPN, without which this project won't be of much use
• Perl installed, and available to execute this project's script
• The DBI Perl module, plus the relevant DBD:: module for your database
• The SQL server you are connecting to must be configured for access
• If this is a new setup, you must load the project schema into the database

For the impatient, a minimal setup includes a connect and disconnect association with OpenVPN, plus telling it about the database you want to use. This example uses SQLite, but you're free to use any available backend.

You'll need to create a new database with the schema. For SQLite, you can do:

sqlite3 /var/db/vpn.sqlite < ./schema/sqlite.sql

Then add the following 2 lines in your OpenVPN config file to provide a basic logging setup, using the same database:

client-connect /path/to/openvpn-db-log.pl -b SQLite -d /var/db/vpn.sqlite

client-disconnect /path/to/openvpn-db-log.pl -b SQLite -d /var/db/vpn.sqlite

Additional features for database backends, recording the server program instance, and live-client-updates are described in more detail below.

Multiple databases are supported by available Perl DBD backends as available on the local system. So long as you have the DBD driver available and a schema, the code should work with most standard SQL systems. Schemas are provided in the schemas/ directory for the following database systems:

• MySQL (backend name mysql)
• PostgreSQL (backend name Pg)
• SQLite (backend name SQLite)

If there isn't a schema available for your preferred RDBMS, consider creating and contributing one (more info in docs/Hacking.md.)

Most database backends will require a server, database name, and user credentials. See the Database options help output for the program flags for each. If your particular database doesn't require one of these, simply omit the option.

If your database backend requires credentials, you can supply them either with the --user / --pass options, or with a --credentials (-c) file. When used, the first 2 lines of a credentials file will be taken as the user and password.

In addition to database connection options, some standard features are available.

Normally, on-connect processing will report a non-zero status if SQL logging fails. This will cause OpenVPN to reject the connection when the script is called using the --client-connect OpenVPN directive.

Where this is undesirable and you want to allow the client connection anyway (simply unlogged) you should enable the --fork (-f) option.

This causes backgrounding of the logging before the SQL connection, returning code 0 (success) to OpenVPN so the client connection may continue.

Note that the script may still exit non-zero if a failure occurs before reaching the SQL connect stage. Read about the Zero feature to avoid this.

Some implementations may want a 0 exit code in the face of fatal problems, such as missing env-vars or bogus command options.

For this, enable the --zero (-z) flag. Error messages are still printed to standard error.

To disable printing of error messages, pass the --quiet (-q) flag.

The exit code is not changed, but can be combined with the --zero flag.

As an advanced/alternative form to provide database values like the database name, host, port, etc, the --dsn command line option allows you to set any DSN attribute you would like. This also works for non-standard values if your specific database backend requires one.

The form of this option is: --dsn opt=value

This method can NOT be used to define the backend driver (-b) or the user/pass (-u/-p) options. This is only valid for the database DSN options.

For example, the following two calls are identical:

openvpn-db-log -b mysql -d vpn_db -H localhost -t 3306 -u db_user -p db_pass

openvpn-db-log -b mysql --dsn database=vpn_db --dsn host=localhost \
  --dsn port=3306 -u db_user -p db_pass

Another advanced feature used by some DBD drivers is environmental variables. To set these, the --env option is available, and used like so:

--env var=value

This will set the env-var var to the value value. If the value has spaces, it is necessary to quote the entire stanza, as in: --env "var=a b c".

Notably, the PostgreSQL driver offers PGSYSCONFDIR that is not available as a DSN option. Also note that this env-var feature can not be used to provide user/pass credentials; you shouldn't do that anyway as this option is exposed to tools like ps.

In order to provide more up-to-date logs and session data, it is possible to parse an OpenVPN status-file and update the duration and byte-transfer fields for connected clients. This allows a "partial-update" to occur, even though the time of disconnect is not yet known.

This is especially useful in environments where long-lived connections are common. When an OpenVPN server is terminated (or the app/OS crashes) there will be no disconnect event despite a partial database row for the connection.

In order to do partial-updates of status files, you must:

• Enable the --status-file OpenVPN directive
• Set the --status-version to version 2 or 3 (v1 is not supported)
• Enable regular processing of the status file (see below)

You will need to set up some regular process by which the program is told to parse a status file from OpenVPN; this could be cron, or some other on-demand event that suits your needs.

The --status-file (-S) flag is the file path and the --status-version (-V) flag must match the OpenVPN setting by the same name (v3 is the default.)

The --update-create (-C) flag allows a missing session entry to be created instead of failing that entry. This situation could occur if a prior client-connect event failed (due to DB error or network issues.) This feature enables the session to be added at the time of update.

As an advanced feature, the -N flag mandates that every client line must be successfully matched and added to the database in order for the transaction to go through. This is probably only useful for people processing the real-time output of a status command from OpenVPN using the STDIN input support; and only then when this is desired behavior.

The --status-age (-A) flag sets a maximum allowable increase between the status file timestamp and the current system clock, in seconds. If an OpenVPN server has terminated, the status file is often left as-is; this option prevents needless database connections.

Additional informational messages for status-file updates can be printed to STDERR when the -N option is not used.

Passing the --status-info (-I) option 1 or more times enables this feature. This option takes no arguments. The verbosity levels are:

1. Invalid --status-file lines show the reason for rejection
2. Like #1, but also print the full rejected line

Some environments have multiple OpenVPN servers and would like to log them to a single backend. Common examples include running on TCP + UDP, leveraging multiple CPU cores, or even separate servers being logged centrally.

In order to identify the correct source of connections in a multi-instance environment, 3 optional instance descriptors are available:

• Name: a textual name or description, up to 64 characters
• Protocol: a textual protocol, up to 10 characters
• Port: a TCP/UDP port in the range of 1-65535

These options default to empty strings and the pseudo-value 0 respectively.

When using this feature, the unique combination of these 3 values defines a separate instance. This means that if you log some connections with name=Blue, then other connections with name=Blue port=1195, these would be stored internally with different instance IDs.

Once an instance association has been added to the database, the unique combination appears in the instances table. This means updating values is possible without much effort, but will require an update to the table entry for the instance definition.

This may eventually be made easier through a utility script. Note that this information is stored in the instances table alone, and referenced as an ID from the sessions table.

Most uses of the partial-update feature will likely want to use the status processing feature described above. For advanced needs, a basic API is provided to do partial-update processing. Limited error checking is done, since the values are normally supplied from OpenVPN anyway.

To use, declare the following env-vars for the call to openvpn-db-log.pl:

• trusted_ip or trusted_ip6
• trusted_port
• common_name
• bytes_received
• bytes_sent
• time_unix
• script_type (must be set to the string: "db-update")
• ifconfig_pool_remote_ip (optional, as OpenVPN may not have issued one)
• time_update (optional, the system time will be used when omitted)

Note that any program CLI options for DB or other program features must still be supplied as described earlier.

The --update-create feature can be used with this API as well (see Processing status files for details.)

Running release-versions is the safest way to go as this project won't guarantee schema stability for non-release versions. Official releases will contain incremental SQL upgrade scripts for any schema changes.

If you're not comfortable adjusting database schema between versions as necessary, a development branch is probably not for you.