Skip to main content
Version: 9.2605.x RR

Database connection pools

nevisAuth connects to its databases through shared, named database connection pools. You configure each connection pool once, and the database features of nevisAuth reference it by name. The following features use a connection pool:

In most setups, all features use the same database. Configure one connection pool, and reference it from each feature. The credentials are then in one place, and one pool size limits the connections of each nevisAuth instance. If a feature needs its own connections, for example to isolate its load, configure a second connection pool for it.

info

The shared database connection pools are available as of nevisAuth 9.2611.0. They replace the connection settings that each feature had in earlier versions. For the migration, see Migrating to nevisAuth 9.2611.

Configuring database connection pools​

Declare the connection pools in a <ConnectionPools> element in <esauth-server>, before <SessionCoordinator>. Each <DatabaseConnectionPool> element has a unique name. A feature references the connection pool with its connectionPoolRef attribute.

One connection pool for all database features
<esauth-server>
<ConnectionPools>
<DatabaseConnectionPool name="MainDb"
connectionUrl="jdbc:postgresql://db:5432/nevisauth"
connectionUser="nevisauth"
connectionPassword="secret"
connectionMaxPoolSize="30"/>
</ConnectionPools>

<SessionCoordinator ...>
<LocalSessionStore .../>
<RemoteSessionStore connectionPoolRef="MainDb" reaperPeriod="60" syncThreads="5"/>
<BackChannelLogout gracePeriod="300">
<RemoteStore connectionPoolRef="MainDb"/>
</BackChannelLogout>
</SessionCoordinator>

<RemoteOutOfContextDataStore connectionPoolRef="MainDb" reaperPeriod="60"/>
...
</esauth-server>

The following example isolates the out-of-context data store in a second connection pool:

Two connection pools
<ConnectionPools>
<DatabaseConnectionPool name="MainDb"
connectionUrl="jdbc:postgresql://db:5432/nevisauth"
connectionUser="nevisauth"
connectionPassword="secret"/>
<DatabaseConnectionPool name="OocdDb"
connectionUrl="jdbc:postgresql://db:5432/nevisauth"
connectionUser="nevisauth"
connectionPassword="secret"
connectionMaxPoolSize="10"/>
</ConnectionPools>

Sizing database connection pools​

Each nevisAuth instance opens up to connectionMaxPoolSize connections for each connection pool. The total number of connections to the database is therefore the sum of connectionMaxPoolSize of all connection pools, multiplied by the number of nevisAuth instances. Verify that the connection limit of your database allows this number.

Connection pool attributes​

The <DatabaseConnectionPool> element has the following attributes.

  • name (String, required)

    The unique name of the connection pool. The database features reference the connection pool with this name in their connectionPoolRef attribute.

  • connectionUrl (String, required)

    The JDBC URL of the database. The URL must start with jdbc:mariadb:// for MariaDB and with jdbc:postgresql:// for PostgreSQL.

    Example
    jdbc:mariadb://authdb:3306/nevisauth?autocommit=true
    jdbc:postgresql://authdb:5432/nevisauth

    For more information, see the connection parameter documentation for MariaDB and PostgreSQL.

    info

    nevisAuth relies on the autocommit feature of the database.

    • With MariaDB, enable autocommit on the database level, or add the query parameter autocommit=true to the JDBC URL.
    • PostgreSQL has autocommit enabled by default.

    For TLS connections, append TLS parameters to the JDBC URL. MariaDB uses the sslMode parameter (camelCase). For one-way TLS (server certificate verified against a CA):

    MariaDB one-way TLS example
    jdbc:mariadb://server.example.com:3306/database?sslMode=verify-ca

    For several hosts with sequential failover:

    MariaDB sequential failover with one-way TLS example
    jdbc:mariadb:sequential//server1.example.com:3306,server2.example.com:3306/database?sslMode=verify-ca

    For all TLS options, see the MariaDB Connector/J TLS documentation.

    PostgreSQL uses the lowercase sslmode parameter and requires ssl=true. For one-way TLS:

    PostgreSQL one-way TLS example
    jdbc:postgresql://server.example.com:5432/database?ssl=true&sslmode=verify-ca

    For all TLS options, see the PostgreSQL JDBC SSL documentation.

  • connectionUser (String, required)

    The user that accesses the data. This user needs SELECT, INSERT, UPDATE, and DELETE rights on the tables of the features that use the connection pool. You can use the same syntax as for passwords, for example pipe://echo $SYNC_USER to read the user from an environment variable. For more information, see Passwords in the configuration.

  • connectionPassword (String, required)

    The password of connectionUser. This attribute accepts the standard encryption syntax, see Passwords in the configuration.

  • connectionSchemaUser (String, optional)

    The user that creates the tables in the database. This user needs CREATE rights. If not set, nevisAuth uses connectionUser. nevisAuth uses this user only if connectionAutomaticDbSchemaSetup is true. We recommend separate users for the schema and for the data.

  • connectionSchemaPassword (String, optional)

    The password of connectionSchemaUser. If not set, nevisAuth uses connectionPassword.

  • connectionAutomaticDbSchemaSetup (Boolean, optional)

    Default value: true

    If true, the features that use the connection pool create their missing tables at startup (CREATE TABLE IF NOT EXISTS). Set it to false if you create the tables yourself, or if the schema user has no CREATE rights.

  • connectionMaxPoolSize (Integer, optional)

    Default value: 20

    The maximum number of connections in the connection pool. If you increase this value, see Sizing database connection pools.

  • connectionMinPoolSize (Integer, optional)

    Default value: the value of connectionMaxPoolSize

    The minimum number of idle connections in the connection pool. If this value is lower than connectionMaxPoolSize, nevisAuth creates the additional connections on demand.

  • connectionTimeout (Integer, milliseconds, optional)

    Default value: 30000

    The maximum time that nevisAuth waits for a connection from the connection pool.

  • connectionMaxLifeTime (Integer, milliseconds, optional)

    Default value: 1800000 (30 minutes)

    The maximum lifetime of a connection in the connection pool.

  • connectionMaxIdleTime (Integer, milliseconds, optional)

    Default value: 600000 (10 minutes)

    The maximum time that an idle connection stays in the connection pool. This applies only if connectionMinPoolSize is lower than connectionMaxPoolSize.