Database Setup

Typical use cases for database setup

Kubernetes environment

Configure the nevisFIDO Instance pattern with the Managed nevisFIDO Database.

Non-Kubernetes environment

• Create the database object and the users in the database server manually, using the following SQL scripts

• nevisFIDO by default is configured to use automatic-db-schema-setup

• nevisFIDO on startup creates the database tables in the previously manually created database

Manual steps for the database setup

To be able to create the tables and define the schema, the schema user must have CREATE privileges in the database. To be able to read, update and remove the session information, the user must have SELECT, INSERT, UPDATE and DELETE privileges in the database.

The following sample configuration uses SQL commands to create a database and two separate users and grant them the required privileges. In the sample, the database where you store the session information is called nevisfido. The database server and nevisFIDO are located on the same machine. The created users match the users from the previous sample configuration (SQL session repository).

These scripts are not necessary with nevisAdmin4 and Kubernetes deployment there the DB schema is set up automatically.

MariaDB

CREATE DATABASE IF NOT EXISTS nevisfido;
CREATE USER 'data-user'@'localhost' IDENTIFIED BY 'secret';
CREATE USER 'schema-user'@'localhost' IDENTIFIED BY 'schema-secret';
GRANT SELECT, INSERT, UPDATE, DELETE ON nevisfido.* TO 'data-user'@'localhost';
GRANT CREATE, ALTER ON nevisfido.* TO 'schema-user'@'localhost';
FLUSH PRIVILEGES;

The statements grant access from localhost only.

For test setups, you might connect remotely. In that case, you can use % instead of localhost to enable connection from anywhere. Do not use the approach in a production environment, it is not secure.

CREATE DATABASE IF NOT EXISTS nevisfido;
CREATE USER 'data-user'@'%' IDENTIFIED BY 'secret';
CREATE USER 'schema-user'@'%' IDENTIFIED BY 'schema-secret';
GRANT SELECT, INSERT, UPDATE, DELETE ON nevisfido.* TO 'data-user'@'%';
GRANT CREATE, ALTER ON nevisfido.* TO 'schema-user'@'%';
FLUSH PRIVILEGES;

Timezone setup

Timezone database should be initialized, in case the SELECT * FROM mysql.time_zone_name; returns nothing than this means the timezone database was not yet initialized. To fix that you have to run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p.

PostgreSQL

CREATE USER "schema-user" WITH encrypted password 'password';
CREATE USER "data-user" WITH encrypted password 'password';

CREATE DATABASE nevisfido
 WITH
OWNER = "schema-user";

ALTER ROLE "schema-user" IN DATABASE nevisfido SET search_path TO "schema-user";
ALTER ROLE "data-user" IN DATABASE nevisfido SET search_path TO "schema-user";

\connect nevisfido "schema-user";
CREATE SCHEMA "schema-user" AUTHORIZATION "schema-user";

GRANT USAGE ON SCHEMA "schema-user" to "data-user";
GRANT CONNECT ON DATABASE nevisfido TO "data-user";

ALTER DEFAULT PRIVILEGES FOR USER "schema-user" IN SCHEMA "schema-user" GRANT SELECT, INSERT, UPDATE, DELETE, TRIGGER ON TABLES TO "data-user";
ALTER DEFAULT PRIVILEGES FOR USER "schema-user" IN SCHEMA "schema-user" GRANT USAGE, SELECT ON SEQUENCES TO "data-user";
ALTER DEFAULT PRIVILEGES FOR USER "schema-user" IN SCHEMA "schema-user" GRANT EXECUTE ON FUNCTIONS TO "data-user";

Session Reaping

When the SQL backend session repository is in use, a session reaping mechanism cleans up old sessions in the database to prevent the table from growing unlimitedly in size.

Sessions are reaped after three times the timeout of the client session. For example, if the default client timeout for a registration operation is 30 seconds, the registration session will be reaped after 90 seconds.

For client session timeout values, refer to FIDO UAF Configuration.

Resilient SQL Session Repository using MariaDB

This chapter describes how to create a fault-tolerant setup for database- or network outages between nevisFIDO and MariaDB. (At least one database node must be available to prevent application failure.)

Use cases

