The JDBC data source supports a number of backing SQL databases out of the box. Depending on driver and connection string the JDBC data source will change the dialect to match the target system.
Before configuring a data source see the details for each vendor below.
Curity is shipped with initialization scripts for each supported database type. These contain the table definitions and indexing needed for a standard Curity setup. Depending on target system and target usage additional indexing might be required. Please contact Curity support if you are uncertain if additional setup is required.
The table definitions are found in the installation under <IDSVR>/etc. Each target system is represented by it’s own setup script:
<IDSVR>/etc
Make sure these are executed on the target database before connecting Curity.
Note
Read more about multi-tenancy
Curity relies on external maintenance. Therefore it is important to setup janitor procedures before going into production. The following tables need to be maintained and cleaned up.
The interval for cleanup and how old data needs to be before cleaning depends on the data management policy of the organization. Some organizations are required to store data longer and others are prevented from doing so. Please consult with your local database administrators or data officers when setting a maintenance strategy.
See each vendor type below for examples on how to clean up the DB.
Important
Cleaning up tokens and delegations can have a legal impact since possible auditable data is lost. Therefore consult your organisations policy on data storage when deciding how to clean data.
As the list of databases supported by Curity has grown, so has the need to enforce common behaviour in some of the areas the SQL standard has left to the various implementations to decide on. One such item is case sensitivity, where the SQL standard mandates (unquoted) identifiers to be treated in a case insensitive manner. This however does not translate well when used in case sensitive contexts like tokens (or any attributes stored in JSON) where case sensitivity is mandated. For this reason as well as to avoid special treatment of certain databases (some identifiers used by Curity have special meaning in some databases but not in others) Curity consistently uses quoting around identifiers, both in the table definitions provided as well as the queries sent from Curity to the databases it communicates with.
The SQL datasource is based on a JDBC datasource. This means that at a minimum it requires a configured connection-string, driver, username and password.
connection-string
driver
username
password
Tip
The full configuration reference is found in the configuration section
By default, the server uses a connection pool to manage the connections to a SQL backend. The default pool settings provide a starting point for many deployments, but there are many options to tweak the behaviour of the connection pool, for example the minimum and maximum size of the connection pool, connection time-out settings, etc. See the data-source reference for a full overview of the configuration options.
To configure multi-tenancy at authentication profile level, the Multi Tenant Mode must first be enabled on the data source. Read more about multi-tenancy
Multi Tenant Mode
Each SQL datasource type also has the option to configure a number of custom queries, that you can use to integrate a custom database schema with the Curity Identity Server. These queries are listed in the table below.
:subjectId
:password
:subject
* Custom credential queries are only supported in the default credentials mode.
*
The custom attribute query can be useful when certain attributes for the account are found in another table or database. Setup a new data source with a custom query for the attributes, and use it in a transformer or token procedure to retrieve the extra details about the user.
A SQL datasource can be configured to collect Audit Events by toggling the use-for-audit configuration parameter. Only a single SQL datasource can have this feature enabled at a time.
use-for-audit
If you are using PostgreSQL or CockroachDB datasource to log audit events, please see PostgreSQL and CockroachDB Audit Log Configuration.
Depending on the backend type the clustering can look different. However the most common setup is to use an Active-Active cluster where the data is replicated in real-time. Curity should be configured to either communicate with a load balancer for the DB or to load balance using the connection string configuration. It is not recommended to use a round robin load-balancing since that is known to cause race conditions in Active-Active clusters. A better approach is to use a failover setup where one DB node gets more traffic and then Curity fails over to the secondary nodes when needed. See the data base vendor’s documentation for how to setup the failover and loadbalancing using the JDBC connection string.
The data source can be configured to expose metrics of the underlying connection pool via the enable-pool-metrics setting. The available metrics are described in the following table:
enable-pool-metrics
All metrics include the ds_id label and are exposed alongside other node metrics, as described in Monitoring.
ds_id
By default, the query used to retrieve credential data checks the account status, and no data is returned for inactive accounts. In such cases, the credential verification done by the Curity Identity Server always fails.
In recent versions of the Curity Identity Server the database schema was subject to some changes to better fit scenarios where user accounts and their passwords are stored in different databases/data-sources and to support new features around credential management (namely password policies). The JDBC data source can be configured with a “credentials mode”, which determines the expected schema and how the data source interacts with the database for credential data access. If nothing is configured, the previous behavior is kept.
The possible modes are described in the following table:
credentials-in-accounts-table
accounts
standard
credentials
credentials-migration
Important notes:
When using MySQL or MariaDB the driver needs to be downloaded from Oracle or MariaDB.
$IDSVR_HOME/etc/mysql-create_database.sql
$IDSVR_HOME/lib/plugins/data.access.jdbc
Minimal configuration:
The connection string is not optimized and only provided as an example.
If done infrequently these procedures should limit the number of entries they operate on since they often lock the table.
1 2
USE se_curity_store; DELETE FROM nonces WHERE status = 'used';
USE se_curity_store; DELETE FROM sessions WHERE expires < unix_timestamp(now()))
USE se_curity_store; DELETE FROM tokens WHERE expires < unix_timestamp(now()))
USE se_curity_store; DELETE FROM delegations WHERE expires < unix_timestamp(now()))
Microsoft SQL server can be run either as on premise or in Azure as a cloud instance. Both are configured with the same drivers.
The driver is shipped with Curity, but if you need to upgrade the driver to a later version it is found in $IDSVR_HOME/lib/plugins/data.access.jdbc. Simply repace the existing jar with the new one. It is important to remove the current driver so that there only exists one. Then restart the nodes.
To configure the following is a minimal non-optimized configuration for SQL Server.
The connection string is not optimized and only provided as an example. We recommend that you consult the vendors documentation when configuring the JDBC driver.
Curity uses VARCHAR for compatibility reasons. There are some known performance issues whith prepared statements in the SQLServer driver from Microsoft, where the server converts unicode strings to ascii-strings before running the statement. This causes indexes to not be used as expected and can cause table scans. To avoid this the jdbc driver can be configured with sendStringParametersAsUnicode=false. This works well for western european languages, but is not tested with asian or non-latin character languages. If that is needed we suggest that to use another database or consult Microsoft.
sendStringParametersAsUnicode=false
Enable sendStringParametersAsUnicode=false in the jdbc driver settings to avoid performance issues with indexes.
DELETE TOP (10000) FROM se_curity_store.dbo.sessions WHERE expires < DATEDIFF(s, '1970-01-01 00:00:00', CURRENT_TIMESTAMP)
DELETE TOP (10000) FROM se_curity_store.dbo.tokens WHERE expires < DATEDIFF(s, '1970-01-01 00:00:00', CURRENT_TIMESTAMP)
DELETE TOP (10000) FROM se_curity_store.dbo.delegations WHERE expires < DATEDIFF(s, '1970-01-01 00:00:00', CURRENT_TIMESTAMP)
PostgreSQL and compatible databases such as CockroachDB can be configured with the driver shipped with Curity.
If you need to upgrade the driver to a later version it is found in $IDSVR_HOME/lib/plugins/data.access.jdbc. Simply replace the existing jar with the new one. It is important to remove the current driver so that there only exists one. Then restart the nodes.
To configure the following is a minimal non-optimized configuration for PostgreSQL and CockroachDB.
If using CockroachDB, a flag needs to be added to the connection-string cockroach=true, i.e. jdbc:postgresql://POSTGRES_HOST:POSTGRES_PORT/se_curity_store;cockroach=true.
cockroach=true
jdbc:postgresql://POSTGRES_HOST:POSTGRES_PORT/se_curity_store;cockroach=true
When using Oracle the driver needs to be downloaded from Oracle.
$IDSVR_HOME/etc/oracle-create_database.sql
1
DELETE FROM "nonces" WHERE "status" = 'used';
DELETE FROM "sessions" WHERE "expires" < (SELECT ROUND((CURRENT_DATE - date '1970-01-01' ) * 60 * 60 * 24) FROM dual)
DELETE FROM "tokens" WHERE "expires" < (SELECT ROUND((CURRENT_DATE - date '1970-01-01' ) * 60 * 60 * 24) FROM dual)
DELETE FROM "delegations" WHERE expires < (SELECT ROUND((CURRENT_DATE - date '1970-01-01' ) * 60 * 60 * 24) FROM dual)
The Oracle driver internally requires serializability of some of its classes. Since Curity restricts which Java types can be serialized by default, this will break the Oracle driver. To fix this, Java must be told to allow serializability of the Oracle driver’s classes. This can be done by including the Java system property se.curity:identity-server:serialFilter=oracle.jdbc.** when starting the Curity Identity Server, which can be done by setting the JAVA_OPTS environment variable (e.g., JAVA_OPTS=-Dse.curity:identity-server:serialFilter=oracle.jdbc.** idsvr)
se.curity:identity-server:serialFilter=oracle.jdbc.**
JAVA_OPTS
JAVA_OPTS=-Dse.curity:identity-server:serialFilter=oracle.jdbc.** idsvr
See enable Java serialization for more details.
HsqlDB is a file based database that cannot be used in production. It is shipped as a small getting started DB only. To setup HsqlDB on a new system use the following settings:
Curity will replace the inline variable in the jdbc string with the file based db delivered with the system.
The database does not support running Curity in a clustered mode, only single node operation is supported.
Warning
HsqlDB cannot be used in production. It is available for development purposes only.