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:
- The remote session store (
<RemoteSessionStore>), see Session management. - The out-of-context data store (
<RemoteOutOfContextDataStore>), see Shared out-of-context data. - The database store of OpenID Connect back-channel logout (
<RemoteStore>in<BackChannelLogout>), see Back-channel logout store.
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.
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.
<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:
<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
connectionPoolRefattribute. -
connectionUrl(String, required)The JDBC URL of the database. The URL must start with
jdbc:mariadb://for MariaDB and withjdbc:postgresql://for PostgreSQL.Examplejdbc:mariadb://authdb:3306/nevisauth?autocommit=truejdbc:postgresql://authdb:5432/nevisauthFor more information, see the connection parameter documentation for MariaDB and PostgreSQL.
infonevisAuth relies on the autocommit feature of the database.
- With MariaDB, enable autocommit on the database level, or add the query parameter
autocommit=trueto the JDBC URL. - PostgreSQL has autocommit enabled by default.
For TLS connections, append TLS parameters to the JDBC URL. MariaDB uses the
sslModeparameter (camelCase). For one-way TLS (server certificate verified against a CA):MariaDB one-way TLS examplejdbc:mariadb://server.example.com:3306/database?sslMode=verify-caFor several hosts with sequential failover:
MariaDB sequential failover with one-way TLS examplejdbc:mariadb:sequential//server1.example.com:3306,server2.example.com:3306/database?sslMode=verify-caFor all TLS options, see the MariaDB Connector/J TLS documentation.
PostgreSQL uses the lowercase
sslmodeparameter and requiresssl=true. For one-way TLS:PostgreSQL one-way TLS examplejdbc:postgresql://server.example.com:5432/database?ssl=true&sslmode=verify-caFor all TLS options, see the PostgreSQL JDBC SSL documentation.
- With MariaDB, enable autocommit on the database level, or add the query parameter
-
connectionUser(String, required)The user that accesses the data. This user needs
SELECT,INSERT,UPDATE, andDELETErights on the tables of the features that use the connection pool. You can use the same syntax as for passwords, for examplepipe://echo $SYNC_USERto 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
CREATErights. If not set, nevisAuth usesconnectionUser. nevisAuth uses this user only ifconnectionAutomaticDbSchemaSetupistrue. We recommend separate users for the schema and for the data. -
connectionSchemaPassword(String, optional)The password of
connectionSchemaUser. If not set, nevisAuth usesconnectionPassword. -
connectionAutomaticDbSchemaSetup(Boolean, optional)Default value:
trueIf
true, the features that use the connection pool create their missing tables at startup (CREATE TABLE IF NOT EXISTS). Set it tofalseif you create the tables yourself, or if the schema user has noCREATErights. -
connectionMaxPoolSize(Integer, optional)Default value:
20The 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
connectionMaxPoolSizeThe 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:
30000The 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
connectionMinPoolSizeis lower thanconnectionMaxPoolSize.