Change the Default Datasources for the Registry Data

Note

To deploy this update into production, you need to have a paid subscription. If you do not have a paid subscription, you can use this feature with the next version of WSO2 Identity Server when it is released.

If you have a paid subscription, this capability can be made available by applying update level 81 for WSO2 Identity Server 7.0.0 using the WSO2 Update Tool. For more information on updating WSO2 Identity Server using the WSO2 Update Tool, see WSO2 Updates.

WSO2 Identity Server comprises three different registry repositories.

1. Local Repository: Stores configuration and runtime data that is local to the server.

2. Configuration Repository: Stores product-specific configurations.

3. Governance Repository: This stores configuration and data that are shared across the whole platform. This typically includes services, service descriptions, endpoints, or data sources.

These repositories use the default datasources as follows.

• WSO2_CARBON_DB: Used by the local repository.
• WSO2_SHARED_DB: Used by the configuration and governance repositories.

They can be changed to utilize alternate datasources as described below.

Configure the Datasource

Following are the sample configuration for each database type.

PostgreSQL

1. Configure the <IS-HOME>/repository/conf/deployment.toml file.

   [[datasource]]
   id="WSO2CONFIG_DB"
   url = "jdbc:postgresql://localhost:5432/gregdb"
   username = "regadmin"
   password = "regadmin"
   driver = "org.postgresql.Driver"
   jmx_enable=false
   

2. Execute the database scripts.

   Execute the scripts in the <IS-HOME>/dbscripts/postgresql.sql file against the created database.

3. Download the Postgres JDBC driver for the version you are using and copy it to the <IS_HOME>/repository/components/lib folder.

MySQL

1. Configure the <IS-HOME>/repository/conf/deployment.toml file.

   [[datasource]]
   id="WSO2CONFIG_DB"
   url = "jdbc:mysql://localhost:3306/IAMtest?useSSL=false"
   username = "root"
   password = "root"
   driver = "com.mysql.jdbc.Driver"
   jmx_enable=false
   

2. Execute the database scripts.

   Execute the scripts in the <IS-HOME>/dbscripts/mysql.sql file against the created database.

3. Download the MySQL JDBC driver for the version you are using and copy it to the <IS_HOME>/repository/components/lib folder.

DB2

1. Configure the <IS-HOME>/repository/conf/deployment.toml file.

   [[datasource]]
   id="WSO2CONFIG_DB"
   url = "jdbc:db2://192.168.108.31:50000/test"
   username = "db2inst1"
   password = "db2inst1"
   driver = "com.ibm.db2.jcc.DB2Driver"
   jmx_enable=false
   

2. Execute the database scripts.

   Execute the scripts in the <IS-HOME>/dbscripts/db2.sql file against the created database.

3. Download the DB2 JDBC driver for the version, you are using and copy it to the <IS_HOME>/repository/components/lib folder.

MSSQL

1. Configure the <IS-HOME>/repository/conf/deployment.toml file.

   [[datasource]]
   id="WSO2CONFIG_DB"
   url = "jdbc:sqlserver://localhost:1433;databaseName=test;SendStringParametersAsUnicode=false"
   username = "sa"
   password = "pass#word2"
   driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
   jmx_enable=false
   

2. Execute the database scripts.

   Execute the scripts in the <IS-HOME>/dbscripts/mssql.sql file against the created database.

3. Download the MS SQL JDBC driver for the version you are using and copy it to the <IS_HOME>/repository/components/lib folder.

Oracle

1. Configure the <IS-HOME>/repository/conf/deployment.toml file.

   [[datasource]]
   id="WSO2CONFIG_DB"
   url = "jdbc:oracle:thin:@localhost:1521/ORCLPDB"
   username = "IS590Test"
   password = "ora12c"
   driver = "oracle.jdbc.OracleDriver"
   jmx_enable=false
   

2. Execute the database scripts.

   Execute the scripts in the <IS-HOME>/dbscripts/oracle.sql file against the created database.

3. Download the Oracle JDBC driver for the version you are using and copy it to the <IS_HOME>/repository/components/lib folder.

---

Advanced database configurations

Apart from the above basic configurations, WSO2 Identity Server supports advanced database configurations. Add the following configurations to the <IS_HOME>/repository/conf/ deployment.toml file under the corresponding [[datasource]] tag.

PostgreSQLMySQLDB2MSSQLOracle

pool_options.maxActive = "80"
pool_options.maxWait = "60000"
pool_options.minIdle = "5"
pool_options.testOnBorrow = true
pool_options.validationQuery="SELECT 1; COMMIT"
pool_options.validationInterval="30000"
pool_options.defaultAutoCommit=false

pool_options.maxActive = "80"
pool_options.maxWait = "60000"
pool_options.minIdle = "5"
pool_options.testOnBorrow = true
pool_options.validationQuery="SELECT 1"
pool_options.validationInterval="30000"
pool_options.defaultAutoCommit=false

pool_options.maxActive = "80"
pool_options.maxWait = "360000"
pool_options.minIdle = "5"
pool_options.testOnBorrow = true
pool_options.validationQuery="SELECT 1"
pool_options.validationInterval="30000"
pool_options.defaultAutoCommit=false

pool_options.maxActive = "80"
pool_options.maxWait = "60000"
pool_options.minIdle = "5"
pool_options.testOnBorrow = true
pool_options.validationQuery="SELECT 1"
pool_options.validationInterval="30000"
pool_options.defaultAutoCommit=false

