docs(jans): allow for passing db properties dynamically

[moabu]

Due to different requirements for different clouds we need to be able to support modifying the db properties files in a dynamic way without needing to mount.

I suggest we add an env for each of the db properties file i.e OVERRIDE_MYSQL_PROPERTIES which would contain a space or comma separated value. i.e "connection.pool.max-total=50 connection.pool.max-idle=55 connection.driver-property.autoReconnect=true" . If the property exists it would override it and if it doesn't exist it would add it. That way we ensure the db property files are not mounted and get out of sync at the users side.

[iromli]

@moabu we can utilize javaproperties library to modify jans-mysql.properties or jans-pgsql.properties. The library recognizes key-value pair separated by newline character \n.

[iromli]

Example reading default jans-mysql.properties using javaproperties:

{'auth.userName': '%(rdbm_user)s',                                                                                                                                                
 'auth.userPassword': '%(rdbm_password_enc)s',                                                                                                                                    
 'binaryAttributes': 'objectGUID',                                                                                                                                                
 'certificateAttributes': 'userCertificate',                                                                                                                                      
 'connection.driver-property.cachePrepStmts': 'false',                                                                                                                            
 'connection.driver-property.cacheResultSetMetadata': 'true',                                                                                                                     
 'connection.driver-property.metadataCacheSize': '500',                                                                                                                           
 'connection.driver-property.serverTimezone': '%(server_time_zone)s',                                                                                                             
 'connection.pool.create-max-wait-time-millis': '20000',                                                                                                                          
 'connection.pool.max-idle': '15',                                                                                                                                                
 'connection.pool.max-total': '40',                                                                                                                                               
 'connection.pool.max-wait-time-millis': '20000',                                                                                                                                 
 'connection.pool.min-evictable-idle-time-millis': '1800000',                                                                                                                     
 'connection.pool.min-idle': '5',                                                                                                                                                 
 'connection.uri': 'jdbc:mysql://%(rdbm_host)s:%(rdbm_port)s/%(rdbm_db)s?enabledTLSProtocols=TLSv1.2',                                                                            
 'db.schema.name': '%(rdbm_schema)s',                                                                                                                                             
 'password.encryption.method': 'SSHA-256'} 

Passing custom properties via env var:

CN_SQL_DB_OVERRIDES=connection.pool.max-total=50\nconnection.pool.min-idle=10 \n connection.new-value=random

resulting in new key-value pairs (for overrides):

{'connection.new-value': 'random',
 'connection.pool.max-total': '50',
 'connection.pool.min-idle': '10 '}

The merged properties would be:

{'auth.userName': '%(rdbm_user)s',
 'auth.userPassword': '%(rdbm_password_enc)s',
 'binaryAttributes': 'objectGUID',
 'certificateAttributes': 'userCertificate',
 'connection.driver-property.cachePrepStmts': 'false',
 'connection.driver-property.cacheResultSetMetadata': 'true',
 'connection.driver-property.metadataCacheSize': '500',
 'connection.driver-property.serverTimezone': '%(server_time_zone)s',
 'connection.new-value': 'random',
 'connection.pool.create-max-wait-time-millis': '20000',
 'connection.pool.max-idle': '15',
 'connection.pool.max-total': '50',
 'connection.pool.max-wait-time-millis': '20000',
 'connection.pool.min-evictable-idle-time-millis': '1800000',
 'connection.pool.min-idle': '10 ',
 'connection.uri': 'jdbc:mysql://%(rdbm_host)s:%(rdbm_port)s/%(rdbm_db)s?enabledTLSProtocols=TLSv1.2',
 'db.schema.name': '%(rdbm_schema)s',
 'password.encryption.method': 'SSHA-256'}

[iromli]

@moabu according to https://github.com/JanssenProject/jans/issues/7816#issuecomment-1959825296, we can pass env vars or Java system properties to override config in jans-sql.properties.

Tested on local jans-auth-server image to intentionally triggering mysql connection error:

1. Passing CN_AUTH_JAVA_OPTIONS=-Dconnection.uri=random-mysql-uri env var (loaded in jans-auth component only)
2. Passing CN_JAVA_OPTIONS=-Dconnection.uri=random-mysql-uri env var (globally loaded in all jans components)
3. Passing specific CONNECTION_URI=random-mysql-uri env var (mapped to connection.uri property)

With this changes, we can take advantage of builtin features.

[moabu]

Certainly! Thanks @yurem for rolling that in .

Appreciate the link @iromli . We can move this issue into documentation phase so it gets added to the operations documentation.

@ossdhaval can you please add the comment above with document stylings to our operational docs.

[ossdhaval]

Certainly! Thanks @yurem for rolling that in .

Appreciate the link @iromli . We can move this issue into documentation phase so it gets added to the operations documentation.

@ossdhaval can you please add the comment above with document stylings to our operational docs.

@moabu

Sure

[ossdhaval]

@moabu

As per my understanding, this affects both the VM OPS guide and Kubernetes OPS guide. And I don't see any existing suitable document under OPS guides where this detail can be added. So, I am planning to create a new category under above two OPS guides for Persistence.

Alternatively, we can add these details under the persistence configuration section under the Database Guide.

I suggest the latter as all the DB-related info stays in the same place.

[moabu]

Yes agreed let's add them to https://docs.jans.io/v1.0.22/admin/reference/database/#configuration as it serves the org centrally