On the primary DB node, failure connections will move to the secondary DB node when a 30 second timeout expires or immediately in case of an incoming request to the DB. You may experience increased response time for the duration of the switch.

Recovery connections will move back to the primary DB node once the connection maximum lifetime expires. The connection maximum lifetime is 30 minutes.

Implementation overview

Regular clustering solutions provide all fault tolerant features themselves. The connecting application is not aware of the resilient setup.

The suggested solution takes a different approach. In this setup, the connecting application becomes part of the resilient setup in terms of configuration.

There are two key features to achieve resilience:

1.) Connectivity (MariaDB JDBC driver) Configure a JDBC url where you define the DB nodes in priority order: jdbc:mariadb:sequential://host-db1:3306,host-db2:3306/nevisfido

2.) Data consistency (MariaDB replication) Configure Master-to-Master replication.

Replication is done by the database, the application and the JDBC driver are not aware of it.

Overview of database users

The replicated session store is managed by several database users to separate concerns. The creation of the users is explained below.

Step-by-step setup of the replicated session store

This chapters assumes that the Session Repository Configuration is completed and the tables used by nevisFIDO are created.

1.) Creation of the replication user:

    CREATE USER IF NOT EXISTS replication_user IDENTIFIED BY 'replicationpassword';
    GRANT REPLICATION SLAVE ON *.* TO replication_user;

2.) Creation of the binary logs user:

    CREATE USER IF NOT EXISTS binarylog_user IDENTIFIED BY 'binarylogspassword';
    GRANT SUPER ON *.* TO binarylog_user;

3.) Configuration of MariaDB service. To configure the MariaDB service, add the following entries to the file /etc/my.cnf as super user. The two configuration files (host-db1 and host-db2) differ at some points. The different lines are marked with (*).

3.1) Configure the MariaDB service on host-db1:

    [mariadb]
    # Enabling binary log
    log-bin
    # The ID of this master (*)
    server_id=1
    # The ID of the replication stream created by this master (*)
    gtid-domain-id=1
    # The basename and format of the binary log
    log-basename=mariadbmaster
    binlog-format=MIXED
    # Setting which tables are replicated
    replicate_wild_do_table="nevisfido.%"
    # Avoiding collisions of primary IDs for tables where the primary ID is auto-incremented
    # Auto-increment value
    auto_increment_increment=2
    # Auto-increment offset (*)
    auto_increment_offset=1
    # Suppressing duplicated keys errors for multi-master setup
    slave_exec_mode=IDEMPOTENT
    # Ignoring some data definition language errors
    slave-skip-errors=1452, 1062
    # Suppressing binary logs after a delay regardless of the replication status
    expire_logs_days=1
    # Maximum number of connections
    max_connections=1000
    # Size of each of the binary log files (default: 1GB)
    max_binlog_size=500M
    # Enabling writing to the DB in parallel threads for the replication
    slave-parallel-threads=10
    # enabling semi-synchronous replication
    rpl_semi_sync_master_enabled=ON
    rpl_semi_sync_slave_enabled=ON
    # change to READ COMMITTED
    transaction-isolation=READ-COMMITTED

3.2) Configure the MariaDB service on host-db2:

    [mariadb]
    # Enabling binary log
    log-bin
    # The ID of this master (*)
    server_id=2
    # The ID of the replication stream created by this master (*)
    gtid-domain-id=2
    # The basename and format of the binary log
    log-basename=mariadbmaster
    binlog-format=MIXED
    # Setting which tables are replicated
    replicate_wild_do_table="nevisfido.%"
    # Avoiding collisions of primary IDs for tables where the primary ID is auto-incremented
    # Auto-increment value
    auto_increment_increment=2
    # Auto-increment offset (*)
    auto_increment_offset=2
    # Suppressing duplicated keys errors for multi-master setup
    slave_exec_mode=IDEMPOTENT
    # Ignoring some data definition language errors
    slave-skip-errors=1452, 1062
    # Suppressing binary logs after a delay regardless of the replication status
    expire_logs_days=1
    # Maximum number of connections
    max_connections=1000
    # Size of each of the binary log files (default: 1GB)
    max_binlog_size=500M
    # Enabling writing to the DB in parallel threads for the replication
    slave-parallel-threads=10
    # enabling semi-synchronous replication
    rpl_semi_sync_master_enabled=ON
    rpl_semi_sync_slave_enabled=ON
    # change to READ COMMITTED
    transaction-isolation=READ-COMMITTED