pool_options.maxActive = "80"
pool_options.maxWait = "60000"
pool_options.minIdle = "5"
pool_options.testOnBorrow = true
pool_options.validationQuery="SELECT 1 FROM DUAL"
pool_options.validationInterval="30000"
pool_options.defaultAutoCommit=false

The elements in the above configuration are described below:

maxActive	This is the maximum number of active connections that can be allocated at the same time from this pool. Enter any negative value to denote an unlimited number of active connections.
maxWait	This is the maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception. You can enter zero or a negative value to wait indefinitely.
minIdle	The minimum number of active connections that can remain idle in the pool without extra ones being created. Enter zero to create none.
testOnBorrow	This indicates whether objects will be validated before being borrowed from the pool. If the object fails to be validated, it will be dropped from the pool and another attempt will be made to borrow another.
defaultAutoCommit	Indicates whether to commit database changes automatically or not
validationInterval	This is the indication to avoid excess validation and only run validation after the specified frequency (time in milliseconds). If a connection is due for validation, but has been validated previously within this interval, it will not be validated again.
defaultAutoCommit	This property is not applicable to the carbon database in WSO2 Identity Server because auto committing is usually handled at the code level, i.e., the default auto commit configuration specified for the RDBMS driver will be effective instead of this property element. Typically, auto committing is enabled for RDBMS drivers by default. When auto committing is enabled, each SQL statement will be committed to the database as an individual transaction, as opposed to committing multiple statements as a single transaction.

Info

For more information on other parameters that can be defined in the <IS_HOME>/repository/conf/deployment.toml file, see Tomcat JDBC Connection Pool.

Support for case-sensitive usernames

Usernames in WSO2 Identity Server are case-insensitive by default. If you wish to enable case-sensitive usernames, configure the following properties.

To enable the case-sensitivity for the primary user store, open the deployment.toml file found in the <IS-HOME>/repository/conf/ directory and add the following configurations to the primary user store.

[user_store.properties]
CaseInsensitiveUsername = false
UseCaseSensitiveUsernameForCacheKeys = false

For secondary user stores, add the following configurations to the <userstore>.xml file found in the <IS_HOME>/repository/deployment/server/userstores directory.

<Property name="CaseInsensitiveUsername">false</Property>
<Property name="UseCaseSensitiveUsernameForCacheKeys">false</Property>

The database indexes are created using LOWER() functions to support case-insensitive usernames for the related tables. Therefore, remove the LOWER() functions from the related index creation queries in the scripts at <IS_HOME>/dbscripts/.

Eg:

CREATE UNIQUE INDEX INDEX_UM_USERNAME_UM_TENANT_ID ON UM_USER(LOWER(UM_USER_NAME), UM_TENANT_ID);

---

Configure the Registry Databases

The following configurations can be added under the [registry] section in the <IS-HOME>/repository/conf/deployment.toml file.

[registry]
local_datasource = "jdbc/<id-of-the-datasource>"
gov_datasource = "jdbc/<id-of-the-datasource>"
config_datasource = "jdbc/<id-of-the-datasource>"

Configuration	Description
local_datasource	This is the jndi config name of the datasource for the local repository. Here, this would be "jdbc/<id>" where id is the value configured in the datasource configurations.
gov_datasource	This is the jndi config name of the datasource configured for the governance repository. Here, this would be "jdbc/<id>" where id is the value configured in the datasource configurations.
config_datasource	This is the jndi config name of the datasource configured for the configuration repository. Here, this would be "jdbc/<id>" where id is the value configured in the datasource configurations.

It is only necessary to add the configuration for the datasource which is being changed. For example, if you wish to change only the governance and configuration datasources, only the gov_datasource and config_datasource configurations would be necessary as shown below.

[registry]
gov_datasource = "jdbc/<id-of-the-datasource>"
config_datasource = "jdbc/<id-of-the-datasource>"

Sample Config

If you have correctly configured, the deployment.toml file should have an entry similar to the following config.

The following sample configuration is for separate MSSQL governance and config databases.

[registry]
gov_datasource = "jdbc/WSO2GOV_DB"
config_datasource = "jdbc/WSO2CONFIG_DB"

[[datasource]]
id="WSO2GOV_DB"
url = "jdbc:sqlserver://localhost:1433;databaseName=test1;SendStringParametersAsUnicode=false"
username = "sa"
password = "pass#word2"
driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jmx_enable=false
pool_options.maxActive = "80"
pool_options.maxWait = "60000"
pool_options.minIdle = "5"
pool_options.testOnBorrow = true
pool_options.validationQuery="SELECT 1"
pool_options.validationInterval="30000"
pool_options.defaultAutoCommit=false

[[datasource]]
id="WSO2CONFIG_DB"
url = "jdbc:sqlserver://localhost:1433;databaseName=test2;SendStringParametersAsUnicode=false"
username = "sa"
password = "pass#word2"
driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jmx_enable=false
pool_options.maxActive = "80"
pool_options.maxWait = "60000"
pool_options.minIdle = "5"
pool_options.testOnBorrow = true
pool_options.validationQuery="SELECT 1"
pool_options.validationInterval="30000"
pool_options.defaultAutoCommit=false