Database#

The VPN server supports other databases than just the default SQLite. For SQLite, no configuration is needed, it works out of the box. If you are happy with that, there is no need to read this document.

You should only consider switching databases when these conditions hold:

1. Your database server(s) have higher reliability/availability than your VPN server;
2. You want to run HA Portal setup.

In all other cases using the default SQLite will be better and easier. This document explicitly only tells you how to use other database, not how to install and configure them. We do have some documentation on how we set up the databases for development purposes and testing.

Next to SQLite, we support PostgreSQL and MariaDB / MySQL. If you need to move away from SQLite, and have a choice, we recommend you use PostgreSQL.

For the remainder of this document, we assume you used deploy_${DIST}_controller.sh and deploy_${DIST}_node.sh to install the VPN service.

Configuration#

SQLite#

NOTE: using the db tool with SQLite is only supported in vpn-user-portal >= 3.7.0.

For SQLite you do not need to do anything. It requires no configuration. When using SQLite, any database initialization, or migration is done automatically if needed. However, you can use the db tool on the SQLite database in case you want to manually perform migrations for some reason:

$ sudo /usr/libexec/vpn-user-portal/db
Database Driver        : sqlite
Journal Mode           : delete
Current Schema Version : 2024052701
Required Schema Version: 2024052701
Status                 : **OK**

NOTE: the SQLite database can be found in /var/lib/vpn-user-portal/db.sqlite in case you want to make a backup, which is highly recommended before you start tinkering!

If you want, but this is currently EXPERIMENTAL, you can change the “Journal Mode” from its default delete to wal with the --set-journal-mode flag. Read Write-Ahead Logging on the SQLite site for more information.

PostgreSQL#

Make sure you have the php-pgsql package installed, on Debian use apt instead of dnf:

$ sudo dnf install php-pgsql
$ sudo systemctl restart php-fpm

In /etc/vpn-user-portal/config.php:

    'Db' => [
        'dbDsn' => 'pgsql:host=db.example.org;dbname=vpn;user=vpn;password=s3cr3t',
    ],

Replace host, dbname, user and password with the values you obtained from your database administrator.

MariaDB/MySQL#

Make sure you have the php-mysqlnd package installed:

$ sudo dnf install php-mysqlnd
$ sudo systemctl restart php-fpm

In /etc/vpn-user-portal/config.php:

    'Db' => [
        'dbDsn' => 'mysql:host=db.example.org;dbname=vpn',
        'dbUser' => 'vpn',
        'dbPass' => 's3cr3t',
    ],

Replace host, dbname, dbUser and dbPass with the values you obtained from your database administrator.

If you connect to your MySQL or MariaDB server over TLS you can use the tlsCa option to specify the CA. If your server also uses mTLS (client certificate) authentication, you can use the tlsCert and tlsKey fields as well. If you are using mTLS you can remove the dbPass field as it won’t be used. Connecting over TLS is available in vpn-user-portal >= 3.4.3.

Database Info#

You can show the current status of your database, this will tell you whether the configuration was done properly, i.e. we are able to connect to the database and whether initialization or migration is required, e.g.:

$ sudo /usr/libexec/vpn-user-portal/db
Database Driver        : pgsql
Current Schema Version : 2024052801
Latest Schema Version  : 2024052801
Status                 : **OK**

Database Initialization#

NOTE: if you use PostgreSQL or MariaDB/MySQL make sure the database referenced by dbname in your configuration exists.

To initialize the database:

$ sudo /usr/libexec/vpn-user-portal/db --init

You can temporary override your database configuration with --dsn, --user, --pass options in case you need different credentials to perform a database initialization.

Database Migration#

Updates to the VPN software MAY require database migrations. This will be indicated in the release notes of newer versions. If a migration is needed, but not performed the VPN portal will show an error message.

$ sudo /usr/libexec/vpn-user-portal/db
Database Driver        : pgsql
Current Schema Version : 2023011801
Latest Schema Version  : 2024052801
Status                 : **Migration Required** (use --migrate)

You can perform the migration automatically:

$ sudo /usr/libexec/vpn-user-portal/db --migrate

You can temporary override your database configuration with --dsn, --user, --pass options in case you need different credentials to perform a database migration.

Database Rollback#

NOTE: database rollbacks are only supported in vpn-user-portal >= 3.7.0.

If you ever need, you can also perform a rollback, i.e. return to an earlier version of the database. For this, you can use the --version flag together with --migrate, for example:

$ sudo /usr/libexec/vpn-user-portal/db --migrate --version 2023011801

Manual Initialization & Migration#

If you want to perform every step manually, you need to look in /usr/share/vpn-user-portal/schema for the SQL schema files. The latest version available there SHOULD be used for initialization, the files with _ are the migration queries that need to be run to migrate from one version to the next.

NOTE: make sure you also create/update the version table that contains the current version, e.g.:

CREATE TABLE version (current_version TEXT NOT NULL);
INSERT INTO version VALUES('2021123001');