MySQL configuration details - maduvena/jans-docs GitHub Wiki

MySQL as choice for Persistence

While installing the Jans Server, the administrator will be presented a menu of the possible choices for the persistence layer (LDAP, MySQL, Couchbase, Postgres etc). MySQL database can be installed in 2 possible ways and the administrator will be prompted to make this choice during installation:

  1. Locally :
    The administrator can configure:
  • RDBM username
  • RDBM password
  • RDBM database name
  1. Remotely :
    The administrator will be prompted to configure:
  • RDBM username
  • RDBM password
  • RDBM port
  • RDBM database
  • RDBM host

Database configuration file name

The database configurations are stored in /etc/gluu/conf/jans-sql.properties and contain the following information:

db.schema.name=jansdb

connection.uri=jdbc:mysql://localhost:3306/jansdb?enabledTLSProtocols=TLSv1.2

connection.driver-property.serverTimezone=UTC+0000
# Prefix connection.driver-property.key=value will be coverterd to key=value JDBC driver properties
#connection.driver-property.driverProperty=driverPropertyValue

#connection.driver-property.useServerPrepStmts=false
connection.driver-property.cachePrepStmts=false
connection.driver-property.cacheResultSetMetadata=true
connection.driver-property.metadataCacheSize=500
#connection.driver-property.prepStmtCacheSize=500
#connection.driver-property.prepStmtCacheSqlLimit=1024

auth.userName=vi....bo
auth.userPassword=qphWF1h....XmUAvn9g==

# Password hash method
password.encryption.method=SSHA-256

# Connection pool size
connection.pool.max-total=40
connection.pool.max-idle=15
connection.pool.min-idle=5

# Max time needed to create connection pool in milliseconds
connection.pool.create-max-wait-time-millis=20000

# Max wait 20 seconds
connection.pool.max-wait-time-millis=20000

# Allow to evict connection in pool after 30 minutes
connection.pool.min-evictable-idle-time-millis=1800000

binaryAttributes=objectGUID
certificateAttributes=userCertificate

Change password for user jans :

  • ALTER USER 'jans'@'localhost' IDENTIFIED BY 'TopSecret';
  • GRANT ALL PRIVILEGES ON jansdb.* TO 'jans'@'localhost';

Create new user claims to jansPerson:

  • You can add additional attributes to jansPerson table and use them. This will be similar to LDAP where DB stores all user attributes in one entry. Additional attributes will not affect the server functionality.
  • Ensure you restart services after DB schema modification

Modify column size of jansPerson :

Say we want to increase the size of mail field to 144. Do the following:

  • a. Modify column size -
ALTER TABLE `jansdb`.`jansPerson` CHANGE COLUMN `mail` `mail` VARCHAR(144) NULL DEFAULT NULL ;
  • b. Drop indexes and re-create -
ALTER TABLE jansdb.jansPerson DROP INDEX `jansPerson_CustomIdx2`;
ALTER TABLE jansdb.jansPerson ADD INDEX `jansPerson_CustomIdx2` ((lower(`mail`)));
  • c. Ensure you restart services after DB schema modification

LDAP to MySQL Migration Script

This script migrates data from ldap to MySQL.

  1. To use this script, firt install python3-ldap module apt install python3-ldap

  2. Install MySQL Server, create a database (namely jansdb), add a user (namely jans) and give grant previlages to user jans on jansdb

  3. Download script to /opt/jans/jans-setup

wget https://raw.githubusercontent.com/JanssenProject/jans/jans-linux-setup-ldap2mysql/jans-linux-setup/tools/ldap2mysql/ldap2mysql.py -O /opt/jans/jans-setup/ldap2mysql.py
  1. Execute
cd /opt/jans/jans-setup`
python3 ldap2mysql.py -remote-rdbm=mysql -rdbm-user=jans -rdbm-password=<password> -rdbm-db=jansdb -rdbm-host=<rdbm_host>
⚠️ **GitHub.com Fallback** ⚠️