3.3) Restart the MariaDB servers on both hosts:

    sudo service mariadb restart

Semi-synchronous replication By default, MariaDB uses asynchronous replication. To reach more consistency, it is recommended using semi-synchronous replication

The database configurations previously shown enable semi-synchronous replication with the following lines:

    rpl_semi_sync_master_enabled=ON
    rpl_semi_sync_slave_enabled=ON

MariaDB versions before 10.3.3 require the installation of plug-ins for semi-synchronous replication and are not supported.

Replication start To start the replication, log in as root into your MariaDB client and run the following commands

1.) On host-db1 (master is host-db2):

  CHANGE MASTER TO
  MASTER_HOST='host-db2',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='replicationpassword',
  MASTER_PORT=3306,
  MASTER_USE_GTID=current_pos,
  MASTER_CONNECT_RETRY=10;

2.) On host-db2 (master is host-db1):

  CHANGE MASTER TO
  MASTER_HOST='host-db1',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='replicationpassword',
  MASTER_PORT=3306,
  MASTER_USE_GTID=current_pos,
  MASTER_CONNECT_RETRY=10;

3.) On host-db1:

sql START SLAVE;

4.) On host-db2:

START SLAVE;

Additional setup

With the provided configuration (expire_logs_days=1 in the MariaDB settings), the system will automatically remove binary logs that are older than one day, even if the logs were not copied by the slave. This prevents the disk of the master node from being filled up in case the slave is down for a long time.

The automatic binary log removal takes place when

1. the master DB node starts,
2. the logs are flushed (nevisFIDO does not use this feature),
3. the binary log rotates, or 4. the binary logs are purged manually (see below).

This means that binary logs older than one day can exist, if none of the listed actions occurred recently.

Complementary to this expiration feature, MariaDB provides the possibility to manually purge the binary logs. The purge action removes all binary logs that were already copied by the slave. This allows a safe removal of the binary logs on a regular basis.

The nevisProxy package is delivered with an adaptable purging script, which is located at: /opt/nevisproxy/sql/mariadb/purgebinarylogs.sh

To use this script,

1. copy the script to a location of your choice, and 2. adapt it to your configuration.

The script takes care of both DB nodes, so that it only needs to be configured once.

If you use different database server nodes for nevisProxy and nevisAuth, you have to set them up separately.

You can schedule the script to run for example once per hour, with a cron job:

 0 * * * * /var/opt/nevisproxy/instance/conf/purgebinarylogs.sh # Absolute path of your adapted script

The provided configuration (max_binlog_size=500M in the MariaDB settings) allows you to configure the maximal size of the binary log files before rotating. The smaller the size, the more often rotations occur, which will slow down replication. The advantage is a more efficient purge process. The bigger the size, the less often rotations occur, but the disk may be filled with old logs.

According to our experiences, a size less than 8K does stop replication completely under heavy load, because the slave keeps rotating the logs.

Usually the slave stops replication if an error occurs. You can check the state of the slave with the following SQL command:

    show slave status\G

Showing the slave status requires the REPLICATION CLIENT grant.

If replication has stopped, usually the error that caused it will be displayed. First you should try to fix the error. If this is not possible, you can do a "forced" restart of the slave like this:

• On the master call (to display the current state of the master):

MariaDB [replicated_session_store]> show master status\G
*************************** 1. row ***************************
            File: mariadbmaster-bin.000131
        Position: 194630804
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

• On the slave (using the values returned by the call show master status\G on the master):

STOP SLAVE;
CHANGE MASTER TO
  MASTER_LOG_FILE='mariadbmaster-bin.000131',
  MASTER_LOG_POS=194630804;
START SLAVE;

In this way, the system will restart the slave, without replicating to the slave all sessions that occurred from the moment the replication has stopped until now.

Resilient SQL Session Repository using PostgreSQL

Not supported during the experimental phase.